Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

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

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.