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

 

  • jgiolli's avatar
    jgiolli
    Regular Visitor
    4 years ago

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

      Could it be because I am in direct query mode?

      • jgiolli's avatar
        jgiolli
        Regular Visitor
        4 years ago

        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!