Unique Rank for column

[Anonymous]

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 🙂 

[FrankAT · Accepted 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)

[v-alq-msft · Accepted solution]

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.

[FrankAT · Accepted 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)

[Anonymous]

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!