Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Vlookup - cant find data table

Hi There,

 

I am trying to do what is a very easy vlookup in excel in Power BI but when I type in the formula bar it doesnt find the data table I am looking for, what have I done wrong?!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    RELATED function really need a relationship first, then columns in another table can be fetched in the formula.

    But LOOKUPVALUE function doesn't need a relationship. Usually if you want to fetch a column in Table2, you should create a calculated column in Table1 like this:

    LOOKUPVALUE('Table2'[Column2],'Table2'[Column1],'Table1'[Column1])

    I create a sample, you can refer to the steps to see if you missed something.

    Table1:

    Table2:

    The two tables don't have any relationship. Now I want to fetch the Cost column in Table1, create a calculated column in Table1:

    Column =
LOOKUPVALUE ( Table2[Cost], 'Table2'[Category], 'Table1'[Category] )

    Result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AbbasG's avatar
    AbbasG
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Anonymous Hi, can you share the formula? In Power BI you have to use the LOOKUPVALUE function instead.

    The below references will help you more - 

    There is a LOOKUP function in DAX:

    https://msdn.microsoft.com/en-us/library/gg492170.aspx

     

    Also, you can check this article from here as well, which has various methods of replicating lookup functionality:

    https://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Pow...

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for this. I have tried to use both the lookupvalue function and related but when I type in the column name for the data I am trying to pull from a different datatable the whole table doesnt show. Is is something to do with managing the relationships behind the data tables maybe?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Anonymous ,

    RELATED function really need a relationship first, then columns in another table can be fetched in the formula.

    But LOOKUPVALUE function doesn't need a relationship. Usually if you want to fetch a column in Table2, you should create a calculated column in Table1 like this:

    LOOKUPVALUE('Table2'[Column2],'Table2'[Column1],'Table1'[Column1])

    I create a sample, you can refer to the steps to see if you missed something.

    Table1:

    Table2:

    The two tables don't have any relationship. Now I want to fetch the Cost column in Table1, create a calculated column in Table1:

    Column =
LOOKUPVALUE ( Table2[Cost], 'Table2'[Category], 'Table1'[Category] )

    Result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • AbbasG's avatar
    AbbasG
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Anonymous if you can share sample data/pbix file (after removing the sensitive information), it would be easy to help.