Forum Discussion
Find Duplicates from one table to another.
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.
3 Replies
- amitchandak
Super User
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
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.
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.
