topic Re: How do I rank values within groups ignoring blanks/zero according to its returned sequence? in DAX Commands and Tips

How do I rank values within groups ignoring blanks/zero according to its returned sequence?

salfa_an — Mon, 27 Feb 2023 07:27:53 GMT

Here is my dummy table

Product	Returned Sequence	Number	Price	Rank	wanted Rank
A	1	121	400€	1	1
A	2	150	0€
A	3	892	0€
A	4	563	0€
A	5	892	700€	5	2
A	5	892	1000€	5	2
A	5	892	100€	5	2
B	1	801	100€	1	1
B	1	801	5000€	1	1
B	2	653	0€
B	3	788	300€	3	2
B	3	788	0€

I calculated the Rank Column with:

Rank = IF(Table1[Cost] <> 0, RANKX(FILTER(Table1, Table1[Product] = EARLIER(Table1[Product])), Table1[Returned Sequence],, ASC, Dense))

Is there any other formula/workaround to get the rank like in the wanted rank column? Thank you!

Re: How do I rank values within groups ignoring blanks/zero according to its returned sequence?

OwenAuger — Mon, 27 Feb 2023 08:12:43 GMT

Hi salfa_an

Here are a couple of options:

1. Modify your original expression to ensure Cost <> 0 within FILTER:

Rank 2 = IF ( Table1[Cost] <> 0, RANKX ( FILTER ( Table1, Table1[Product] = EARLIER ( Table1[Product] ) && Table1[Cost] <> 0 ), Table1[Returned Sequence], , ASC, DENSE ) )

2. Rank over a table of distinct values of Returned Sequence, created using CALCULATETABLE:

Rank 3 = IF ( Table1[Cost] <> 0, VAR RankingTable = CALCULATETABLE ( VALUES ( Table1[Returned Sequence] ), ALLEXCEPT ( Table1, Table1[Product] ), Table1[Cost] <> 0 ) RETURN RANKX ( RankingTable, Table1[Returned Sequence], , ASC ) )

Do these work for you?

Regards

Re: How do I rank values within groups ignoring blanks/zero according to its returned sequence?

salfa_an — Mon, 27 Feb 2023 08:26:45 GMT

Hi OwenAuger
i tried your first solution and it works. thank you so much