Lookup with if Condition

[Anonymous]

Hello,

Can anyone have advice for below?

I am trying to do lookup with if condition and below is my calculated column and I am getting below error

“Function LOOKUPVALUE expects a column reference as argument number 4.”

calcualte Column in  MAIN TABLE = IF(LOOKUPVALUE(Sheet2[Avg],Sheet2[Product Name],[Product],Sheet2[Product Name]<>BLANK(),"Empty"

Main Table		Lookup Table
Product	Avg		Product Name	Avg
A			A	100
B			B	200
C			D	300
E			E

[Greg_Deckler]

I would create a VAR that is your Sheet2 table filtered by [Product Name] <> BLANK() and then filter down from there. So, something like:

Column = 

VAR __table = FILTER(ALL('Sheet2'),[Product Name] = [Product] && [Product Name] <> BLANK())

RETURN

...

I'm not understanding your data though. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[AlB]

Hi @Anonymous

Following what the error message says, look at the "argument 4"  of your lookupvalue:

Sheet2[Product Name]<>BLANK()

I guess you forgot to close the parenthesis for lookupvalue right before that. In any case if you explain exactly what the code needs to do I'd be able to help with the solution. I'm not sure what's required right now. 

[Anonymous]

Hi AIB,

what i am trying to do in lookup table if column Product name is blank then display message "No".

Requirements:

Maine table and Lookup table does not have any relationship.I want Avg from lookup table Avg , if Lookup Table Product name is empty or null then message should say "No".

let me knof if you need more inforamation.

Thanks

[AlB]

Hi @Anonymous

If I understand correctly what you need, try this:

NewAvgColumn =
VAR _LookedUpAvg =
    LOOKUPVALUE ( Sheet2[Avg], Sheet2[Product Name], MainTable[Product] )
RETURN
    IF ( NOT ISBLANK ( _LookedUpAvg ), _LookedUpAvg )

  This will leave blank wherever the name is not found in table2. Note that it is not allowed to have different data types in the same column so the result when not found cannot be text as you were asking for, since the rest is numbers. You could either return a blank as the code does or another number (outside the range of possible results for Avg) that you know means "Not found"

It is a best practice to use a fully qualified name for columns, i.e. including column name: TableName[ColumName] so that a column can be readily differentiated from a measure, which will not use the table name: [MeasureName]

[Anonymous]

Hi AIB,

Please see below final result should look like,

Final Result
Product	Avg
A	100
B	200
C	"N/A" or 0000
E	"N/A" or 0000

[AlB]

@Anonymous

Like I said in my previous post, you can't. A column can only have one data type so if you want to show a zero it will have to be a "normal" one. 

[Anonymous]

Hi AIB,

I have tried to put IF(NOT ISBLANK(00000),_LOOKEDUPAVG) and i am not able to see 00000.Not sure about "Normal".Please explain

    IF ( NOT ISBLANK ( _LookedUpAvg ), _LookedUpAvg )

[AlB · Accepted solution]

@Anonymous

A zero. Simply the number: 0.

NewAvgColumn =
VAR _LookedUpAvg =
    LOOKUPVALUE ( Sheet2[Avg], Sheet2[Product Name], MainTable[Product] )
RETURN
    IF ( NOT ISBLANK ( _LookedUpAvg ), _LookedUpAvg, 0 )

But I'm not sure that's the best option because, how will you tell if the average was actually a zero or the name wasn't present in the other table? I would stick with the blank as we had in the first version    

[Anonymous]

Hi AIB,

Thank you so much for your help.

<AIB>But I'm not sure that's the best option because, how will you tell if the average was actually a zero or the name wasn't present in the other table? I would stick with the blank as we had in the first version 

I have used 00000 and it is my indicator to use in with other calculated column and when end user see 00000 they understand there has no AVG available for the product.

[AlB]

@Anonymous

How have you used 00000?

[Anonymous]

Hi AIB,

calculated column=if (column =00000,"y","n").Once i get "y" and "n" i am will use this for next calculation.

let me know if you need more inforamation.

thanks

[AlB]

@Anonymous

Ok, but 00000 as a number is still  and exactly the same as 0. It will be shown as 0, your users will see 0, not 00000

If you did the following, you will have simple zeros in your column. Don't you?

NewAvgColumn =
VAR _LookedUpAvg =
    LOOKUPVALUE ( Sheet2[Avg], Sheet2[Product Name], MainTable[Product] )
RETURN
    IF ( NOT ISBLANK ( _LookedUpAvg ), _LookedUpAvg, 00000 )

[Anonymous]

Hi AIB,

It works perfectly fine but how can i display "N/a" or 0000 when when name not found in table 2.

<AIB>This will leave blank wherever the name is not found in table2.