RANKX
Power BI · Advanced DAX & Time Intelligence
Theory
RANKX(table, expression, value, order, ties) ranks the current item by evaluating the expression for every row of the table and finding where the current value falls. In practice you iterate over all items of a dimension, so the ranking ignores the visual's own filter on that dimension: Customer Rank = RANKX(ALL(Customers[Name]), [Total Sales]). The default order is descending (largest value = rank 1).
With the sample data, Asha (₹2,700) ranks 1, Ravi (₹2,400) 2, Meera (₹600) 3 and Karan (₹500) 4. The ties argument decides what happens with equal values: Skip (the default) gives 1, 1, 3, while Dense gives 1, 1, 2. A common mistake is RANKX(Customers, [Total Sales]) without ALL: inside a visual by Name, the table contains only the current customer, so everyone gets rank 1. Use ALLSELECTED instead of ALL if the ranking should respect slicers on that dimension.
Why we use it
Top-N lists, leaderboards and 'where do we stand' questions all need ranks. RANKX gives dynamic ranks that update with slicers on other dimensions such as Year or Region.
When to use it
Use RANKX for ranking items in visuals or filtering a Top N with a measure. For a simple 'top 5 by value' on one visual, the visual-level Top N filter is quicker. Add a check like IF(ISINSCOPE(...)) to hide the rank on total rows.
How it works
- Visual row for a customer
- Evaluate [Total Sales] for the current customer
- Evaluate [Total Sales] for every customer in ALL(Customers[Name])
- Count how many values are larger
- Are there ties? Yes: Apply Skip or Dense rule. No: Rank = larger values + 1.
- Rank shown per row
Syntax
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
order: DESC (default) | ASC
ties: Skip (default) | DenseExample
Customer Rank =
RANKX(
ALL(Customers[Name]),
[Total Sales],
,
DESC,
Dense
)
// Hide on the total row
Customer Rank (clean) =
IF(ISINSCOPE(Customers[Name]), RANKX(ALL(Customers[Name]), [Total Sales]))Key points
- Iterate over ALL (or ALLSELECTED) of the dimension being ranked
- Default order is descending: highest value gets rank 1
- Ties: Skip gives 1,1,3; Dense gives 1,1,2
- Without ALL every row ranks 1 inside a visual
- Use ISINSCOPE to blank the rank on totals