How Do RANKX and Drill Through Work in Power BI?
RANKX() is a DAX function that ranks rows of a table based on an expression — such as ranking products by total sales — and is often paired with a Top N filter to spotlight best or worst performers. Drillthrough is a separate Power BI report feature that lets a viewer right-click a data point to jump to a dedicated detail page pre-filtered to that selection.
What RANKX Does in DAX
RANKX() ranks rows of a table based on an expression, such as ranking products by total sales — useful for building leaderboard-style visuals and highlighting top or bottom performers.
Top N Filtering in Power BI
Top N filtering can be applied via a visual-level filter (the “Top N” filter type) or in DAX using TOPN() to return only the highest- or lowest-performing items — useful for dashboards highlighting best-selling products, top customers, or lowest-performing regions.
What Is Drillthrough in Power BI
Drillthrough Filters are a special filter type used on a dedicated drillthrough page, triggered by right-clicking a data point on another report page — the destination page opens pre-filtered to whatever was clicked.
Setting Up and Disabling a Drillthrough Page
A drillthrough page is built like any other report page, with a drillthrough field added to the Drillthrough well in the Visualizations pane; Power BI automatically adds a back button. Drillthrough can be turned off for a specific visual or page through the page’s drillthrough settings when it isn’t wanted.
How to Rank Products and Set Up Drillthrough in Power BI
This tutorial covers two related report-building techniques in about 7 steps: ranking products with RANKX() and Top N filtering, then setting up a drillthrough page so viewers can jump to product-level detail.
Prerequisites
- Power BI Desktop with a Products/Sales-style model
- A Total Sales measure already created
Steps
- Create a RANKX measure: Product Rank = RANKX(ALL(Products), [Total Sales]).
- Add it to a table visual: Place Product Name, Total Sales, and Product Rank together to see the ranking.
- Apply a Top N filter: Use the visual-level Top N filter type, or wrap RANKX in TOPN() for a DAX-based version.
- Sort by rank: Sort the table visual by the Product Rank column so the top performers appear first.
- Create a new drillthrough page: Add a page and drag Product Name into the Drillthrough well in the Visualizations pane.
- Build the detail page: Add product-level visuals (trend, breakdown) that will filter automatically based on the drillthrough selection.
- Test the drillthrough: Right-click a product on the ranked table and confirm it jumps to the filtered detail page with the auto-added back button.
