Count distinct id when the status changes from A to B

[rohit18]

Hi all,

wanted to count the distinct Lead_id when it changed it state from qualified to Inactive.

attached image for refrence 

[v-rzhou-msft · Accepted solution]

Hi  @rohit18 ,

Here I suggest you to try this code to create a measure.

Count = 
VAR _leadid_list =
    CALCULATETABLE (
        VALUES ( 'Table'[lead_id] ),
        FILTER (
            'Table',
            VAR _QualifiedDate =
                CALCULATE (
                    MIN ( 'Table'[date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[lead_id] ),
                        'Table'[change] = "Qualified"
                    )
                )
            VAR _InactiveDate =
                CALCULATE (
                    MAX ( 'Table'[date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[lead_id] ),
                        'Table'[change] = "Inactive"
                            && 'Table'[date] > _QualifiedDate
                    )
                )
            RETURN
                _QualifiedDate <> BLANK ()
                    && _InactiveDate <> BLANK ()
        )
    )
RETURN
    COUNTAX ( _leadid_list, [lead_id] )

Result is as below.

Best Regards,
Rico Zhou

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