Detect precise status change

[Anonymous]

Hi everyone,
I found different topic about detecting or counting status changes but it didn't help me.

I have this table. Usually, an item goes from "resolved" to "closed"over time, but what I want, is to detect when an item goes from "resolved" to "Active" like the example given above with the item #14440. I simply want the list of Item ID that respect this condition. Maybe with a measure but I can't find/create one that works... 
Can someone help me on this?
Thank you in advance !

[v-xicai]

Hi @Anonymous ,

You can create measure Filter1 like DAX below, then put the Filter1 in the Visual Level Filter of table visual which displays the field [Work Item Id] , setting Filter1 as "is not blank".

Filter1 =
VAR d1 =
    CALCULATE (
        COUNT ( Table1[State] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Work Item Id] = MAX ( Table1[Work Item Id] )
                && Table1[State] = "Resolved"
        )
    )
VAR d2 =
    CALCULATE (
        COUNT ( Table1[State] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Work Item Id] = MAX ( Table1[Work Item Id] )
                && Table1[State] = "Active"
        )
    )
RETURN
    IF ( AND ( d1 > 0, d2 > 0 ), 1, BLANK () )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi! Thank you for your answer.
I supposed that :

Table1[Work Item Id] = "Active"

 was meant to be :

Table1[State] = "Active"

because my id would never be a string, but even with this changed it doesn't works. I don't know how we could detect a precise status change from one day to another without writing something with my date column  

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.