Breaking RANKX ties - calendar issue?

[AC23VM]

Hi there.  I've been having trouble for a few days trying to break RANKX ties.  I've tried adding in unique identifiers and random numbers, but it's not breaking the ties.  I've had no luck with Copilot, and suggestions here don't seem to be working.  Very likely, it's my issue in not having the DAX right, but I wonder if anyone could please suggest a fix?  In trying to determine a top 5 count, I've got this far:

LowerLevelRootCauseCount =

CALCULATE(

    COUNTROWS('Outcomes Testing Data'),

    ALLEXCEPT('Outcomes Testing Data', 'Outcomes Testing Data'[Lower Level Root Cause], 'Calendar'[Start of Month])

)

LowerLevelRootCauseRank =

RANKX(

    ALLSELECTED('Outcomes Testing Data'[Lower Level Root Cause]),

    CALCULATE(

        COUNTROWS('Outcomes Testing Data'),

        ALLEXCEPT('Outcomes Testing Data', 'Outcomes Testing Data'[Lower Level Root Cause], 'Calendar'[Start of Month])

    ) * 1000000 +

    MAX('Outcomes Testing Data'[Lower Level Root Cause UniqueID]) +

    MAX('Outcomes Testing Data'[RandomValue]),

    ,

    DESC,

    DENSE

)

RandomValue = RAND()

And this is the output:

I added the slicer to point out, it works if I apply all dates - it shows 1-5.  But if I try to look per month, it's full of ties.  I tried to fix this by including the calendar into parts of the formulae, but I might have it wrong.

[DataNinja777]

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,

[AC23VM]

Thanks for this @DataNinja777 

I'm getting this error, where it can't find that column, which of course exists: