Forum Discussion

majdkaid22's avatar
majdkaid22
Helper V
10 years ago
Solved

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) 

 

 

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

Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    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.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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

    • majdkaid22's avatar
      majdkaid22
      Helper V
      10 years ago

      Anonymous thanks mate

       

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

       

      How will that work? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

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

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    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.

    • majdkaid22's avatar
      majdkaid22
      Helper V
      10 years ago

      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