BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 12 January 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using the RANKX function in DAX to sort data
This blog explains how you can sort data in DAX. Note that in addition to the RANKX function, there are two other possibilities I know of:
Function | Notes |
---|---|
RANK.EQ | This function is included to provide compatibility with Excel. It provides a subset of the functionality of RANKX, and is rarely used. |
EARLIER | This function provides an alternative way of ranking data, and is if anything even harder to understand than RANKX! |
Syntax of the RANKX function
Here's how you can write the RANKX function:
You can miss out all but the first two arguments. The arguments are:
Argument | What it is | What it means |
---|---|---|
1 | Table | The table you are ranking over. |
2 | Expression | The expression you're ranking by. |
3 | Value | A bizarre chance to do one-off value substitutions (see foot of page). |
4 | Sort order | Whether you're sorting in ascending or descending order. |
5 | Ties | How to handle ties. |
Arguments 3, 4 and 5 are explained under separate headings further down this page.
Ranking using a calculated column
This is nice and simple! The example below sorts the products in list price order, with the most expensive item coming first:
The Emperor is the most expensive product; Wol the cheapest.
Setting sort order
The Order argument can take one of two values:
Value | What it means |
---|---|
0 or FALSE | Sort into descending order |
1 or TRUE | Sort into ascending order |
Note that the default is to use descending order, which is the opposite of usual Microsoft practice (not to mention common sense).
So you could rewrite our formula like this to put Wol first and The Emperor last:
=rankx(
Product,
[FullPrice],
,
True()
)
Note the comma as a placeholder for the third argument.
Ranking using measures
Things get a bit more complicated when you create a measure to rank data. To illustrate this, I'll show getting it wrong first.
Let's suppose that you've already created a measure to show total sales, and you want to use this to rank species:
First create a measure called TotalSales, to sum the quantity for transactions.
The obvious thing to do is to create this measure in the Animal table:
This measure ranks species by total sales. What could possibly go wrong?
Here's how this measure shows up in a typical pivot table:
The rank is 1 for every cell.
The reason for this is that a measure will aggregate data for the records in the underlying query context. This formula is showing (for example) that the total sales for the East quadrant for the Amphibian species is the first (and only) value in the set of figures for the relevant cell.
The solution is to widen the query context to rank over all species:
SalesOrder:=RANKX(
ALL( Species ),
[TotalSales]
)
This will then give the correct results:
I've included the total quantity sold to show the results aren't fiddled! Mammals are always the best-sellers, and amphibians always the worst.
Dealing with ties
Suppose that you have the following measure in the transactions table, showing for any set of results the highest single transaction amount (usually 4, but occasionally less):
HighestQuantity: =MAX([Quantity])
Also, in the Product table (which you'll need to unhide from client tools) you create the following measure:
Ranking:=RANKX(
ALL('Product'),
'Transaction'[HighestQuantity]
)
You then create a pivot table showing these measures by product for the West quadrant:
There are 6 products which share top ranking for this measure (with 4), so the next ranking begins at number 7.
This is the default (and most sensible) way of dealing with ties. It's called Skip, but you can also use Dense for the 5^{th} argument:
The same rankings but using the Dense method of dealing with ties.
Here's the amended measure (note the commas acting as placeholders for missing arguments):
Ranking:=RANKX(
ALL('Product'),
'Transaction'[HighestQuantity],
,
,
Dense
)
Whether it's worth knowing this is another question! And on this theme (and purely for the sake of completeness), I'll finish this blog by showing what I call ...
Hypothetical sorting
To complete the picture, the third argument of the RANKX function allows you (bizarrely) to substitute a different value for each cell of a pivot table. The only way to understand this is by example!
I'm going to try to explain why in this pivot table the WeirdRank statistic shows 6 for Owl.
To start with, I've created (as can you) two measures in the Transaction table:
Measure | Formula |
---|---|
HighestQuantity | =MAX([Quantity]) |
LowestPrice | =MIN([Price]) |
In the Animal table, I've then created this measure:
WeirdRank:=RANKX(
ALL(Animal),
[HighestQuantity],
[LowestPrice]
)
What this will do is rank each animal's highest quantity into the correct order, but using the lowest price for the animal and the highest quantity for all of the others. Here's what this means for the Owl animal:
Animal | HighestQuantity | LowestPrice | What to use |
---|---|---|---|
Owl | 3 | 2.80 | 2.80 |
Cat | 2 | Irrelevant | 2 |
Sheep | 3 | Irrelevant | 3 |
Snake | 3 | Irrelevant | 3 |
Frog | 2 | Irrelevant | 2 |
Tiger | 3 | Irrelevant | 3 |
Crocodile | 3 | Irrelevant | 3 |
Penguin | 1 | Irrelevant | 1 |
Here's the table for the Cat animal:
Animal | HighestQuantity | LowestPrice | What to use |
---|---|---|---|
Owl | 3 | Irrelevant | 3 |
Cat | 2 | 3.61 | 3.61 |
Sheep | 3 | Irrelevant | 3 |
Snake | 3 | Irrelevant | 3 |
Frog | 2 | Irrelevant | 2 |
Tiger | 3 | Irrelevant | 3 |
Crocodile | 3 | Irrelevant | 3 |
Penguin | 1 | Irrelevant | 1 |
Here's another example pivot table, to try to explain:
Any lowest price more than 4 (the biggest HighestQuantiy) ranks as number 1.
This use of RANKX blows my mind, to be honest; just as well it's not that useful!
And with that, I'm done with ranking!