Forum Discussion

gaurav-narchal's avatar
gaurav-narchal
Helper I
5 years ago
Solved

Identify Duplicate Values in a Table using a measure

Hello

 

How can i identify dupplicate values in a table column (Number) using a measure?

 

Thanks

Gaurav

 

ProductNumber
Drum111
Drum122
Drum123
Drum134
Drum133
Drum133
Glass133
Glass145
Glass134
Plate123
Plate1234
Glass12344
Drum12334

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    You don't need a measure for this. You can do it directly using the table visual by counting the "Number" for each product. You can use a table visual in which you add the Product column and Number column twice. The first Number column will be the Number itself and the second Number column will be aggregated by count as shown below. You can then sort or filter by "Count of Number" on anything greater than 1 which indicates a duplicate.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You don't need a measure for this. You can do it directly using the table visual by counting the "Number" for each product. You can use a table visual in which you add the Product column and Number column twice. The first Number column will be the Number itself and the second Number column will be aggregated by count as shown below. You can then sort or filter by "Count of Number" on anything greater than 1 which indicates a duplicate.

     

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi gaurav-narchal ,

    you can do it like this:

     

     

    Dublicate = 
IF (
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Number] ) ) = 1,
    "-",
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Number] ) )
)


... in Products = 
IF (
    SEARCH (
        ",",
        CALCULATE (
            CONCATENATEX ( VALUES ( 'Table'[Product] ), 'Table'[Product], ", " ),
            ALLEXCEPT ( 'Table', 'Table'[Number] )
        ),
        1,
        0
    ) = 0,
    "-",
    CALCULATE (
        CONCATENATEX ( VALUES ( 'Table'[Product] ), 'Table'[Product], ", " ),
        ALLEXCEPT ( 'Table', 'Table'[Number] )
    )
)

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)