Rank Functions in MDX
I know its looks weird to ask for Rank function”s” in MDX because there is only one for it.
But in SQL we have multiple Rank functions and used under different circumstances for solving different purposes. Those are RANK, ROW_NUMBER, NTILE and DENSE_RANK.
If SQL has it why not in MDX!!!! So I tried to give you few examples so that you can call the functionality of Rank functions of SQL in MDX. I saw few post online but those were so bloody complex that it took time for me to understand what they were trying to do, and yet not much useful.
Below is the Query with different calculated measures and then the screenshot from the result. Calculated measure names are actually based on T+SQL ranking functionality:

Result (click on image to enlarge)
So you can study the MDX and result from the post. Here I can explain in detail what I did in MDX for different cases but I believe these example are simplest I can come up with and can be understood easily. Still in case of any doubt or explanation needed please comment and I’ll be ready to help.
These example doesn’t have NTILE but I’ll try to get some time and add alternative for it later.
Posted on April 24, 2014, in Begin BI, MDX, SSRS Reporting and tagged Convert SQL Rank to MDX, Convert SQL Row Number in MDX, Dense Rank in MDX, Identity Column, Identity Column in MDX, Identity Column in SQL, Order by in MDX, Order in MDX, Partition by in MDX, Problem with Rank in MDX, Rank alternative MDX, Rank function in MDX, Rank in MDX, Row Number function in MDX, Row Number in MDX. Bookmark the permalink. 4 Comments.
Awesome compilation of thoughts; I have idea to add create NTILE function.
NTILE(x) function accepts one argument and bucketize all the rows in x parts; We can achieve it by using quotient function on COUNT(*) and Row Number like:
NTILE(x) = INT(ROWNUMBER / INT(COUNT / X)) + 1
I have not test it out, will post it again with some sample data
Very good Explanation ..Keep going
You know this is the same in any other field.
You would think past showes us at least anything, but no.
Feel free to disagree but the world is changing, and none of us have no control over it.
E.g., If only Obama had any balls to put Putin to his place, but it seems like it’s never happening, welcome WW3.
Awesome post, thanks!
Sarah http://phytorenew350i.com/
I think Chris Webb or Rob Collie blogged about NTILE in MDX at one point, but I cannot remember.