SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

PowerPivot – Commonly Used DAX Expressions

Today I will be presenting in a webinar, which by the way is recorded for later viewing, on Commonly used DAX Expressions.  You can register (or if you miss it live watch the recording) for this webinar here.  I thought it would be great if I could push out the code that I plan to show ahead of time so you could review it immediately following or even during the webinar.  Also, if I run out of time you can see what I planned to cover!

Some of these have a short description while others are self explanatory based on the name of the calculations.  Please keep in mind if you have problems building any of these that they do build off each other so make sure you do all of them in order!  I?m also using the AdventureWorksDW2008R2 database as my data source.


Calculated Columns

Calculated Column on DimCustomer (Concatanate Fields)

=DimCustomer[FirstName]&" "&DimCustomer[LastName]

Calculated Column on DimPromotion (IsNULL equivalent)

=IF(DimPromotion[MaxQty]=Blank(),"No Max Required","Max Qty Required")

Calculated Column on FactInternetSales


Calculated Column on DimsalesTerritory



Calculated Measures


Calculated Measure on FactInternetSales [Profit]


Calculated Measure on FactInternetSales [Profit Margin]


Calculated Measure on FactInternetSales [Customer Count] Used to get distinct count of customers


Calculated Measure on FactInternetSales [Due Date Sales Amount] Used for role playing dimensions

USERELATIONSHIP(DimDate[DateKey], FactInternetSales[DueDateKey]))


Calculated Measures - Time Intelligence


Calculated Measure on FactInternetSales [YTD Profit] Returns YTD Profit



Calculated Measure on FactInternetSales [Last Year YTD Profit]


Calculated Measure on FactInternetSales [Rolling 12 Months Profit] Returns running total of measure

DATEADD(FIRSTDATE(DimDate[FullDateAlternateKey]),-11, MONTH),

Calculated Measure on FactInternetSales [Last Years Profit]

=CALCULATE(FactInternetSales[Profit], SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]))



Great PowerPivot/DAX Resources



Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


No comments.

Leave a Comment

Please register or log in to leave a comment.