Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Ended up finding the solution here: https://community.powerbi.com/t5/Desktop/lookupvalue-filtering-2-columns/m-p/359973#M162404
Hi @Anonymous ,
Create one key column by concatenating ID1&ID2 in both table and then LOOKUPVALUE using the key column.
Ended up finding the solution here: https://community.powerbi.com/t5/Desktop/lookupvalue-filtering-2-columns/m-p/359973#M162404
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.
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.
Hi @Anonymous ,
Could you please What the error occored in formula when you follow the suggestions mentioned in my original post?
Best regards,
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.
@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])) |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |