ranking within MDX

Question

I have this MDX Query:

WITH
MEMBER [COUNT_RANK] AS
   RANK(([TKT].[SP].CURRENTMEMBER,
[TKT].[SA].CURRENTMEMBER) ,
[TKT].[SP].CURRENTMEMBER
*[TKT].[SA].[SA]
)
SELECT {
[COUNT_RANK],
[Measures].[TKT Count],
[Measures].[Est Hours]
} ON 0,
ORDER ({[TKT].[SP].[SP]} * {[TKT].[SA].[SA]}, [Measures].[TKT Count], DESC)
ON 1
FROM Ops

the issue I have is that While the COUNT_RANK works and provides a 1-to-n value of ranking per SP for each SA, I need the order of the rank based on TKT Count desc. Meaning for rank = 1, then that SP*SA must have the highest number of TKTs.

Right now the result is random TKT Counts for the RANKING. how do I make the RANK go based on TKT Count DESC?

This is for SQL Server 2016 SSAS. Thanks.

Answer 1

From the MSDN Docs, you should create an ordered set before you declare the RANK() member, and use RANK function on the ordered set.

Here's their example:

WITH   
SET OrderedCities AS Order  
   ([Geography].[City].[City].members  
   , [Measures].[Reseller Sales Amount], BDESC  
   )  
MEMBER [Measures].[City Rank] AS Rank  
   ([Geography].[City].CurrentMember, OrderedCities)  
SELECT {[Measures].[City Rank],[Measures].[Reseller Sales Amount]}  ON 0   
,Order  
   ([Geography].[City].[City].MEMBERS  
   ,[City Rank], ASC)  
    ON 1  
FROM [Adventure Works]