Forum Discussion
LookupValue matching multiple columns error
I am having issues finding the correct value when matching two text values between tables.
My data model has two tables. The first table is sales_items. The second table is returned_items. I want to match on the order_id and the sku columns. Then find the price for the returned items. The price is found in the sales_items table. There is also a quantity_returned column in the returned_items table. My goal is to add a column to the returned_items table with the price from the sales_items table for each returned sku. This must be matched by the order_id because a sku's price can fluctuate.
My issue: An order can have multiple sku's, but only one of those skus might be returned by the customer. But I am unable to get a correct match between the order_id AND the purchased-returned skus. There is an active relationship between the sales_items[order_id] and returned_items[order_id]. I have an inactive relationship between sales_items[sku] and returned_item[sku].
Using the below code with LOOKUPVALUE, I get an error that says, "A table of multiple values was supplied where a single value was expected."
Returns Sum = LOOKUPVALUE(sales_order_item[price],sales_item[order_id],'Returned_items'[order_id], sales_item[sku],'Returned_items'[sku])
When I use the below code with the CALCULATE function, then the price for the first sku for the matching order_id is returned, regardless if it matches the sku in the returned_items table.
Returns Sum2 = CALCULATE(
max(
sales_item[price])
FILTER(sales_order_item,
Sales_item[order_id] = Returned_items[order_id] &&
sales_order_item[sku] = 'Returned_items'[sku]
)
)
Here are example tables:
Sales_items
| order_id | sku | price | Quantity_ordered |
| 1 | 123 | 10.00 | 1 |
| 1 | 124 | 15.00 | 1 |
| 1 | 125 | 10.00 | 1 |
| 2 | 234 | 5.00 | 1 |
| 2 | 335 | 20.00 | 1 |
| 3 | 123 | 10.00 | 2 |
| 4 | 555 | 25.00 | 3 |
| 4 | 222 | 10.00 | 1 |
returned_items
| order_id | sku | quantity_returned |
| 1 | 123 | 1 |
| 1 | 124 | 1 |
| 2 | 335 | 1 |
| 3 | 123 | 1 |
| 4 | 555 | 3 |
| 4 | 222 | 1 |
Here are two solutions. A relationship between the tables is not required.
1. Calculated column in table Returned_items:
Price = LOOKUPVALUE ( Sales_items[price], Sales_items[order_id], Returned_items[order_id], Sales_items[sku], Returned_items[sku] )2. Calculated column in table Returned_items using a composite key. The composite key "order_id-sku" can be created in Power Query or DAX.
Price (composite key) = LOOKUPVALUE ( Sales_items[price], Sales_items[order_id-sku], Returned_items[order_id-sku] )
2 Replies
Here are two solutions. A relationship between the tables is not required.
1. Calculated column in table Returned_items:
Price = LOOKUPVALUE ( Sales_items[price], Sales_items[order_id], Returned_items[order_id], Sales_items[sku], Returned_items[sku] )2. Calculated column in table Returned_items using a composite key. The composite key "order_id-sku" can be created in Power Query or DAX.
Price (composite key) = LOOKUPVALUE ( Sales_items[price], Sales_items[order_id-sku], Returned_items[order_id-sku] )DataInsights Your solution using LOOKUPVALUE ended up working. Which was the same function I had been trying to use. In my original post I had a typo that I went back and edited. The typo was just in this post though, not my workbook.
My real issue was my dataset did in fact have duplicate records. I was unaware that was possible in the database I pulled the data from. Hence the error stating, "A table of multiple values was supplied where a single value was expected."
Anyone who finds this question should verify their datasets do not have duplicate records if they see the table with multiple values error.
