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.⚡

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")
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous currently you have one column and even simple countrows will work but think if you have another column in the table let's say index

 

A 1

A 2

B 3

 

now simple countrows will not work, you will get count of 1 for each row because every row is unique now so using allexcept will only keep the filter on name column (A,B) and remove any other row-level filter. Make sense?

 

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.⚡