Countifs in Power Bi - Tried solutions from community now stuck

[vehloaaron]

I have a scenerio where I am trying to find duplicate emails, where on one email someone has opted out (opt out = 1) and the other duplicate email has not (opt out = 0), if it is a duplicate and on one row they have opted out, but the other they have not, I want to identify that and mark the second duplicate row as opted out.  Below are my desired results in excel, and what I have tried in PowerBi.  This seemed to work until I added more rows, then it broke.  Note, I have multiple opt out preferences, so if there is a duplicate email and they have opted out of one, they may not have opted out of the other (ie. opt out 2). Any help would be much appreciated.

Here is the desired result: 

Email	Opt out 1	Opt Out 2	New Opt Out 1	New Opt Out 2
email@email.com	0	1	0	1
email@email.com	0	0	0	1
email123@email.com	1	0	1	0
email123@email.com	0	0	1	0
otheremail@email.com	0	0	0	0
duplicate@email.com	0	0	1	0
duplicate@email.com	1	0	1	0

Here is what I have tried in PowerBi

opt out measure test 1 = CALCULATE(COUNTROWS(Sheet1),Sheet1[Email]=Sheet1[Email],Sheet1[Opt Out 1]=1)

opt out measure test 2 = CALCULATE(COUNTROWS(Sheet1),Sheet1[Email]=Sheet1[Email],Sheet1[Opt Out 2]=1)

[v-zhangti · Accepted solution]

Hi, @vehloaaron 

You can try the following methods.

Column:

New Opt out 1 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Email] = EARLIER ( 'Table'[Email] )
            && 'Table'[Opt Out 1] = 1
    )
)

New Opt out 2 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Email] = EARLIER ( 'Table'[Email] )
            && 'Table'[Opt Out 2] = 1
    )
)

Measure:

New Opt out 1 M = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] )
            && 'Table'[Opt Out 1] = 1
    )
)

New Opt out 2 M = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] )
            && 'Table'[Opt Out 2] = 1
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[HotChilli]

I think you want something like this:

CALCULATE(COUNTROWS(Sheet1), Sheet1[Email] = MIN(Sheet1[Email]), Sheet1[Opt Out 1] = 1) + 0

[vehloaaron]

Thank you.  This looked like it may work but I am getting an error that "Min or Max of booleans are not currently supported", my number formate is Int.64, not boolean.  Any thoughts on that error?

= Table.TransformColumnTypes(#"Removed Other Columns",{{"Email Opt Out SLeA", Int64.Type}})