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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you TrentS! I also tried it in another sample file and the following logic works fine.

         

        new status =
        var lookup =
        LOOKUPVALUE('table2'[status], 'table2'[ID], 'table1'[ID])
        var compareVaraiable = BLANK()
        return
        IF(lookup = compareVaraiable, "No ", lookup)
         

        I was able to assign values.  In that case,  am not sure why its not working for my main file. Do you have any guesses? The error is "A table of multiple values was supplied where a single value was expected."

         

        Thanks for your help!