Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Error when using LOOKUPVALUE with 2 search values

I have two tables, without relationships, setup as shown below. I want to pull the corresponding data from Table 2, Col C into Table 1 (ideally with DAX), based on both ID 1 and 2—matching data shown in color below.

 

My code looks like this: LookupValue(Table 2[Col C], Table 2[ID 1], Table 1[ID 1], Table 2[ID 2], Table 1[ID 2])

I get this error:  "A table of multiple values was supplied where a single value was expected."

 

Table 1

Table 2

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Create one key column by concatenating ID1&ID2 in both table and then LOOKUPVALUE using the key column.

    Key = Table2[ID1]&Table2[ID2]

    Best Regards,
    Mail2inba4

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

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    Anonymous 

     

    You have to create a new column in Table 1 as below.

     

    New Column = CALCULATE(VALUES('Table 2'[COL C]),FILTER('Table 2','Table 1'[ID]='Table 2'[ID] && 'Table 1'[ID2]='Table 2'[ID 2]))
     
    If this helps, mark it as a solution.
    Kudos are nice too.
     
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can create calculate column use following formula to meet your requirement if there are multi matched rows in Table 2:

     

    Column =
CALCULATE (
    MAX ( 'Table 2'[COL C] ),
    FILTER (
        'Table 2',
        'Table 1'[ID 1] = [ID 1]
            && 'Table 1'[ID 2] = [ID 2]
    )
)

     

    Or

     

    Column =
CALCULATE (
    MIN ( 'Table 2'[COL C] ),
    FILTER (
        'Table 2',
        'Table 1'[ID 1] = [ID 1]
            && 'Table 1'[ID 2] = [ID 2]
    )
)

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the help. I'm trying to move the corresponding values from Table 2 Col C to a new column in Table 1 and this solution doesn't seem to work, nor does the one above. One challenge is that I cannot filter out duplicates for Table 2 Col C because all values are between 1 and 5 (so there are technically duplicates.

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Could you please What the error occored in formula when you follow the suggestions mentioned in my original post?


        Best regards,