Count Id's where it has at one time been two different values

[RevOps608]

I have a 'Opportunity Field History' table. Every row contains a stage change for an opportunity. I need to count opportunities Id's where within the 'New Value' been both  "4 - Estimate Generated" and "6 - Closed Won". Because they're not in both stages at the same time, I cannot get my filters to work correctly in my summarize formula. If I use || it counts opportunities that have been in eith stage but not both and && draws and gets zero because they cant be in the stage at the same time. I need to calculate this for both 2023 and 2024.

COUNTROWS(
       SUMMARIZE(
                   FILTER(
           'Opportunity Field History',
           'Opportunity Field History'[NewValue] = "4 - Estimate Generated" ||
           'Opportunity Field history'[NewValue] = "6 - Closed Won"),
           'Opportunity Field History'[OpportunityId],
           'Opportunity Field History'[NewValue],
           'Opportunity Field History'[Created_YR])
)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please try something like below whether it produces the expected result.

expected result measure: =
VAR _t01 =
    SUMMARIZE (
        FILTER (
            'Opportunity Field History',
            'Opportunity Field History'[NewValue] = "4 - Estimate Generated"
                && 'Opportunity Field History'[Created_YR] IN { 2023, 2024 }
        ),
        'Opportunity Field History'[OpportunityId]
    )
VAR _t02 =
    SUMMARIZE (
        FILTER (
            'Opportunity Field History',
            'Opportunity Field History'[NewValue] = "6 - Closed Won"
                && 'Opportunity Field History'[Created_YR] IN { 2023, 2024 }
        ),
        'Opportunity Field History'[OpportunityId]
    )
VAR _intersectlist =
    INTERSECT ( _t01, _t02 )
RETURN
    COUNTROWS ( _intersectlist )

[RevOps608]

Thank you Jihwan_Kim. This worked. I did try a similar version using UNION and count. It wasnt working correctly though. I'll dig into it later. This is the first time I've used INTERSECT.

Thanks again