RANK function in MDX

RANK function assigns a one-based index for members based on expressions.

Example 1:

Below Query will assign ranks without breaking hierarchy.


WITH SET NEWSET AS ORDER
(
    [Product].[Product Categories].[SubCategory].MEMBERS
    ,[Measures].[Order Count]
    ,DESC
)
MEMBER [MEASURES].[Ranked] AS RANK (       
        [Product].[Product Categories].CURRENTMEMBER
        ,NEWSET
    )
SELECT
    {
        [Measures].[Order Count]
        ,[MEASURES].[Ranked]
    } ON 0
    ,ORDER( [Product].[Product Categories].[SubCategory].MEMBERS
            , [MEASURES].[Ranked]
            , ASC) ON 1
FROM [Adventure Works]

Example 2
Below Query will assign ranks & breaks hierarchy defined at cube level.

WITH SET NEWSET AS ORDER
(
    [Product].[Product Categories].[SubCategory].MEMBERS
    ,[Measures].[Order Count]
    ,BDESC
)
MEMBER [MEASURES].[Ranked] AS RANK (       
        [Product].[Product Categories].CURRENTMEMBER
        ,NEWSET
    )
SELECT
    {
        [Measures].[Order Count]
        ,[MEASURES].[Ranked]
    } ON 0
    ,ORDER( [Product].[Product Categories].[SubCategory].MEMBERS
            , [MEASURES].[Ranked]
            , BASC) ON 1
FROM [Adventure Works]