RANKX

[Anonymous]

After trying the full day, I'm hopeless to find the answer, how to rank for one NAME and DATE over the BATCH

batch (number)	date (date)	name (text)	rank
10	1/1/2022	abc	1
22	1/1/2022	abc	2
22	1/1/2022	abc	2
22	1/1/2022	abc	2
46	1/1/2022	abc	3
46	1/1/2022	abc	3
58	5/1/2022	abc	1
58	5/1/2022	abc	1
67	5/1/2022	abc	2

trying all combination of  RANX, FILTER,ALLSELECTED... it returns '1' value only

please help

thanks a lot!

[rob7one]

What if i would like to do the rank for one coulmn more? For example to create the rank for the batch column for month, name and additional location (paris, berlin, rome, new york, hongkong etc.). Is this possible?

[Shaurya · Accepted solution]

Hi @Anonymous,

Since you want to rank the Batch column for a particular Name and Date, you'll have to consider all the other records in the Name and Date column with that value. Therefore, you'll have to use the EARLIER() function along with RANKX() and FILTER().

Use this DAX Formula:

Rank = RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Name]=EARLIER('Table'[Name])),'Table'[Batch],,ASC,DENSE)

Here's the result:

 

Works for you? Mark this post as a solution if it does!

[Anonymous]

Measure 4 = RANKX(ALL('DATE', 'NAME'), calculate(MAX(NUMBER)))

...this is one of my thousends versions which work but return only '1' value