cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rice
Frequent Visitor

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.

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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

 

 




Lima - Peru

View solution in original post

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

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:

6.PNG

Hope this helps.

 

Best Regards,

Giotto

Vvelarde
Community Champion
Community Champion

@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

 

 




Lima - Peru

View solution in original post

Rice
Frequent Visitor

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.