Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

RANKX DENSE functionality

Dear all,   Every now and then I stumble along the rankx function. This is what I use. Fairly simple.   PRODUCTSCAN = COUNTROWS('MyDataTablev 1) RANKING = RANKX(ALLSELECTED('MyDataTablev 1'[Pro...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

     

    You need to break the RANK TIES.

     

    https://docs.microsoft.com/en-us/dax/rankx-function-dax

     

    The functionality of dense is 

     

    Dense

    The next rank value, after a tie, is the next rank value. For example if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.

     

    You will need a rank breaker.

     

    Use RAND

    RANKING = RANKX(ALLSELECTED('MyDataTablev 1'[Product EAN],'MyDataTablev 1'[language]),[PRODUCTSCAN] + RAND(),,DESC,dense)

     

    else 

     

    RANKING = RANKX(FILTER(ALLSELECTED('MyDataTablev 1'[language] = MAX('MyDataTablev 1'[language])),[PRODUCTSCAN] ,,DESC,dense)

     

     

    Else please share some sample data and the expected output

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI Anonymous ,

 

You need to break the RANK TIES.

 

https://docs.microsoft.com/en-us/dax/rankx-function-dax

 

The functionality of dense is 

 

Dense

The next rank value, after a tie, is the next rank value. For example if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.

 

You will need a rank breaker.

 

Use RAND

RANKING = RANKX(ALLSELECTED('MyDataTablev 1'[Product EAN],'MyDataTablev 1'[language]),[PRODUCTSCAN] + RAND(),,DESC,dense)

 

else 

 

RANKING = RANKX(FILTER(ALLSELECTED('MyDataTablev 1'[language] = MAX('MyDataTablev 1'[language])),[PRODUCTSCAN] ,,DESC,dense)

 

 

Else please share some sample data and the expected output

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous amitchandak ,

 

Thanks for the tips!! my understanding was that "dense" does the job in my example above. So the last entry im my example table should have the ranking number four?