Stop Counting/Ranking and start again based on criteria

[Anonymous]

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

[ryan_mayu · Accepted solution]

@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))

[Anonymous]

Thanks @ryan_mayu 

This works like a charm 🙂
I now need to figure out what you did there! 🙂

Much appreciated

[ryan_mayu]

you are welcome