Compare two tables and fill column with information when results match

[rangelferreira]

Hello all,

I have two tables:

one named Sites where I have:

Item	Department	Province	District
482964	Loreto	Maynas	Tapiche
741852	Loreto	Ucayala	Contamana

and another one called Geocode where I have:

Ubigeo	Department	Province	District
160509	Loreto	Maynas	Tapiche
160601	Loreto	Ucayala	Contamana

Goal:

I need to have the Ubigeo colum in the Sites table. The Ubigeo value from the Geocode table must be copied to the Sites table when the Department, Province and District values match between the tables. 

Item	Department	Province	District	Ubigeo
482964	Loreto	Maynas	Tapiche	160509
741852	Loreto	Ucayala	Contamana	160601

Notes:

• The Geocode table has 1874 possible combinations of Department / Province / District and the resulting Ubigeo for each (Every distric in Peru has its unique Ubigeo).
• The Items table has 500 items where the Department / Province / District can be duplicated for some of the items.

Can someone help me acchieving this result?

Thank you in advance!!!

[amitchandak]

Create a new column in the table1

new column = maxx(filter(table2,table1[Department] = table2[Department] && table1[Department]=table2[Department] && table1[Department] = table2[Department]), table2[Ubigeo])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[rangelferreira]

Hi @amitchandak thanks, but it is not working:

Custom column ->
maxx(filter(Sites[Department] = Geocode[Department] && Sites[Province]=Geocode[Province] && Sites[District] = Geocode[District]), Geocode[Ubigeo])

Error: Token Literal Expected below the highlited section above:

[amitchandak · Accepted solution]

The table name is missing in filter

maxx(filter(Geocode,Sites[Department] = Geocode[Department] && Sites[Province]=Geocode[Province] && Sites[District] = Geocode[District]), Geocode[Ubigeo])