Forum Discussion

Rice's avatar
Rice
Helper I
6 years ago
Solved

RankX Tie Break Alphabetically

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 dis...
Need Help
  • Vvelarde's avatar
    Vvelarde
    6 years ago

    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's avatar
Rice
Helper I
6 years ago

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's avatar
MeiYing
Helper I
2 years ago

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's avatar
    Rice
    Helper I
    2 years ago

    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.