topic Re: Conditional Column using IF statement comparing two different columns in two tables in DAX Commands and Tips

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

jgiolli — Wed, 20 Oct 2021 14:07:18 GMT

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

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

DataInsights — Thu, 21 Oct 2021 14:02:22 GMT

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:

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

jgiolli — Thu, 21 Oct 2021 14:27:00 GMT

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

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

DataInsights — Thu, 21 Oct 2021 14:48:34 GMT

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

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

jgiolli — Thu, 21 Oct 2021 15:07:59 GMT

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.

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

jgiolli — Thu, 21 Oct 2021 15:11:54 GMT

Could it be because I am in direct query mode?

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

jgiolli — Thu, 21 Oct 2021 15:24:39 GMT

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!

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

DataInsights — Thu, 21 Oct 2021 15:37:12 GMT

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