Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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!
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?
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!
User | Count |
---|---|
134 | |
72 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |