Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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!
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!
Thanks @ryan_mayu
This works like a charm 🙂
I now need to figure out what you did there! 🙂
Much appreciated
you are welcome
Proud to be a Super User!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |