Forum Discussion

TrentS's avatar
TrentS
Helper IV
7 years ago
Solved

Lookupvalue help

I am returning to a previous issue (https://community.powerbi.com/t5/Desktop/Logic-help-IF-Switch-Contains/m-p/622072#M296983) with an update. (Thanks Greg for that previous help.) It works with the...
Need Help
  • TrentS's avatar
    TrentS
    7 years ago

    Thanks JSH, I will experiment with that. Might be a cleaner method and I can gain some education in it.

    I did find a solution just a minute ago. A combination of IF and ISBLANK works.

    = IF(
    ISBLANK(
    LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
    ), 'Server'[Customer_Name],
    LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
    ), 'Server'[Customer_Name]
    ))
jsh121988's avatar
jsh121988
Microsoft Employee
7 years ago

I didn't read your whole issue, but I read your logic and summary. Try this:

CalcColumn = // On the Server table 
VAR sCleanName = LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], Server[Customer_Name], "NotFound")

RETURN IF(sCleanName = "NotFound", Server[Customer_Name], sCleanName)

 

  • TrentS's avatar
    TrentS
    Helper IV
    7 years ago

    Thanks JSH, I will experiment with that. Might be a cleaner method and I can gain some education in it.

    I did find a solution just a minute ago. A combination of IF and ISBLANK works.

    = IF(
    ISBLANK(
    LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
    ), 'Server'[Customer_Name],
    LOOKUPVALUE(Excel[Clean_Name], Excel[Customer_Name], 'Server'[Customer_Name])
    ), 'Server'[Customer_Name]
    ))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey TrentS, 

       

      I am also facing a similar problem and I tried the solution you had suggested. I am getting this error "A table of multiple values was supplied where a single value was expected. " 

      This is what i am trying to do: I am trying to do a lookup with if condition where, if there is a match between ID from table 1 and table 2, i get the corresponding status if not i assign a value, else if there is a match but the status column is empty then i have to assign a new value. 

       

      I have two tables: table1 and table 2
      table 2 has ID, status

      table 1 has ID

      column = 

      column = IF(ISBLANK(
      LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
      ), "No", LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
      )
       
      i also used this formula : 
      column  =
      var lookup =
      LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
      var compareVaraiable = BLANK()
      return
      IF(lookup = compareVaraiable, "No")

      I have also attached the pbix file here. It would be great if you have ideas to resolve this!
      • TrentS's avatar
        TrentS
        Helper IV
        6 years ago

        Hi Anonymous ,

         

        I created a plain pbix to test the syntax/logic and it seemed to work fine with both a value (7) not found in table and a value with a blank status.

        I will try to take a peek at your file here shortly.

         

        TrentS