Lookup of multiple columns

[Anonymous]

Hi

The following sample data simplifies my question. It's just to try to convey what I'm trying to do.

In a nutshell, I'm trying to return value from another table by searching for two values.

The use-case I have, I've materials bought by customers, but each customer has a different contract price.

Here is may sample data:

Transaction Data

Purchase Date	Part#	Customer
1/1/2022	1234	A
3/1/2022	4321	B
5/1/2022	6789	C

Price Table

Customer Name	Part#	Price ($)
A	1234	10
B	1234	15
C	4321	25
D	4321	45
E	6789	33

I wish to add a calculated column to the transaction data table where I can look up the price table for both "Customer Name" and "Part Number". The add the corresponding price to the transaction table as calculated column. As shown in the price table, same part number has different prices depending on the customer.

[amitchandak · Accepted solution]

@Anonymous , Create a new column in Transaction Table

maxx(filter(Price, Price[Customer Name] = Transaction[Customer Name] && Price[Part#] = Transaction[Part#]) , Price[Price ($)] )