Forum Discussion

Rice's avatar
Rice
Icon for Helper I rankHelper 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 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.

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

     

     

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    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
      Icon for Helper I rankHelper 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
        Icon for Helper I rankHelper 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 () )
    )
  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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