Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

Lookup with if Condition

‎01-21-2019 09:58 AM

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

 

Labels:
Message 1 of 14
13,230 Views
0
1 ACCEPTED SOLUTION
AlB
Super User
Super User
In response to Anonymous

‎01-21-2019 11:44 AM

@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    

View solution in original post

Message 9 of 14
13,116 Views
13 REPLIES 13

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All