Multiple Duplicate Tests

[aewing13]

Hi all,

I'm curious if there are any ideal methods for performing multiple duplicate tests on the same table, each testing different columns.

For example, from the below, I'd like to identify duplicates on the following columns 1) ID and Invoice, 2) Date and Amount. Ideally, I'd like a flag field like I've shown below in the DuplicatesFlag1 and DuplicatesFlag2 fields, but I am not sure if that is an option.

My current thought is that I'll have to create separate copies of the table and use the group by functionality for each duplicate test, and then join back to the main table to create the flags.

I appreciate any and all advice! Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

Write these calculated column formulas

Column = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Invoice]=EARLIER(Data[Invoice])))>1)

Column 2 = 1*(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Date]=EARLIER(Data[Date])&&Data[Amount]=EARLIER(Data[Amount])))>1)

Hope this helps.

[aewing13]

This works perfectly. Thank you!

[Ashish_Mathur]

You are welcome.