Rank function


  • I have an MDX Query that is using the rank function, but it is not resetting the rank value when the top level of the hierarchy changes.  I want to use MDX to get the same values I would get when using the TSQL Row_Number () over (partition by ...) function.  Here is a sample of the actual output and desired output from the query below:

    HourNum HighLowDiff HighLowDiffMode(actual) HighLowDiffMode(desired)
    0 0 2 2
    0 1 1 1
    0 2 3 3
    1 0 4 1
    1 1 6 3
    1 2 5 2

    WITH SET HourDiff AS 
    Order (([Dim Date Time].[Hour Num].Children,[Gbp Usd Gaps H1].[High Low Diff].Children),[Measures].[High Low Diff],DESC)
    MEMBER [Measures].[High Low Diff Mode] AS
    Rank (([Dim Date Time].[Hour Num].CurrentMember,[Gbp Usd Gaps H1].[High Low Diff].CurrentMember), HourDiff)
    {	[Measures].[High Low Diff Mode]	} ON COLUMNS,
    {	([Dim Date Time].[Hour Num].Children,[Gbp Usd Gaps H1].[High Low Diff].Children)	} ON ROWS
    FROM h1

    Monday, November 18, 2013 2:29 PM


  • Would you be opposed ordering the set inside the rank function?  This makes it a bit easier because it keeps the current context in play for the outside dimension (the one you want to "partition" on).

    Here's an example where the months of each year are sorted and ranked by sales amount.  The ranking is "partitioned" by year. 

    	SET [YearMonthSet] AS
    			[Date].[Calendar Year].[Calendar Year],
    				CROSSJOIN (
    					 [Date].[Calendar Year].CurrentMember
    					,[Date].[Month of Year].[Month of Year]
    				,[Measures].[Internet Sales Amount]
    	MEMBER [Measures].[Rank in Year] AS
    			 [Date].[Month of Year].CurrentMember
    			,ORDER (
    				 [Date].[Month of Year].[Month of Year]
    				,([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])
    		[Measures].[Internet Sales Amount],
    		[Measures].[Rank in Year]
    	} ON 0,
    	} ON 1
    	[Adventure Works]

    The calculated set isn't necessary but just makes the result set pretty:

    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by JeffreyShumaker Wednesday, November 20, 2013 12:54 AM
    Tuesday, November 19, 2013 9:39 PM