Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
aewing13
Frequent Visitor

Multiple Duplicate Tests

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.

 

aewing13_0-1713547667997.png

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1713599431175.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.

Ashish_Mathur_0-1713599431175.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

This works perfectly. Thank you!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.