How to flag duplicates with DAX?

[UsePowerBI]

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!

[parry2k · Accepted solution]

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

[UsePowerBI]

@parry2k 

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

[parry2k]

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

[UsePowerBI]

@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]

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