Forum Discussion
Conditional Column using IF statement comparing two different columns in two tables
I am trying to compare two columns which include revenue from two different tables. Column 1 is project amount and it would contain revenue for projects from our CRM tables. Column 2 is revenue fro...
Try this calculated column in the ERP table PA01201. It performs a lookup using Date.
New_revenue =
VAR vAmountERP = PA01201[PARetainer_Fee_Amount]
VAR vAmountCRM =
LOOKUPVALUE ( New_Project[New_revenue], New_Project[Date], PA01201[Date] )
VAR vResult =
IF ( vAmountERP = 0, vAmountCRM, vAmountERP )
RETURN
vResult
CRM table New_Project:
ERP table PA01201:
Its saying that LOOKUPVALUE is not a function and as a result its not letting me lookup a field. Also, instead of date we want to compare based on New_Project[Project_Number].
Are you creating a calculated column? LOOKUPVALUE should be available.
To lookup on a different column, simply replace Date with Project_Number:
LOOKUPVALUE ( New_Project[New_revenue], New_Project[Project_Number], PA01201[Project_Number] )