Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
filarap
Helper III
Helper III

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 IDUpdate DateRANKUpdate Distance What I need
117/03/20231  1
118/03/202321 2
125/03/202337 1
126/03/202341 2
127/03/202351 3
209/03/20231  1
213/03/202324 2
215/03/202332 3
230/03/2023417 1
315/03/20231  1
411/03/20231  1
415/03/202324 2
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@filarap 

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@filarap 

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

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @ryan_mayu 

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

Much appreciated

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.