Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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!
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.
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!
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
Proud to be a Super User!
Paul on Linkedin.
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!
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |