Forum Discussion
Match multiple columns and find corresponding values using DAX
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?
Remove the comma from before the parenthesis that ends CALCULATE.
Best
Darek
