Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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).

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

=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/

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.