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