Match multiple columns and find corresponding values using DAX

[VHosamane]

Here is the scenario that I have and need help with DAX calculated field syntax to arrive with the two fields in the Transaction-Table.

I have a lookup table as shown, which has distinct rows with combination of ClientName and ProjectName.

LookUp-Table

ClientName	ProjectName	ProjectType	Product
Client1	Project1	Tech	SAAS
Client2	Project2	Service	IT
Client1	Project2	Tech	R&D
Client2	Project1	Service	IT
Client3	Project3	Both	SAAS
Client3	Project1	Service	R&D

Transaction-Table

Client Name	Project Name	Project Cost	Emp Name	LookUp EmpType	Timesheet Date	Input Hr	LookUp EmpRate	Calc EmpCost	Calc Product	Calc ProjectType
Client1	Project1	$9,999,999	EmpA	Emp	2-Jan	4	$100	$400	SAAS	Tech
Client1	Project1	$9,999,999	EmpB	Contract	2-Jan	4	$200	$800	SAAS	Tech
Client1	Project1	$9,999,999	EmpC	Emp	2-Jan	4	$100	$400	SAAS	Tech
Client1	Project1	$9,999,999	EmpD	Emp	2-Jan	4	$100	$400	SAAS	Tech
Client1	Project2	$1,000,000	EmpA	Emp	9-Feb	8	$100	$800	R&D	Tech
Client1	Project2	$1,000,000	EmpB	Contract	9-Feb	8	$200	$1,200	R&D	Tech
Client2	Project1	$5,000,000	EmpM	Emp	2-Jan	8	$100	$800	IT	Service
Client2	Project1	$5,000,000	EmpM	Emp	12-Jan	8	$100	$800	IT	Service
Client4	Project1	$7,500,000	Emp A	Emp	10-Apr	8	$100	$800	N/A	N/A
Client1	Project7	$100,000	EmpA	Emp	2-Feb	8	$100	$800	N/A	N/A

Based on ClientName and Project name in Transaction-Table, need to look up ProjectType & Product in the Look-Up table and get the value into the Transaction-Table, Calc-Product & Calc-ProjectType respectively. If the combination of ClientName and ProjectName does not exist, then enter N/A in both the columns.

Can you please help with the DAX calculated field syntax in the Transaction-Table? Would it be possible without joining/ linking the two tables?

[Anonymous]

Yes, it is possible but I'd do it in Power Query as suggested by @HotChilli. If your transaction table is really big, then creating calculated columns like this is a NO-NO.

But one of the columns would be (I don't recommend doing it this way, TT - transaction table, LT - lookup table):

Calc-Product =
var __client = TT[Client Name]
var __proj = TT[Project Name]
var __product = 
	CALCULATE(
		VALUES( LT[Product] ),
		TREATAS(
			{__client, __proj},
			LT[Client Name],
			LT[Project Name]
		),
	)
return
	if( __product = blank(), "N/A", __product )

If this expression errors out somewhere, it means that VALUES( LT[Product] ) returns more than 1 value and you have a problem with duplication in the LT. Bear in mind that this will work OK when there are NO RELATIONSHIPS between the two tables. If there are, then you should use the RELATED function.

Best

Darek

[VHosamane]

What's wrong in the calculated formula syntax?

[Anonymous]

Remove the comma from before the parenthesis that ends CALCULATE.

Best

Darek

[VHosamane]

Thank you @Anonymous & @HotChilli . I tried both solutions and went with solution mentiond by @HotChilli. As @Anonymous  rightly mentioned in the note that the solution would work only if there is no relationship. So it's very important to go through the details. Thank you very much to both of you. 

[VHosamane]

Hi Darek,

Calc-Product = 
var __client = TT[Client_Name]
var __proj = TT[Project_Name]
var __product =
    CALCULATE(
            VALUES(LT[Product]),
            TREATAS(
                {__client, __proj},
                LT[ClientName],
                LT[ProjectName]
                ),
            )
return
    IF(__product = BLANK(), "N/A", __product)

I tried this code, but gets this error: Argument '3' in CALCULATE function is required.

[HotChilli · Accepted solution]

Use Power Query, Merge the queries on ClientName and ProjectName using a LEFT join (with the transaction table first).

Expand the 'Table' column to return Product and Project Type.

Replace any null value with N/A

[calerof]

Hi @HotChilli ,

Thank you very much for this response, I was not aware that it was possible to do nested joints, i.e. merge two tables with more than one key columns. 

Great,

happy learning!

Fernando