Urgency to identify the Records/Id's whose status changed from Error to Ready(Based on PaRev)

[Anonymous]

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

[Ahmedx]

pls try

[Anonymous]

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

[Ahmedx]

pls try

[Anonymous]

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

[Ahmedx]

write the expected result

[Anonymous]

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

[Ahmedx · Accepted solution]

pls try

[Anonymous]

Thank you so much Ahmed, its working , you are  genius, thank you once again