Forum Discussion
Helper VRank Measure with ID and Date
Hi All,
Below is the sample data:
| ID | Date | Quantity |
| 1 | 01/01/20 | 2 |
| 1 | 01/02/20 | 3 |
| 1 | 01/03/20 | 1 |
| 2 | 01/01/20 | 3 |
| 2 | 01/02/20 | 7 |
| 2 | 01/03/20 | 9 |
| 3 | 01/04/20 | 3 |
| 3 | 01/05/20 | 1 |
| 3 | 01/06/20 | 2 |
below is the output I need: Date Slicer dates selected from 1/2/2020 to 1/6/2020
| ID | Date | Quantity | Rank |
| 1 | 01/02/20 | 3 | 1 |
| 1 | 01/03/20 | 1 | 2 |
| 2 | 01/02/20 | 7 | 1 |
| 2 | 01/03/20 | 9 | 2 |
| 3 | 01/04/20 | 3 | 1 |
| 3 | 01/05/20 | 1 | 2 |
| 3 | 01/06/20 | 2 | 3 |
I want to create a measure to display rank within ID category and rank start from the oldest date selected through a slicer.
Try this one:
Measure = RANKX(FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])),CALCULATE(SUM([Date])),,ASC,Dense)Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
adnanarain , try like
rankx(allselected(Table[Date]), calculate(sum(Table[Quantity])),,desc,dense)
Please find the attached file after the signature.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
adnanarainThanks, Amit for your reply. This ranking is based on date and quantity so basically I need rank based on ID and date no matter what is the quantity, so for example, if we have ID 1, the date is 1st January and the quantity is 5 and in 2nd row, we have ID 1, date 2nd January and quantity is 8 then the rank will be 1 for 1st January and 2 for 2nd January. No matter what is the quantity.
I have tried to clear my point here I hope is clear now.
- adnanarain
amitchandak This is what I want.
Try this one:
Measure = RANKX(FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])),CALCULATE(SUM([Date])),,ASC,Dense)Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
