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



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s