Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have below sample data, need to identify the records whose status changed from Error to Ready (Previous status should be Error, changed to Ready with highest PaRev Number). We need to count the these id's
PaRev | id | Status |
1 | 85df | Error |
3 | 85df | Ready |
2 | 85df | Error |
1 | 49d | Error |
3 | 49d | Ready |
5 | 49d | Ready |
6 | 49d | Ready |
4 | 49d | Ready |
2 | 49d | Error |
1 | 89b1 | Error |
4 | 89b1 | Ready |
2 | 89b1 | Error |
3 | 89b1 | Error |
Result | ||
3 | 85df | Ready |
6 | 49d | Ready |
4 | 89b1 | Ready |
Solved! Go to Solution.
Hi Thanks for sharing the post,
I have tried the Dax query that you have given in our raw data, its partially working however,we found below scenario also found in the output. As per Requirement it shouldn't be counted. For below scenarios highesht PaRev is under Error state.
PaRev | id | Status |
6 | f7a2 | Error |
1 | f7a2 | Ready |
2 | f7a2 | Ready |
3 | f7a2 | Ready |
4 | f7a2 | Ready |
5 | f7a2 | Ready |
3 | 751 | Ready |
5 | 751 | Ready |
7 | 751 | Ready |
6 | 751 | Ready |
8 | 751 | Ready |
9 | 751 | Error |
3 | 700 | Ready |
5 | 700 | Ready |
7 | 700 | Ready |
6 | 700 | Ready |
8 | 700 | Error |
Thank you for helping, i apologize if i am not clear with my requirement, for the below scenarios those id's should not be counted, when i use the dax query that you have sent below is the output
id Status PacRev
700 Ready 7
751 Ready 8
f7a2 Ready 5
Here , For f7a2, highest PaRev is 6, but status is Error, but i need Status should be Ready with Highest PaRev number. Similarly , For 751, Highest PaRev is 9 with status Error,So these ID,s Shouldn't be counted because the highest PaRev number is status is not Ready.
PaRev | id | Status |
6 | f7a2 | Error |
1 | f7a2 | Ready |
2 | f7a2 | Ready |
3 | f7a2 | Ready |
4 | f7a2 | Ready |
5 | f7a2 | Ready |
3 | 751 | Ready |
5 | 751 | Ready |
7 | 751 | Ready |
6 | 751 | Ready |
8 | 751 | Ready |
9 | 751 | Error |
3 | 700 | Ready |
5 | 700 | Ready |
7 | 700 | Ready |
6 | 700 | Ready |
8 | 700 | Error |
write the expected result
For Example,
Output should be only id 85df,
rest all id's shouldn't be counted because highest PaRev number for these id's f7a2,751,700 is "Error"
PaRev | id | Status |
6 | f7a2 | Error |
1 | f7a2 | Ready |
2 | f7a2 | Ready |
3 | f7a2 | Ready |
4 | f7a2 | Ready |
5 | f7a2 | Ready |
3 | 751 | Ready |
5 | 751 | Ready |
7 | 751 | Ready |
6 | 751 | Ready |
8 | 751 | Ready |
9 | 751 | Error |
3 | 700 | Ready |
5 | 700 | Ready |
7 | 700 | Ready |
6 | 700 | Ready |
8 | 700 | Error |
1 | 85df | Error |
3 | 85df | Ready |
2 | 85df | Error |
Result | ||
3 | 85df | Ready |
Thank you so much Ahmed, its working , you are genius, thank you once again