Forum Discussion
Unique Rank for column
Hey all,
Im trying to create Unique rank(Ordinal), like the row_nubmber() function in Sql server.
Data set example:
| Sub-Category | Value | RANKX | Target Rank |
| A | 10 | 1 | 1 |
| B | 9 | 2 | 2 |
| C | 8 | 3 | 3 |
| D | 7 | 4 | 4 |
| E | 7 | 4 | 5 |
| F | 7 | 4 | 6 |
| G | 4 | 5 | 7 |
| H | 3 | 6 | 8 |
| I | 3 | 6 | 9 |
I saw the RADACAD blog about that:
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
But I do not use a Date field.
Any suggestions for the Target rank?
Thanks 🙂
Hi Anonymous
with a calculated column you can do it like this :
Distinct Rank without Ties = VAR _Rank = RANKX(ALL('Table'),'Table'[Value],,DESC) VAR _CountIfExists = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Value] = EARLIER('Table'[Value]) && 'Table'[Sub-Category] <= EARLIER('Table'[Sub-Category]))) - 1 RETURN _Rank + _CountIfExistsWith kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create two measures as below.
R = var r1 = RANKX( ALL('Table'), CALCULATE(SUM('Table'[Value])) ) var r2 = RANKX( FILTER( ALL('Table'), 'Table'[Value]=SELECTEDVALUE('Table'[Value]) ), CALCULATE(MAX('Table'[Sub-Category])),,ASC ) return r1*100+r2 Rank = RANKX( ALL('Table'), [R] )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous
with a calculated column you can do it like this :
Distinct Rank without Ties = VAR _Rank = RANKX(ALL('Table'),'Table'[Value],,DESC) VAR _CountIfExists = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Value] = EARLIER('Table'[Value]) && 'Table'[Sub-Category] <= EARLIER('Table'[Sub-Category]))) - 1 RETURN _Rank + _CountIfExistsWith kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)Hey FrankAT ,
I found on the internet the answer -
All I needed to do is to add a Random Var and add it to the Rank.
Thanks!
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create two measures as below.
R = var r1 = RANKX( ALL('Table'), CALCULATE(SUM('Table'[Value])) ) var r2 = RANKX( FILTER( ALL('Table'), 'Table'[Value]=SELECTEDVALUE('Table'[Value]) ), CALCULATE(MAX('Table'[Sub-Category])),,ASC ) return r1*100+r2 Rank = RANKX( ALL('Table'), [R] )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.