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