LookupValue matching multiple columns error

[DataScope06]

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

[DataInsights · Accepted solution]

@DataScope06,

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] )

[DataScope06]

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