Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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)
Solved! Go to 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.
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.
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}})
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |