Forum Discussion
Stop Counting/Ranking and start again based on criteria
Hi Everyone,
I need help figuring out how to create DAX Calculated Column formula that will give me the rank based on [Update Date] within group [Case ID], but, it will stop ranking and start ranking again, if [Update Distance] is less than 5.
Below table shows the data I have and the output I am trying to get.
I used different methods using EARLIER / RANK, FILTER...., even aksed CHATGPT for formula, but cannot figure it out.
Please help.
| Case ID | Update Date | RANK | Update Distance | What I need | |
| 1 | 17/03/2023 | 1 | 1 | ||
| 1 | 18/03/2023 | 2 | 1 | 2 | |
| 1 | 25/03/2023 | 3 | 7 | 1 | |
| 1 | 26/03/2023 | 4 | 1 | 2 | |
| 1 | 27/03/2023 | 5 | 1 | 3 | |
| 2 | 09/03/2023 | 1 | 1 | ||
| 2 | 13/03/2023 | 2 | 4 | 2 | |
| 2 | 15/03/2023 | 3 | 2 | 3 | |
| 2 | 30/03/2023 | 4 | 17 | 1 | |
| 3 | 15/03/2023 | 1 | 1 | ||
| 4 | 11/03/2023 | 1 | 1 | ||
| 4 | 15/03/2023 | 2 | 4 | 2 |
Anonymous
pls try this
Column = var _last=maxx(FILTER('Table','Table'[Update Distance]>=5&&'Table'[Case ID]=EARLIER('Table'[Case ID])&&'Table'[RANK]<=EARLIER('Table'[RANK])),'Table'[RANK]) return if('Table'[Update Distance]>=5, 1,if(ISBLANK(_last),'Table'[RANK]-_last, 'Table'[RANK]-_last+1))
3 Replies
Anonymous
pls try this
Column = var _last=maxx(FILTER('Table','Table'[Update Distance]>=5&&'Table'[Case ID]=EARLIER('Table'[Case ID])&&'Table'[RANK]<=EARLIER('Table'[RANK])),'Table'[RANK]) return if('Table'[Update Distance]>=5, 1,if(ISBLANK(_last),'Table'[RANK]-_last, 'Table'[RANK]-_last+1))