Solved - Power BI RANKX returning identical values across filtered hierarchies due to improper ALLSELECTED context nesting in DAX

📌 The Problem Explained

Imagine you are a Sales Operations Manager building a performance leaderboard in Power BI. You have a hierarchy consisting of Region > Manager > Sales Rep. You apply a slicer for “Quarter 3” and expect your table to rank the Sales Reps from 1 to 50 based on their performance.

Instead, every single row in your table shows the number 1.

This “All-Ones” bug occurs because the RANKX function is evaluating the rank within the context of a single row rather than against the entire filtered list. Without the correct context transition—specifically when nesting ALLSELECTED within a hierarchy—Power BI treats every item as its own unique universe, making it the “winner” of its own one-item list. This makes your reports useless for identifying top performers or identifying sales trends.

💡 The Quick Solution

To fix the identical ranking issue, you must use ALLSELECTED to ignore the row-level filters while respecting the slicer filters, and use ISINSCOPE to ensure the rank only calculates at the correct hierarchy level.

Corrected Rank Measure =
IF(
    ISINSCOPE('Sales'[SalesRepName]),
    RANKX(
        ALLSELECTED('Sales'[SalesRepName]),
        [Total Sales],
        ,
        DESC,
        Dense
    ),
    BLANK()
)

🛠️ Step-by-Step Breakdown

Follow these steps to implement a robust ranking system that survives slicers and hierarchical drilling.

Step	Action	Description
1	Define the Base Measure	Ensure you are ranking a measure (e.g., [Total Sales]) rather than a raw column like SUM(Sales[Amount]).
2	Open DAX Editor	Go to the Data or Report view and click New Measure.
3	Apply ALLSELECTED	Use ALLSELECTED('Table'[Column]) as the first argument in RANKX. This tells Power BI to look at all items currently visible in the visual/slicers.
4	Add ISINSCOPE	Wrap the formula in an IF(ISINSCOPE(...)) statement. This prevents the rank from appearing on “Total” rows where it doesn’t make sense.
5	Set Tie-Breaking	Use the Dense argument at the end of the RANKX function if you want consecutive ranking (1, 2, 2, 3) instead of (1, 2, 2, 4).

Navigation Path in Power BI Desktop

1. Select your Table or Matrix visual.
2. Navigate to the Data pane on the right.
3. Right-click your table > New Measure.
4. Paste the corrected DAX formula into the Formula Bar.

⚠️ Common Mistakes to Avoid

• Using ALL instead of ALLSELECTED: If you use ALL, your ranking will ignore your slicers. For example, a rep might be ranked #100 globally even if you have filtered the report to only show their specific local team. Always use ALLSELECTED for interactive reports.
• Inlining the calculation: Avoid writing RANKX(ALLSELECTED(T), SUM(Sales[Amount])... ). DAX requires a “Context Transition” to work correctly in RANKX. By referencing a pre-defined measure (like [Total Sales]), Power BI automatically wraps the calculation in a CALCULATE statement, which is essential for accurate ranking.
• Ranking the wrong column: Ensure the column inside ALLSELECTED matches exactly the column you are using in your visual’s rows. If your visual uses “Full Name” but your DAX uses “Employee ID,” the context will not match, and you will see the “All-Ones” error again.

By mastering these DAX context nuances, you can ensure your Power BI dashboards provide accurate, actionable insights for sales automation and performance tracking.