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
majdkaid22
Helper V
Helper V

Lookup Value two tables

Hi Community,

 

am trying to find something similar to Vlookup to apply on 2 tables in power bi.

 

I have one table (meaccount) which has unique  values for every client, and I have table (meaccounthistory) that has a column [Country] which I need to add it as table (meaccount) 

 

meaccounthistory.PNGMeaccounts.PNG

 

It's worth mentioning that table (meaccounthistory) has multiple rows for same account, and I would like to distinguish the Country from the latest record

 

I tried using the lookupvalue, but am not succeeding to complete it.

 

Appreciate your help

1 ACCEPTED SOLUTION
konstantinos
Memorable Member
Memorable Member

@majdkaid22 @kcantor Merge queries is easiest way but probably will return multiple rows ( since  we matching values from many to one ) so then remove duplicates on ID and good to go.

 

You can try with DAX also in case you have a column in meAccountHistory , like date or transaction ID that defines the latest record. 

 

Create a calculated column :

 

BringCountry = LASTNONBLANK(meAccountHistory[IsoCountry];MAX(meAccountHistory[Date]))

or

LASTNONBLANK(meAccountHistory[IsoCountry];MAX(meAccountHistory[TransactionID])) 

This way you avoid returning multiple values. 

 

Be careful though that it will bring the latest IsoCountry so if an account change country it will show him as the latest country. Of cource if you use filter from meAccount table.

 

Hope that helps.

Konstantinos Ioannou

View solution in original post

9 REPLIES 9
konstantinos
Memorable Member
Memorable Member

@majdkaid22 @kcantor Merge queries is easiest way but probably will return multiple rows ( since  we matching values from many to one ) so then remove duplicates on ID and good to go.

 

You can try with DAX also in case you have a column in meAccountHistory , like date or transaction ID that defines the latest record. 

 

Create a calculated column :

 

BringCountry = LASTNONBLANK(meAccountHistory[IsoCountry];MAX(meAccountHistory[Date]))

or

LASTNONBLANK(meAccountHistory[IsoCountry];MAX(meAccountHistory[TransactionID])) 

This way you avoid returning multiple values. 

 

Be careful though that it will bring the latest IsoCountry so if an account change country it will show him as the latest country. Of cource if you use filter from meAccount table.

 

Hope that helps.

Konstantinos Ioannou

@konstantinos it worked as a treat. many thanks mate!

 

as you, @kcantor and @Anonymous said, the more proper way is to have it done in query editor and merge both and have (meaccount) with a country column. 

 

Better to save on some less Dax formulas in the front end.

 

 

Many thanks

Majd

Anonymous
Not applicable

Create new column:

 

ISOCountryName = lookup('meAccount'[CountryColumn] , 'meAccount'[AccountID] , 'meAccountHistory'[AccountID )

 

I can't seem to find the country in your meAccount table though. If you have it than the above should work

 

Edit: You should also take the summary off from AccountID in your table as you probably won't be doing any sums with ID's 🙂

Default summarization = Do Not Summarize

@Anonymous thanks mate

 

The country is in the (meaccounthistory) table, and I want to have it added to (meaccount) 

 

How will that work? 

Anonymous
Not applicable

ISOCountryName 2 = lookup('meAccountHistory'[ISOCountryName] , 'meAccountHistory'[AccountID] , 'meAccount'[AccountID )

Result.PNG

 

My challenge here that hiaccounthistory has mutiple records for the same AccountID.

 

How can I trigger this?

Anonymous
Not applicable

Strange setup of data where the account table does not have a country but the history does.

 

History doesn't have a unique set of countries so you have to make them unique either with creating another query where you remove all duplicates.

You're actually right. I will ask the developers to add the country to the account list. 

 

this makes no sense

 

Cheers,

Majd

@majdkaid22 You should pull them both in through Query Editor and merge the Queries to add the column you need. This method makes VLOOKUP unnecessary.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.