Rank System

Hey bparker_ntst ,

Your approach with an unconnected rank table and custom measures is a clever and often effective method for building dynamic rankings in Power BI. However, the problem you're encountering where complex filters and slicers interfere with the expected behavior is common. Here's a structured breakdown of the issue and how to resolve it. When you use unconnected tables and DAX measures, the ranking logic operates based on the evaluation context, which is influenced by slicers, filters, and visual-level filters. If your ranking measure isn't designed to ignore or override certain filters, then those filters limit the rows being ranked, causing unexpected results.

Solutions:

1. Use ALL() to Control Context Explicitly

Your rank measure should use ALL() or REMOVEFILTERS() to override slicers/filters. Example:

RankMeasure = 
RANKX(
    ALL('MainTable'[TextColumn]), 
    CALCULATE(SUM('MainTable'[ValueColumn]))
)

This ensures ranking is done over all rows, ignoring current filters.

If you need to preserve certain filters (e.g., region, year), you can use ALLSELECTED() or REMOVEFILTERS() selectively:

RankMeasure = 
RANKX(
    REMOVEFILTERS('MainTable'[TextColumn]), 
    CALCULATE(SUM('MainTable'[ValueColumn]))
)

2. Total Value by Rank Measure

If your rank table is disconnected, you can still link it using DAX logic. For example:

TotalByRank = 
CALCULATE(
    SUM('MainTable'[ValueColumn]),
    FILTER(
        ALL('MainTable'),
        [RankMeasure] = MAX('RankTable'[Rank])
    )
)

This finds all rows in the main table that match the current rank and sums their values.

3. Use ISINSCOPE() for Complex Visual Behavior

If visuals have hierarchy or complex drill-downs, use ISINSCOPE() to modify behavior:

RankMeasure = 
IF(
    ISINSCOPE('MainTable'[TextColumn]),
    RANKX(
        ALL('MainTable'[TextColumn]),
        CALCULATE(SUM('MainTable'[ValueColumn]))
    )
)

4. When Filters Must Be Respected (Partial Override)

Sometimes you want to respect some filters but still rank globally. Try ALLSELECTED():

RankMeasure = 
RANKX(
    ALLSELECTED('MainTable'[TextColumn]), 
    CALCULATE(SUM('MainTable'[ValueColumn]))
)

This allows slicers like Year, Region to be respected, but ranks over what's visible in those filters.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam