Conditional Column using IF statement comparing two different columns in two tables

[jgiolli]

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 from our ERP system tables.  What I'd like to do is look at Column 2 and for any values that are 0, I'd like to replace that with what is in column 1 in the CRM table.

I tried doing this in measures but the subtotals would not work properly so believe I need a custom column or conditional column.  This is the formula I used for the measure.  Any help would be greatly appreciated.

IF(sum(PA01201[PARetainer_Fee_Amount])= 0, sum(New_Project[New_revenue]), sum(PA01201[PARetainer_Fee_Amount]))

[DataInsights]

@jgiolli,

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:

[jgiolli]

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].  

[DataInsights]

@jgiolli,

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] )

[jgiolli]

I'm going into the field list under the table PA1201, selecting new column and adding the formula you provided.  Its saying LOOKUPVALUE is not a function.  Not sure if there is another place I should be doing creating this column.

[jgiolli]

Could it be because I am in direct query mode?

[jgiolli]

So good news is that this error was occuring because of the direct query which is now fixed.  However, now I'm getting a different error.  Here's the expression as entered:

New_revenue = VAR vAmountERP = PA01201[PARetainer_Fee_Amount] VAR vAmountCRM = LOOKUPVALUE ( New_Project[New_revenue], New_Project[Project Number], PA01201[PAcontid] ) VAR vResult = IF ( vAmountERP = 0, vAmountCRM, vAmountERP ) RETURN vResult

The error says a table of multiple values was supplied where a single value was expected.  Any ideas on how to resolve?  Thanks for your help!

[DataInsights]

@jgiolli,

Verify that you have one row per project.

https://dax.guide/lookupvalue/ 

"If multiple rows match the search values and in all cases Result_Column values are identical then that value is returned. However, if Result_Column returns different values an error is returned."