Forum Discussion

jgiolli's avatar
jgiolli
Regular Visitor
4 years ago

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

7 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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's avatar
      jgiolli
      Regular Visitor
      4 years ago

      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's avatar
        DataInsights
        Super User
        4 years ago

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