DAX RANKX: Ties, Dense Ranking and Rank by Group
· 8 min read
RANKX ranks the current item against a list of items, using a measure you choose. The first argument is the table to rank over, usually ALL of the column on your visual. The second is the expression to rank by. By default the highest value gets rank 1 and ties use SKIP (1, 1, 3).
Customer Rank =
RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue] )The ALL is the part people forget. Without it, RANKX only sees the one customer in the current row, and every customer ranks 1. Below: the full syntax, ties with SKIP and DENSE, ranking within a category, and the mistakes AI assistants make with RANKX.
The syntax
RANKX ( <table>, <expression> [, <value> [, <order> [, <ties>]]] )- table: the list of items to compare against. RANKX evaluates the expression once for each row of this table.
- expression: what to rank by, usually a measure. Because RANKX iterates, a measure here gets context transition, so it returns the value for each row of the table.
- value (optional): the value to find the rank of. Leave it out and RANKX uses the expression in the current filter context.
- order (optional):
DESC(the default, highest is 1) orASC(lowest is 1). - ties (optional):
SKIP(the default) orDENSE.
Example: rank customers by revenue
Brightline Supply Co.'s sample model has a Sales fact table with eight order lines and Products, Customers and Date dimensions. Revenue counts completed orders only. Put Customers[CustomerName] on a table visual with both measures:
Completed Revenue =
CALCULATE (
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) ),
Sales[Status] = "completed"
)
Customer Rank =
RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue] )| Customer | Completed Revenue | Customer Rank |
|---|---|---|
| Harbor & Pine Hotels | 208.10 | 1 |
| River Print Co | 180.00 | 2 |
| Lakeside Studios | 149.00 | 3 |
| Pioneer Studios | 75.00 | 4 |
| Total | 612.10 | 1 |
Look at the total row. On the total there is no customer filter, so the value being ranked is 612.10, which beats every single customer. A rank on a total is meaningless, so blank it:
Customer Rank =
IF (
ISINSCOPE ( Customers[CustomerName] ),
RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue] )
)ISINSCOPE is TRUE only on rows where CustomerName is the grouping level, so the total row shows nothing.
In SQL this is RANK() OVER (ORDER BY revenue DESC). The SQL version is explained in RANK over PARTITION BY.
RANKX ties: SKIP vs DENSE
Rank customers by the number of completed orders instead. Harbor and Lakeside have 2 each, Pioneer and River have 1 each:
Completed Orders =
CALCULATE ( DISTINCTCOUNT ( Sales[OrderID] ), Sales[Status] = "completed" )
Order Rank (skip) =
RANKX ( ALL ( Customers[CustomerName] ), [Completed Orders] )
Order Rank (dense) =
RANKX ( ALL ( Customers[CustomerName] ), [Completed Orders], , DESC, DENSE )| Customer | Completed Orders | Order Rank (skip) | Order Rank (dense) |
|---|---|---|---|
| Harbor & Pine Hotels | 2 | 1 | 1 |
| Lakeside Studios | 2 | 1 | 1 |
| Pioneer Studios | 1 | 3 | 2 |
| River Print Co | 1 | 3 | 2 |
With SKIP, two customers share rank 1, so the next rank is 1 + 2 = 3. With DENSE, the next rank is simply 2. The empty third argument (, ,) keeps the default value, so you can reach the order and ties arguments.
Pick SKIP when "rank 3" should mean "two customers are ahead of you". Pick DENSE when you want the number of distinct levels, for example "the second-highest order count". These match SQL's RANK() and DENSE_RANK().
If you need a unique rank (a "top 1" that must return one customer), break the tie with a second measure, for example rank by orders and then by revenue. A simple way is to add a tiny tie-breaker to the expression: [Completed Orders] + [Completed Revenue] / 1000000. Here that puts Harbor (208.10) ahead of Lakeside (149.00).
Rank within category
To rank products inside their own category, put Products[Category] and then Products[ProductName] on the rows of a matrix:
Product Rank in Category =
IF (
ISINSCOPE ( Products[ProductName] ) && NOT ISBLANK ( [Completed Revenue] ),
RANKX ( ALL ( Products[ProductName] ), [Completed Revenue] )
)| Category | Product | Completed Revenue | Product Rank in Category |
|---|---|---|---|
| Coffee & Kitchen | Tea Assortment (200) | 99.50 | 1 |
| Coffee & Kitchen | Espresso Beans 1kg | 96.00 | 2 |
| Paper & Print | Toner Cartridge Black | 112.10 | 1 |
| Paper & Print | Copy Paper A4 (500) | 49.50 | 2 |
| Cleaning | Paper Towels (24) | 255.00 | 1 |
Why this works: ALL ( Products[ProductName] ) removes only the product filter. The Category filter from the matrix row is still there, so products from other categories return blank revenue and rank below the real ones. Each category starts again at 1.
The NOT ISBLANK check matters. All-Purpose Cleaner 5L only has a cancelled order, so its completed revenue is blank. RANKX treats a blank number as 0, so without the check the cleaner would get rank 2 in Cleaning, and the row would appear in the matrix with no revenue next to it.
If the visual shows only ProductName (no Category on the rows), there is no Category filter to keep, and this measure ranks across all products: Paper Towels 1, Toner 2, Tea 3, Espresso 4, Copy Paper 5. To rank within the category even then, keep the category of the current product explicitly:
Product Rank in Own Category =
VAR CurrentCategory = SELECTEDVALUE ( Products[Category] )
RETURN
RANKX (
FILTER (
ALL ( Products[ProductName], Products[Category] ),
Products[Category] = CurrentCategory
),
[Completed Revenue]
)The SQL equivalent is RANK() OVER (PARTITION BY category ORDER BY revenue DESC).
Ranking only what the slicer shows
ALL ignores slicers. Select East and North in a Region slicer, and Lakeside Studios still ranks 3 and Pioneer Studios 4, because RANKX compares them with Harbor and River even though those rows are hidden. Use ALLSELECTED to rank within the selection:
Customer Rank (selected) =
RANKX ( ALLSELECTED ( Customers[CustomerName] ), [Completed Revenue] )| Customer | Completed Revenue | Customer Rank | Customer Rank (selected) |
|---|---|---|---|
| Lakeside Studios | 149.00 | 3 | 1 |
| Pioneer Studios | 75.00 | 4 | 2 |
Now Lakeside ranks 1 and Pioneer 2. The difference between ALL and ALLSELECTED is covered in more detail in ALL, ALLEXCEPT and ALLSELECTED.
Newer versions of DAX also have a RANK window function, which takes an ORDERBY clause and an optional PARTITIONBY, and also defaults to SKIP for ties. RANKX is still the one you will see in most existing models and interview questions.
Common mistakes
Every row ranks 1: no ALL
The most common AI-written version:
Customer Rank =
RANKX ( Customers, [Completed Revenue] )It returns 1 for every customer. In the Harbor row, the table Customers is filtered to Harbor only, so Harbor is compared with itself. How to spot it: if every row of a rank column shows 1, look at the first argument. It needs ALL or ALLSELECTED of the column on the visual.
Every row ranks 1: a bare aggregation instead of a measure
The second version AI assistants write looks almost the same:
Customer Rank =
RANKX ( ALL ( Customers[CustomerName] ), SUM ( Sales[Line Revenue] ) )(Here Line Revenue is a calculated column on Sales holding quantity × price × (1 − discount).)
Also all 1s. SUM inside RANKX has no CALCULATE around it, so there is no context transition: for every customer in the list it returns the same number (the current row's total). Every value ties, so everyone is rank 1. Use a measure reference such as [Completed Revenue], or wrap the expression in CALCULATE. This also quietly skips the "completed only" rule, which is a second bug in the same line. The reason context transition matters is explained in SUM vs SUMX.
ALL on a different column from the visual
RANKX ( ALL ( Customers[Region] ), [Completed Revenue] ) with CustomerName on the rows is a third way to get all 1s. RANKX loops over West, East and North, but the customer filter from the row is still on. In the Pioneer row, West and East return blank (Pioneer has no revenue there) and North returns 75.00, so Pioneer "wins" with rank 1. Every customer does the same. The column inside ALL must be the column you are ranking.
FAQ
How does RANKX handle ties?
By default with SKIP: tied items share the same rank and the next rank jumps by the number of ties (1, 1, 3). Pass DENSE as the fifth argument to get 1, 1, 2.
How do I rank within a group in Power BI?
Put the group (for example Category) above the item on the matrix rows and use RANKX ( ALL ( Products[ProductName] ), [Measure] ). The group's filter stays, so ranking restarts in each group. To rank within a group when the group is not on the visual, filter the ranking table to the current item's group, as shown above.
Why does RANKX return 1 for every row?
Either the table argument is not wrapped in ALL (so RANKX sees only the current item), or the expression is a plain aggregation without CALCULATE (so every item gets the same value). Fix both and test on a row you can check by hand.
How do I rank in ascending order?
Pass ASC as the fourth argument: RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue], , ASC ). Pioneer Studios, with the lowest revenue, becomes rank 1.
Is RANKX the same as RANK in SQL?
The default RANKX matches SQL RANK() and RANKX with DENSE matches DENSE_RANK(). The difference is that SQL ranks rows in a result set, while RANKX ranks the current item of a visual against a table you pass in. Practise both with the DAX practice exercises.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Rank the products by revenue
- Rank the busiest months, ties share a place
- Rank each product within its category