ISINSCOPE() with RANKX() in Power BI
Table of Contents
Introduction
Building ranking functions in Power BI gets complicated when you deal with data hierarchies like Country-State, Category-Subcategory or Product-Type. The standard RANKX() function will rank all values on the same level, and as a result, it will lead to wrong ranking once we drill down into the hierarchy.
By combining ISINSCOPE() with RANKX(), you can create dynamic rankings that automatically adjust based on the level of the hierarchy being viewed. This approach allows parent-level items to be ranked against other parents while child-level items are ranked only within their respective parent. In this tutorial, you will see how to build a geographical ranking where Country-Region will be ranked globally, and State-Province will be ranked within its selected country.
For more information about building dynamic rankings, check out our related guide: How to Use RANKX for Dynamic Rankings in Power BI.
Note: For reference and practice, the exercise file can be downloaded from the link provided at end.
ISINSCOPE() with RANKX()
To create a geographical ranking with State-Province at the sub level and Country-Region at a higher level:
- Right click on the All Measures table in the Fields
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
Geographical Rank (CM) =
IF (
ISINSCOPE ( Customer[Country-Region] ),
RANKX ( ALLSELECTED ( Customer[Country-Region] ), [Total Sales Amount (CM)] ),
IF (
ISINSCOPE ( Customer[State-Province] ),
RANKX ( ALLSELECTED ( Customer[State-Province] ), [Total Sales Amount (CM)] )
)
)
- ISINSCOPE() is used to test the scope of the current filter context.
- RANKX() than evaluates the Rank for ALLSELECTED[State-Province] and ALLSELECTED[Country-Region] based on the total sales amount.
- Click on the matrix visual in the visualization pane.
- Drag and drop Country-Region and State-Province from the Customer table to the rows.
A hierarchy will be detected by Power BI.
- Drag and drop Geographical Rank (CM) from All Measures table to the Values.
- Click the three dots (⋯) in the top-right corner of the Matrix visual.
- Select Sort by and choose Geographical Rank (CM).
- Select Ascendingto display the ranking from the lowest rank number to the highest.
10. Uncheck the box at the left on Not Applicable in the “Filters on this visual” in the Filters Pane.
When another country is expanded, Power BI recalculates the ranking because the matrix filter context changes. Since RANKX() uses ALLSELECTED(), the measure is evaluated again based on the updated visible selection, which can cause the displayed ranking values to recalculate.
Conclusion
Combining ISINSCOPE() and RANKX() is a great way to produce intelligent rankings in Power BI. This approach increases report usability, allows drill-down analysis, and gives better insight. No matter what kind of hierarchy structure you need to work with, geographical regions, product categories, or organizational structure, this approach allows you to rank every hierarchy level within its context.
FAQs
It checks whether a certain column is present in the visual hierarchy. This helps DAX to calculate differently based on the hierarchy drill-down level.
Their combination makes it possible to generate intelligent rankings, where each hierarchy level is ranked independently from the other.
ALLSELECTED() removes the current row filter while preserving slicer and report filters, allowing RANKX() to rank only the visible items in the current selection.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enroll in
- Microsoft 365 Copilot Training Courses – Practical Learning for Modern Work
- Power BI Advanced Training Course.
- Power BI Report Design
- Power BI Essentials
- Power BI DAX Essentials
- Power BI Service Course
Here, you can access detailed courses and resources designed to enhance your Power BI skills and take your reporting capabilities to the next level.
Downloads
