RankX Tie Break Alphabetically

[Rice]

Hello All,

I'm just looking for a way to break rankx ties alphabetically in DAX based on a secondary column (Such as Category Name) without using an index column. I have a simple table visual displaying the top 10 Categories by the Count of categories. I require rankx to break ties alphabetically based on the category name to display only the top 10 values and no more.

I can break the tie using the random function or based on date, but unfortunately cannot figure out how to break it based on text.

I've investigated many solutions on the web and on this site, but none seem to assist with this specifically.

Any assistance would be much appreciated.

Kind regards.

[v-gizhi-msft]

Hi,

You can try to combine the rankx with category.

Please try this measure:

Measure = MAX('Table'[Category])&"-"&RANKX(ALLSELECTED('Table'),CALCULATE(COUNT('Table'[Category])),,DESC,Dense)

Then apply it to the visual by setting Top 10 based on this measure, it shows:

Hope this helps.

Best Regards,

Giotto

[Vvelarde · Accepted solution]

@Rice 

Hi, Try with this DAX

Note: You need to adjust to Column Names and Top 10 Filter.

Ranking =
RANKX (
    ALLSELECTED ( 'Table'[Pais] );
    'Table'[TotalPoints]
        + DIVIDE (
            RANKX (
                ALLSELECTED ( 'Table'[Pais] );
                CALCULATE ( MIN ( 'Table'[Pais] ) );
                ;
                DESC;
                DENSE
            );
            1000
        )
)

Regards

Victor

[Rice]

This worked like a charm. It seemed my issue was trying to include the category count as a variable instead of a measure. Stupid mistake. Thanks for the help!

Final DAX for me:

Var Ranks = 
    RANKX(
        ALLSELECTED('Table'[Category]),
        [Category Count] + DIVIDE(
            RANKX(
                ALLSELECTED('Table'[Category]),
                CALCULATE(MIN('Table'[Category])),,DESC,Dense), 
                100000))
RETURN
    IF (
        ISBLANK ( 'TopN'[TopN Value] ),
        ranks,
        IF ( Ranks <= 'TopN'[TopN Value], [Category Count], BLANK () )
    )