Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
|
Solved! Go to 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
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
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.
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
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]
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 |
@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.
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 )
@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
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.
@Anonymous
How have you used 00000?
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
@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 )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |