Forum Discussion
RANKX
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!
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!
3 Replies
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!
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?
