Composite Key - Duplicates

[Anonymous]

Hi All,

I have the following table :

ID	Code	Date	Composite
1	2820	14.07.2024
2	1292	01.06.2024
3	5869	01.01.2024
4	2910	20.11.2024
5	2939	22.06.2024
5	4580	19.10.2024
6	3020	15.07.2024
6	1018	25.10.2024
6	3020	15.07.2024
7	1846	02.03.2024
8	1084	07.03.2024
9	3750	11.09.2024
10	4950	12.03.2024
10	4950	12.03.2024

I also have a composite key with the following measure :

[ID] & " | " & [Code] & " | " & [Date]

Now I want to count only the duplicates :

Ex : 6 and 10 IDs are exact duplicates as the composite key is a match.

How can I achieve this in PBI? Thanks

[Anonymous]

@samratpbi Thank you, however the results are not exactly correct and it is due to the date column

It is not considering date in the key. Please advise. Thanks

[Anonymous]

Hi @Anonymous 

Please correct me if I've misunderstood.

In my understanding, samratpbi's method should be correct. Only composite key matches exactly will be counted, in your screenshot the two rows don't match exactly because of the different dates, so Date “22.05.2024” has a duplicate count of 0, while Date “10.04.2024 has a duplicate count of 1, which should be a duplicate of the original data. 

Based on the example data you provided, using samratpbi's method, the test results look correct.

The measures created are as follows:

CompositeKey = [ID] & " | " & [Code] & " | " & [Date]

CountAllRows = COUNTROWS('Table')

CountDistinctRows = DISTINCTCOUNTNOBLANK('Table'[CompositeKey])

DuplicateRowsCount = [CountAllRows] - [CountDistinctRows]

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

[mickey64]

Step 1: I add a column.

Step 2: I make a matrix.

[samratpbi · Accepted solution]

Hi,

If you want to count the duplicates, then you can use the below measures to achieve that.

First, create a measure to count total rows in the table:

Total Count = COUNTROWS(Composite_Count)

Then create a measure to count unique rows based on composite key column you have created:

Unique Count = DISTINCTCOUNTNOBLANK(Composite_Count[Composite])

Then measure 1 - measure 2 will give you duplicate count:

Duplicate Count = [Total Count] - [Unique Count]

If this helps to resolve your problem, then mark this as solution provide, thanks!

[Anonymous]

@samratpbi Thanks actually the COUNTROWS on composite keys does not work

Can you please help..Thanks

[samratpbi]

Hi, Countrows works on table, not column. Hence please provide the table name, it should return total count in that table.

If this helps to resolve your problem, then mark it as solution, thanks!