My name is Rank, Dense Rank!

The Rank is one of the functions that wasn’t initially designed for the MDX. Well, it’s possible for sure, however it’s not a first class function. You can easily do ranking using Excel, but the iterating isn’t a strong side of the MDX. You may read a Mosha Pasumansky’s blog post about the Rank function. He explains how to write a C# stored procedure in order to iterate set members. There is a little information about ranking sets with two or more attributes and dense ranking, so, I’ve found it interesting to post a sample here. Let’s go with a simple rank and two attributes:

with
Dynamic Set OrderedSet as
Order(
    NonEmptyCrossJoin(
       [Client].[Client].[Client].Members,
       [Product].[Product].[Product].Members,
       [Measures].[Invoice Count],
	   2
    ),
    [Measures].[Invoice Count],
    BDESC
)

Member [Measures].[Rank] as
Rank(
        ([Client].[Client].Currentmember,[Product].[Product].CurrentMember),
        OrderedSet
)

select {[Measures].[Invoice Count],[Measures].[Rank]} on 0,
non empty OrderedSet on 1
from [BI Fake]
where ([Date].[Day].&[20160120])

The result:

Let’s say we want to dense rank it based on a measure value:

with
Dynamic Set OrderedSet as
Order(
    NonEmpty(
       [Client].[Client].[Client].Members,
       [Measures].[Invoice Count]
    ),
    [Measures].[Invoice Count],
    BDESC
)

Dynamic Set DenseOrderedSet as
Order(
    NonEmpty(
       [Client].[Client].[Client].Members,
       [Measures].[RankFirstMatch]
    ),
    [Measures].[Invoice Count],
    BDESC
)

Member [Measures].[Rank] as
Rank(
        [Client].[Client].Currentmember,
        OrderedSet
)

Member [Measures].[RankFirstMatch] as
IIF(
	[Measures].[Invoice Count] 
	=
	(
		OrderedSet.Item([Measures].[Rank] -2),
		[Measures].[Invoice Count]
	),
	NULL,
	[Measures].[Rank]
)

Member [Measures].[RankDenseSet] as
Rank(
        [Client].[Client].Currentmember,
        DenseOrderedSet
)

Member [Measures].[DenseRank] as
IIF(
	[Measures].[RankDenseSet] = 0,
	(OrderedSet.Item([Measures].[Rank] -2),[Measures].[DenseRank]),
	[Measures].[RankDenseSet]
)

select {[Measures].[Invoice Count],[Measures].[Rank],[Measures].[RankFirstMatch],[Measures].[RankDenseSet],[Measures].[DenseRank] } on 0,
non empty OrderedSet on 1
from [BI Fake]
where ([Date].[Day].&[20160120])

The result: