Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
vehloaaron
Frequent Visitor

Countifs in Power Bi - Tried solutions from community now stuck

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: 

EmailOpt out 1Opt Out 2New Opt Out 1 New Opt Out 2
email@email.com0101
email@email.com0001
email123@email.com1010
email123@email.com0010
otheremail@email.com0000
duplicate@email.com0010
duplicate@email.com1010

 

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)PBi SS.png

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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
    )
)

vzhangti_0-1647927043292.png

 

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
    )
)

vzhangti_1-1647927174171.png

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.

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

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
    )
)

vzhangti_0-1647927043292.png

 

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
    )
)

vzhangti_1-1647927174171.png

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
Super User
Super User

I think you want something like this:

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

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}})

 

vehloaaron_0-1647735830284.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.