Forum Discussion
Match multiple columns and find corresponding values using DAX
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?
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
7 Replies
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
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
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.What's wrong in the calculated formula syntax?
