Ranked Index Skipping Numbers

[jbeckham812]

Hi All,

I currently have a table with an index column and 2 calculated columns based off of it. The current way I have the formulas written for the calculated columns is: 

RI - Sav = if(Containers[Destination]<>"Sav",blank(),RANKX(Containers,Containers[Received Index],,ASC))

RI - Per = if(Containers[Destination]<>"Per",blank(),RANKX(Containers,Containers[Received Index],,ASC))

This returns values as follows:

Destination	Received Index	RI - Sav	RI - Per
Sav	1	1
Sav	2	2
Sav	3	3
Sav	4	4
Per	5		5
Per	6		6
Sav	7	7
Per	8		8

I would like the table to look as follows:

Destination	Received Index	RI - Sav	RI - Per
Sav	1	1
Sav	2	2
Sav	3	3
Sav	4	4
Per	5		1
Per	6		2
Sav	7	5
Per	8		3

Could someone please help me understand how my RI calculated columns need to be modified in order to make this work?

Thanks in advance!

[v-luwang-msft · Accepted solution]

Hi @jbeckham812 ,

Test the following dax:

RI - Sav2 = if(Containers[Destination]="Sav",RANKX(Filter(Containers,Containers[Destination] = "Sav"),Containers[Received Index],,ASC))

RI - Per = if(Containers[Destination]="Per",RANKX(Filter(Containers,Containers[Destination] = "Per"),Containers[Received Index],,ASC))

Final get the below:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[parry2k]

@jbeckham812 here it is :

RI - Sav = if(Containers[Destination]="Sav",RANKX(Filter(Containers,Containers[Destination] = "Sav"),Containers[Received Index],,ASC))

RI - Per = if(Containers[Destination]="Per",blank(),RANKX(Filter(Containers,Containers[Destination] = "Per"),Containers[Received Index],,ASC))

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡