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 () )
    )

[MeiYing]

Hi @Rice, 

I am also having same issue like yours and can help to explain this part below? 

   IF (
        ISBLANK ( 'TopN'[TopN Value] ),
        ranks,
        IF ( Ranks <= 'TopN'[TopN Value], [Category Count], BLANK () )
    )

[Rice]

It's essentially saying:

If topN Value is blank display all ranks, otherwise display only the categories with a rank less than the topN parameter set. As to why I chose that route 4 years ago, I can't recall! 

Parameter documentation for DAX: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

I would also recommend starting your own post though instead of reviving something from 4 years ago.