Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am looking for a way to count the total numnber of duplicate records on a column. Using the sample data below for duplicate "Number", the expected output would be 6 = 4 for 001 + 2 for 003.
Solved! Go to Solution.
Try this formula:
Number of Duplicates =
var summaryTable = ADDCOLUMNS(VALUES('Table'[Column1]), "duplicates", CALCULATE(COUNT('Table'[Column1])))
return SUMX(FILTER(summaryTable, [duplicates] > 1), [duplicates])
If you want everything in one single measure, you can do as
Count Dups only Measure = SUMX( Filter( GROUPBY('Table', [Number], "Dups Cnt", COUNTX( CURRENTGROUP(), 1)), [Dups Cnt] > 1), [Dups Cnt])
You get the same output. I prefer two measure approach, as we can use "Cnts" for other places if needed.
🙂
Try this formula:
Number of Duplicates =
var summaryTable = ADDCOLUMNS(VALUES('Table'[Column1]), "duplicates", CALCULATE(COUNT('Table'[Column1])))
return SUMX(FILTER(summaryTable, [duplicates] > 1), [duplicates])
@vicky_ Do you know how I can adjust this so that it returns "0" instead of "Blank" if the result is no duplicates?
Use COALESCE ...
Say, like this
Count Dups only Measure 3 = COALESCE( SUMX( Filter( GROUPBY('Table', [Number], "Dups Cnt", COUNTX( CURRENTGROUP(), 1)), [Dups Cnt] > 1), [Dups Cnt]) , 0)
Worked perfectly. Thanks!
First of all your expected output is wrong! It should be 7
You can achieve this as two measure approach as below. hope it helps!
First measure to count
Cnts = count('Table'[Number])
Second measure to count duplicates
Count Dups only Measure = SUMX( FILTER ( DISTINCT ( 'Table'[Number] ), [Cnts] > 1 ), [Cnts] )
Sample output:
You're absolutely right. It should be 7. 🙄
Thanks!
If you want everything in one single measure, you can do as
Count Dups only Measure = SUMX( Filter( GROUPBY('Table', [Number], "Dups Cnt", COUNTX( CURRENTGROUP(), 1)), [Dups Cnt] > 1), [Dups Cnt])
You get the same output. I prefer two measure approach, as we can use "Cnts" for other places if needed.
🙂
Makes sense. Thanks!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |