cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rangelferreira
Frequent Visitor

Compare two tables and fill column with information when results match

Hello all,

 

I have two tables:

one named Sites where I have:

ItemDepartmentProvinceDistrict
482964LoretoMaynasTapiche
741852LoretoUcayalaContamana

 

and another one called Geocode where I have:

UbigeoDepartmentProvinceDistrict
160509LoretoMaynasTapiche
160601LoretoUcayalaContamana

 

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. 

 

ItemDepartmentProvinceDistrictUbigeo
482964LoretoMaynasTapiche160509
741852LoretoUcayalaContamana160601

 

 

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!!!

1 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])

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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:

The table name is missing in filter

 

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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors