Filtering based on 2 sets of criteria of the same columns

[KMZ_ESS]

Dear all,

can someone help me with a measure? Please see sample data below. What I want is to return the accounts with the following criteria:

- (campaign = campaign1 & "reaction" does not contain data) & (campaign = campaign2 & "reaction" contains data) OR ((campaign = campaign1 & "reaction" does not contain data) & (campaign = campaign2 & "reaction" does not contain data))

Hope that makes sense. The result for the sample data should be Account2, Account3 and Account 5.

Thanks a lot! 🙂

Account	Campaign	Reaction
Account1	Campaign1	reaction1
Account1	Campaign2
Account2	Campaign1
Account2	Campaign2	reaction1
Account3	Campaign1
Account3	Campaign2	reaction2
Account4	Campaign1	reaction1
Account4	Campaign2	reaction3
Account5	Campaign1
Account5	Campaign2

[v-yiruan-msft · Accepted solution]

Hi @KMZ_ESS ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _count1 =
    CALCULATE (
        COUNT ( 'Table'[Account] ),
        FILTER (
            'Table',
            'Table'[Account] = EARLIER ( 'Table'[Account] )
                && (
                    'Table'[Campaign] = "Campaign1"
                        && 'Table'[Reaction] = BLANK ()
                )
        )
    )
VAR _count2 =
    CALCULATE (
        COUNT ( 'Table'[Account] ),
        FILTER (
            'Table',
            'Table'[Account] = EARLIER ( 'Table'[Account] )
                && ( 'Table'[Campaign] = "Campaign2" )
        )
    )
RETURN
    IF ( _count1 > 0 && _count2 > 0, 'Table'[Account] )

Best Regards