Error when using LOOKUPVALUE with 2 search values

[Anonymous]

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

[Anonymous]

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.

[Anonymous · Accepted solution]

Ended up finding the solution here: https://community.powerbi.com/t5/Desktop/lookupvalue-filtering-2-columns/m-p/359973#M162404

[v-lid-msft]

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]

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]

Hi @Anonymous ,

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

Best regards,

[Anonymous]

No error, but the related Table 2 Col C values are not being brought over (most are blank). I believe this is because there are many "duplicates" and ID1 needs to be matched before ID2 to find the correct value. ID1 is like a document number and ID2 is the chapter—e.g. there are many entries for a single book (and many books referenced) and all books have a chapter 1 and so on, so both IDs are needed, sequentially, to find correct value in Col C.

Thanks again for the help.

[VasTg]

@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.