562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to use the RANKX function in DAX measures to sort data |
---|
The RANKX function is one way (probably the best one) to sort data. It's not the world's easiest function to understand, but this blog explains the pitfalls.
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources. |
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! |
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.
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.
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.
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.
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 5th 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 ...
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!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.