Welcome to EMC Consulting Blogs Sign in | Join | Help

Christian Wade's Blog

DAX Common Calcs Reference List

Having recently evaluated the BISM Tabular (CTP3), I checked I can perform some common calcs and created this reference list.  Rather than store it in the cloud somewhere, I thought I’d share it (and then find it easier when I want to use it later).

 

Many-to-Many Dimensions

 

M2MDim

 

No Allocation

Update 10/18/11 - use SUMMARIZE instead of COUNTROWS:

 

=CALCULATE( SUM('SalesFact'[Sales]),

                     FILTER( 'InnerDim',

                                   CALCULATE( COUNTROWS('M2MFact') > 0 )

                                )

                   )

 

=CALCULATE( SUM('SalesFact'[Sales]),

                        SUMMARIZE( 'M2MFact', 'InnerDim'[InnerDimKey] )

                     )

 

 

With Allocation

=SUMX(  'InnerDim',

                 CALCULATE( SUM( 'SalesFact'[Sales]) *

                                        SUM('M2MFact'[M2MFactAllocationFactor])

                                      )

             )

 

Moving Averages (day level)

 

=CALCULATE( [My Measure],   --already existing summable measure 

                     DATESINPERIOD( 'Date'[SQLDate],

                                                  LASTDATE( 'Date'[SQLDate]),

                                                  -89,

                                                  day

                                                ),

                     ALL('Date')

                   ) / 90

 

Role Playing Dimensions

 

For multiple instances of the same dimension using different relationships, easy workaround is to just create multiple instances of the same table in the model (OK – it’s not perfect, but it works).

 

And we can create different measures that relate to the single instance of the dimension, but use "inactive" relationships using the USERELATIONSHIP function:

 

=CALCULATE([Sales], USERELATIONSHIP(DimDate[DateKey], FactSales[ShipDate])

  

Period To Date

 

=CALCULATE( [My Measure],   --already existing summable measure 

                     DATESMTD('Date'[SQLDate]),

                     ALL('Date')

                   )

 

Parent Child Hierarchies

 

See http://www.powerpivotblog.nl/powerpivot-denali-parent-child-using-dax

 

 

Published Tuesday, September 06, 2011 8:46 PM by christian.wade

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

online gokken said:

nice list! =)

September 14, 2011 11:08 AM
 

Lubos Pribula said:

Christian,

the many to many DAX formula with the allocation inside saved my day.

I use it in Excel worksheet powered by PowerPivot and I can't still believe how powerful it is.

Thank you so much.

June 24, 2014 8:26 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems