Find Duplicates from one table to another.

[chrisv08]

I have these 2 tables one for 2020 and one for 20201 with lets say 5 columns.  It has First Name, Last Name, Location #, Unique Identifier, and Amount Charged.

We're trying to identify customers that have gone from one location one year to a different the following year. So basically at the top I want to have 2 filters, one for 2020 and one for 2021. I select a location for each year and if there are any duplicates, it shows up on the table the name, unique identifier of these duplicates. The unique identifier never changes, even if they go from one location to another.

[Anonymous]

Hi @chrisv08 ,

Can you provide sample data and expected output?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@chrisv08 , With combine these two tables, or create year and location as common dimensions and join with them.

In any case have these two dimensions

Have these measures from two tables (or one table), with common Date/year Table

This Year = CALCULATE(sum('Table1'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table2'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Location Same = countx( Addcolumns(Summarize(Table, Table[Customer], Location[Location]) , "_1", [This Year]

, "_2", [Las year]) , if(not(isblank([_1])) && not(isblank([_2])) , [Customer], blank())

subtract from distinct count of location for changed location

similar approch like

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[chrisv08]

I think this only calculates the amount of duplicates,  would thi also show the actual customers that are duplicate. Like first and last name of each duplicate.