MDX – DENSE RANK

It is very common scenario to have a TOP N reporting requirements. In most of the scenarios MDX TopCount and Rank can be used to solve but at the same time it is very common to miss the big picture of TIES. Unfortunately, we don’t have a DENSE_RANK function so requires bit logical calculations.
Going forward with a scenario from AdventureWorks Cube, for each month get the Top 3 Products in all countries. It is bit complex to include for each scenario with Top N requirements but it can be a common scenario in most of reporting requirements.

WITH SET [Sorted Models] AS
// For each month get Top 25 records, choosed a Top 25 from business case 
 Generate ( [Date].[Calendar].[Month].Members,
		TopCount 
		  ( nonempty(
			   {[Date].[Calendar].CurrentMember
			  * [Product].[Model Name].[Model Name].MEMBERS
			  * [Geography].[Country].[Country].MEMBERS
                            }
			   ,[Measures].[ORDER Count] 
			     ) 
		   , 25
		   ,[Measures].[ORDER Count]
		  )
	      )

MEMBER [Measures].[Rank] AS
// Get the Rank of current member Tuple to Current Month Set
// Do not use Sorted Models set due to dynamic for each set
Rank ( (  [Date].[Calendar].CurrentMember 
	, [Product].[Model Name].CurrentMember
	, [Geography].[Country].CurrentMember
        )
  ,  Generate ( [Date].[Calendar].CurrentMember,
	TopCount 
	  ( nonempty(
		 {  [Date].[Calendar].CurrentMember
		  * [Product].[Model Name].[Model Name].MEMBERS
		  * [Geography].[Country].[Country].MEMBERS
                  }
		   ,[Measures].[ORDER Count] 
		   ) 
	   , 25
	   ,[Measures].[ORDER Count]
	  )
	 )
    , [Measures].[ORDER Count] 
    )
	  
MEMBER [Measures].[Previous Set Index] AS
// Get the Set Index using Rank 
( 
[Measures].[Rank] - 2 
) 
MEMBER [Measures].[Dense Rank] AS
// Get the Dense Rank using the Index position value 
CASE 
 WHEN [Measures].[Rank] = 1 
  THEN 1 
 ELSE 
		( 
		[Sorted Models].Item([Measures].[Previous Set Index]), 
		[Measures].[Dense Rank] 
		) 
	 + 
		Iif 
		( 
		     ( 
		     [Sorted Models].Item([Measures].[Previous Set Index]), 
		     [Measures].[ORDER Count] 
		     ) 
		  = 
		     [Measures].[ORDER Count] 
		 ,0 
		 ,1 
		) 
End 
 
SELECT
  {
      [Measures].[ORDER Count]
    , [Measures].[Rank]
    , [Measures].[Dense Rank]
    //, [Measures].[Previous Set Index]
  } ON 0
 ,NON EMPTY
	 {
	 // FILTER Can be used to get the TOP 3 using DENSE RANK 
	 // FILTER (  
			   [Sorted Models]
	//		 , [Measures].[Dense Rank] <=3
	//	    )
	 
	 } ON 1
	FROM (
		SELECT [Date].[Calendar].[Calendar Year].&[2006] ON 0 
                 FROM
                     [Adventure Works]
		 )