DAX and data models
Rankx Power BI: The Foundation Everyone Skips
Rankx Power BI explained straight: real numbers, named settings, and a build path for practitioners who need the actual answer, not filler.
Austin Levine
·
Aktualisiert
The RANKX Power BI function ranks rows by a measure, like top products or top regions, and updates live as users filter. You write it as a measure, point it at the right table with ALL or ALLSELECTED, and set DESC and DENSE so the top value is rank 1 with no gaps on ties.
The RANKX Power BI function is where a lot of "top 10" dashboards go wrong, and that's a business problem before it's a technical one. In a 2025 Drexel LeBow and Precisely survey of 550-plus data professionals, 67% of organizations said they don't fully trust the data they use to make decisions. A ranking that flips when someone clicks a slicer is exactly the kind of thing that feeds that distrust. Gartner puts the cost of poor data quality at 12.9 million dollars a year for the average organization. So when a manager asks "who are our top five customers" and gets a different answer on two reports, that isn't a cosmetic bug. It decides whether people believe the dashboard at all.
Below is the practitioner version: what RANKX actually does, how to write it so the numbers stay correct, the settings that trip people up, and how to keep the ranking fast and correct once real users depend on it.
Key takeaways
RANKX ranks rows by a measure, like top products or regions, and updates live as users filter.
Write RANKX as a measure, set DESC so the top value is rank 1, and DENSE so ties leave no gaps.
The first argument, the table you rank across, is what decides whether the rank looks right.
Choosing ALL or ALLSELECTED decides whether the ranking ignores or respects the user's slicer.
What does RANKX do in Power BI?
RANKX is a DAX function that assigns a rank number to each row based on a value you give it. You hand it a table to rank over and an expression to rank by, and it returns 1 for the highest, 2 for the next, and so on.
Here's the smallest useful version:
```dax Product Rank = RANKX( ALL(Product[Name]), [Total Sales] ) ```
That measure ranks every product by total sales. Drop it in a table next to your product names and each product gets its position. The important part is that it recalculates for the exact slice a user is looking at. Filter to one region and, depending on how you scope it, the ranks can recompute for just that region. That live behavior is the whole reason a ranking belongs in a measure instead of a frozen column.
Why does a bad rankx Power BI measure lose trust so fast?
Because ranking is the most public number on a dashboard. A "top 5 customers" tile ends up in a management report, and if it disagrees with the number someone pulled last week, the whole report gets questioned. In a 2024 peer-reviewed study by Panko and colleagues, 94% of business spreadsheets used in decision-making contained errors. A ranking rebuilt by hand in Excel every month inherits every one of those errors, plus a new one each time someone copies the wrong range.
A clean RANKX measure fixes that. The ranking logic lives in one place, defined once, and every report that uses it returns the same order. Nobody re-sorts anything by hand. It also makes the model ready for Copilot, because Copilot answers "who are our top accounts" from the measure you defined rather than guessing at a column.
How do you write a rankx Power BI measure that stays correct?
The setting that decides everything is the first argument, the table you rank across. Get that wrong and the rank looks random. Here's a fuller version with the settings named:
```dax Product Rank = RANKX( ALL(Product[Name]), [Total Sales], , DESC, DENSE ) ```
Three choices are doing the work here:
ALL(Product[Name]) tells RANKX to rank across every product, ignoring the current row's filter. Without it, RANKX ranks each row against only itself and every product comes back as rank 1.
DESC puts the biggest seller at rank 1. Use ASC when smaller should win, like fastest response time.
DENSE stops gaps when two products tie. With DENSE, a tie for second is followed by third. Without it, the next rank jumps to fourth.
That's the core of the rankx dax pattern. One base measure for the value, one RANKX measure that references it, and the three arguments set on purpose instead of by accident.
ALL or ALLSELECTED: which one does your rank need?
This is the single most common reason a rank looks off, so it's worth being blunt about. The choice between ALL and ALLSELECTED decides whether the ranking respects the user's slicer.
ALL | ALLSELECTED |
|---|---|
What it ranks over | Every row, ignores all filters |
Rank when a region is selected | Ranks against all regions |
Good for | A fixed global leaderboard |
Common surprise | Ranks don't change on slice |
If a manager filters to one region and expects to see the top products in that region, you want ALLSELECTED. If they want each product's standing across the whole company no matter what they filter, you want ALL. Test both against a real slicer before you ship, because they look identical until someone clicks something.
How do you move a ranking out of a KPI Excel sheet?
Plenty of teams still run their leaderboards in a KPI Excel workbook: a big table, a manual sort, and a copy-paste into the deck every month. It works until the source data grows or two people keep separate versions. That's when a KPI tracker Excel file starts producing two different "top 10" lists in the same week.
Moving it into Power BI is mostly a modeling job, not a rewrite. Load the same fact table, build one Total Sales measure, then write the RANKX measure above on top of it. Now the ranking sorts itself live, updates on refresh, and every viewer sees the same order. The manual sort and the monthly copy-paste both go away, and so does the argument about whose spreadsheet is right.
How do you keep a ranking fast once the data grows?
RANKX over millions of rows can get slow if the underlying table is huge and refreshed in full every morning. Incremental refresh fixes the refresh side. You create two Power Query parameters named exactly `RangeStart` and `RangeEnd`, filter the fact table by a date column between them, then set an incremental refresh policy on the table. A common policy stores 5 years of rows and refreshes only the last 10 days, so Power BI partitions by date and touches just the recent rows. A refresh that took 40 minutes can drop to a couple of minutes. `RangeStart` and `RangeEnd` are case-sensitive, so type them precisely or the policy won't apply.
Two more features keep the ranking safe once people rely on it. Use View As in Power BI Desktop to test row-level security before you publish, so a regional manager only ranks the accounts they're allowed to see. Use deployment pipelines in the Power BI Service to move the model through Development, Test, and Production instead of editing the live report on a Friday afternoon.
Where do Fabric and OneLake fit in?
Microsoft Fabric adds a storage mode called Direct Lake, where the model reads Parquet files straight from OneLake, the storage layer under Fabric, with no import step. You get close to Import speed on large tables without the refresh wait, and your RANKX measure works exactly the same on top of it. Fabric passed 28,000 customers by Ignite 2025, so more teams are choosing between these modes each quarter. For most business dashboards an Import model is still the right default, and the RANKX measure you write doesn't change when you move to Direct Lake later.
Where to go from here
A ranking people trust comes down to a few habits. Write RANKX as a measure, not a column. Set the first argument on purpose, ALL for a global leaderboard and ALLSELECTED for "top within my slice." Add DESC and DENSE so rank 1 is the top value with no gaps on ties. Keep refresh fast with `RangeStart` and `RangeEnd`, and gate changes through View As and deployment pipelines.
Want to check your own model? Run our free Power BI Model Review: thirteen questions, a score out of 100, and your top three risks with the fix for each. If your top-10 tiles still won't reconcile after that, book a semantic-model and AI-readiness review with us and we'll walk your measures with you.
Read next
KPI and data modeling: the full guide (pillar)
Frequently asked questions
What's RANKX in Power BI in one sentence?
RANKX is a DAX function that ranks rows by a value and returns a rank number, 1 for the top spot. Written as a measure, the ranking recalculates for the exact slice a user is viewing, which is what keeps a "top 10" tile correct when someone filters.
Why is every row showing rank 1?
Almost always the first argument is wrong. If you rank over the current row instead of the whole table, RANKX has nothing to compare against and returns 1 everywhere. Wrap the column in ALL or ALLSELECTED so RANKX ranks across all the rows you mean.
What's the difference between ALL and ALLSELECTED in RANKX?
ALL ignores every filter and ranks across the whole table, giving a fixed global leaderboard. ALLSELECTED respects the user's slicer and ranks only within what they picked. Use ALLSELECTED when a manager filters to a region and expects the top products in that region.
How do I handle ties in a RANKX result?
Add DENSE as the last argument. With DENSE, two products tied for second are both rank 2 and the next one is rank 3, with no gap. Without it, the rank after a tie jumps ahead, which looks wrong to anyone reading the tile.
Do I need Microsoft Fabric to use RANKX?
No. RANKX runs on Power BI Pro and Premium without Fabric. Fabric adds Direct Lake mode and OneLake storage, which help with very large or very fresh tables, but the measure you write is the same. For most company dashboards an Import model on Pro is plenty.
