Printed 2015/10/06 06:45PM

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

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.