Forum Discussion

chrisv08's avatar
chrisv08
Frequent Visitor
4 years ago

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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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/ba-p/1361529

chrisv08's avatar
chrisv08
Frequent Visitor
4 years ago

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.