http://www.sqlservercentral.com/blogs/dknight/2011/08/11/powerpivot-_2600_ndash_3B00_-commonly-used-dax-expressions/

Printed 2014/08/20 06:42PM

PowerPivot – Commonly Used DAX Expressions

2011/08/11

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

=RELATED(DimSalesTerritory[SalesTerritoryRegion]) 

Calculated Column on DimsalesTerritory

=SUMX(RELATEDTABLE(FactInternetSales),FactInternetSales[SalesAmount])

 

Calculated Measures

==================================================

Calculated Measure on FactInternetSales [Profit]

=SUM(FactInternetSales[SalesAmount])-SUM(FactInternetSales[TotalProductCost])

Calculated Measure on FactInternetSales [Profit Margin]

=FactInternetSales[Profit]/SUM(FactInternetSales[SalesAmount])

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

=DISTINCTCOUNT(FactInternetSales[CustomerKey])

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

=CALCULATE(SUM(FactInternetSales[SalesAmount]), 
USERELATIONSHIP(DimDate[DateKey], FactInternetSales[DueDateKey]))

 

Calculated Measures - Time Intelligence

==================================================

Calculated Measure on FactInternetSales [YTD Profit] Returns YTD Profit

 

=CALCULATE(FactInternetSales[Profit],DATESYTD(DimDate[FullDateAlternateKey]),ALL(DimDate))

Calculated Measure on FactInternetSales [Last Year YTD Profit]

=TOTALYTD(FactInternetSales[Profit],
DATEADD(DimDate[FullDateAlternateKey],-12,MONTH))

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

=CALCULATE(FactInternetSales[Profit], 
DATESBETWEEN(DimDate[FullDateAlternateKey],
DATEADD(FIRSTDATE(DimDate[FullDateAlternateKey]),-11, MONTH),
LASTDATE(DimDate[FullDateAlternateKey])))

Calculated Measure on FactInternetSales [Last Years Profit]

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

 

 

Great PowerPivot/DAX Resources

http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-data-analysis-expressions-dax-language.aspx
http://www.powerpivotblog.nl/

http://cwebbbi.wordpress.com/
http://powerpivotgeek.com/
http://powerpivotpro.com/
http://powerpivot-info.com/


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