Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to flag duplicates with DAX?

Hello   Is there a way to create a calculated column which will put a flag when the entry has a duplicate value in a specific column?   E.g.   A, FLAGGED A, FLAGGED B, NOT FLAGGED   Thanks!...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous try something like this

     

    Is Duplicste = 
VAR __cnt = CALCULATE ( COUNTROWS ( Table ), ALLEXCEPT ( Table, Table[Column] ) )
RETURN
IF ( __cnt > 1, "Flagged", "Not Flagged" )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous try something like this

 

Is Duplicste = 
VAR __cnt = CALCULATE ( COUNTROWS ( Table ), ALLEXCEPT ( Table, Table[Column] ) )
RETURN
IF ( __cnt > 1, "Flagged", "Not Flagged" )

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    parry2k 

    thanks, can you tell me please why ALLEXCEPT is necessary? 

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Anonymous ALLEXCEPT remove all other filters except the one that is in the column.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        parry2k 

         

        Thanks but why is this necessary? Why do we need to remove any applied filters on the table apart from that column?

         

        I am not aware of applying any filters in the table.

         

        Why cannot we just use:

         

        IF ( CALCULATE ( COUNTROWS ( Table ), Table[Column] ) ) > 1, "DUPLICATE","NOT DUPLICATE")