I have gathered some useful mdx statement to help us all by using it.
Have fun!
Dimension MDX examples
Top ten products on product category level based on actual
{TopCount([Product].[Product Category].Members, 10, [Measures].[Actual])}
Last 10 products on product category level based on actual
{BottomCount([Product].[Product Category].Members, 10, [Measures].[Actual])}
Return children if exists to selected member in the Filter Selector otherwise return selected member.
StrToSet(IIF(IsLeaf(_FILTERTUPLE_.Item(0)), "{_FILTERTUPLE_.Item(0)}", "{_FILTERTUPLE_.Item(0).Children}")).
Return members filtered on a custom property. We need to exclude the All level from the set to filter on since it does not have any custom properties.
Filter(Except([Warehouse].Members,{ [Warehouse].[All Warehouse]}), [Warehouse].CurrentMember.Properties("PROPERTYNAME") = "PROPERTYVALUE")
Time examples
How to define a default value for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")
How to create a named set for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
{StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")}
How to create a named set referring to a separate column in the table used for the time dimension as a member property
{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}
Referring to a named set called Current Month
[Current Month].Item(0).Item(0)
Referring to last 6 months (up to current month)
LastPeriods(6, [Current Month].Item(0).Item(0))
How to use the named set Current Month for positioning on the quarter level
LastPeriods(6, Ancestor([Current Month].Item(0).Item(0), Time.Quarter))
Last 6 members on month
{Tail([Time].[Month].members,6)}
Last 6 members on month where actual is larger then 0
{Tail(Filter({[Time].[Month].members},[Measures].[Actual] > 0),6)}
All months where actual is larger then 0
{Filter({[Time].[Month].members},[Measures].[Actual] >0)}
Order months depending on actual
{Order([Time].[Month].members, [Measures].[Actual], BDESC)}
Returns all members in the Warehouse dimension.
Hierarchize([Warehouse].Members)
Returns ‘All Warehouse’ member and all members in level Country and States Provinces.
Hierarchize({[Warehouse].[All Warehouse], [Warehouse].[Country].Members,[Warehouse].[State Province].Members})