cancel
Showing results for
Did you mean:

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

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.

 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
1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

3 REPLIES 3
Super User

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

Proud to be a Super User!

Helper III

Thanks @ryan_mayu

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

Much appreciated

Super User

you are welcome

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors