Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
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 + _CountIfExists
With 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.
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.
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 + _CountIfExists
With 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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |