Remove Duplicates only if value is 0 in other column

[pbiuser452]

Hey Everyone,

I have a table that I need to remove duplicates on. The Column I need to use is an ID number of the user. I need to remove the duplicate if the ID is a duplicate of another ID, but only remove the row if the value in a different column is 0.

For example:

ID number	Other Value
0000001	4
0000001	0
0000200	5
0000200	2

I only want to remove the '0000001' duplicate where the other value is 0, but keep the '0000200' duplicates since both of the other value's are not 0. 

Please let me know if I need to clarify anything!

[v-chenwuz-msft]

Hi pbiuser452,

In another word, you want to remove duplicates rows with 0.

This my original data:

1 Create a new column

New column =

var countID = CALCULATE(COUNTROWS('TABLE'),FILTER('TABLE',EARLIER('TABLE'[ID number])='TABLE'[ID number]))

var sumValue = CALCULATE(sum('TABLE'[Other Value]),FILTER('TABLE',EARLIER('TABLE'[ID number])='TABLE'[ID number]))

return

If(sumValue=0 && countid>1,1,IF(countID>1 && [Other Value]=0,0,2))

2 Then drag all the fields you need to the table visual and put New column to the filters on this visual.

3 Set Other Value’s feature Don’t summarize

4 Then set the filter’s value is greater then 0.

And the result:

Best Regards

Community Support Team _ chenwu zhu

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

[pbiuser452 · Accepted solution]

Thank you, this helps a ton! I ended up finding out that the data was bad and built a new query to capture the correct data. Thank you so much for your help!

[PaulDBrown]

Try:

First create a measure for the sum of Other Value = SUM(table[Other Value])

then

Remove duplicates with 0 =
VAR IDs = VALUES(table[Id number])

VAR Zero = CALCULATETABLE( VALUES(table [Id number]), FILTER(table, [Sum Other Value] = 0))

RETURN

COUNTROWS( EXCEPT(IDs, Zero))

now create a visual with the Ids and add the measure to the filters in the filter pane and set the value =1

[pbiuser452]

Thank you, this helps a ton! I ended up finding out that the data was bad and built a new query to capture the correct data. Thank you so much for your help!