Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Composite Key - Duplicates

Hi All,

 

I have the following table :

 

IDCodeDateComposite
1282014.07.2024 
2129201.06.2024 
3586901.01.2024 
4291020.11.2024 
5293922.06.2024 
5458019.10.2024 
6302015.07.2024 
6101825.10.2024 
6302015.07.2024 
7184602.03.2024 
8108407.03.2024 
9375011.09.2024 
10495012.03.2024 
10495012.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
Need Help
Tips and Tricks
  • samratpbi's avatar
    samratpbi
    1 year ago

    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!

6 Replies

  • samratpbi's avatar
    samratpbi
    Super User
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      samratpbi Thanks actually the COUNTROWS on composite keys does not work

       

      Can you please help..Thanks

       

      • samratpbi's avatar
        samratpbi
        Super User
        1 year ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.