Breaking RANKX ties - calendar issue?

Hi AC23VM ,

The issue with RANKX ties when filtering by month is likely caused by the interaction between ALLSELECTED, ALLEXCEPT, and the Calendar table. Your current approach attempts to break ties by incorporating a random number and a unique ID, but it doesn’t fully respect the monthly filter context. The solution is to modify the ranking measure to explicitly ensure that RANKX evaluates only the relevant month’s data.

A better approach is to refine the RANKX function by filtering within the selected month. This can be done by applying a FILTER function within ALLSELECTED, ensuring that only data corresponding to the selected Start of Month is considered. The updated measure is:

LowerLevelRootCauseRank =
VAR CurrentCount = 
    CALCULATE(
        COUNTROWS('Outcomes Testing Data'),
        ALLEXCEPT('Outcomes Testing Data', 'Outcomes Testing Data'[Lower Level Root Cause], 'Calendar'[Start of Month])
    )
VAR TieBreaker = 
    MAX('Outcomes Testing Data'[Lower Level Root Cause UniqueID]) +
    MAX('Outcomes Testing Data'[RandomValue])

RETURN
    RANKX(
        FILTER(
            ALLSELECTED('Outcomes Testing Data'),
            'Outcomes Testing Data'[Lower Level Root Cause] IN VALUES('Outcomes Testing Data'[Lower Level Root Cause])
                && 'Calendar'[Start of Month] IN VALUES('Calendar'[Start of Month])
        ),
        CurrentCount * 1000000 + TieBreaker,
        ,
        DESC,
        DENSE
    )

This modification ensures that ranking is performed only within the selected month by applying VALUES('Calendar'[Start of Month]) within the FILTER function. This approach allows RANKX to consider only the entries corresponding to the currently filtered month, eliminating unwanted ties while maintaining ranking consistency. The tie-breaking component, which sums the unique ID and a random value, helps avoid duplicate ranks for entries with the same count.

Best regards,