Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Lookupvalue if value is blank

Hi, I am currently trying to do something very simple.

 

I want to create a new column, such that it is the same as another column BUT where the 'other' column is blank it performs a lookupvalue function to fill it.

 

Example below:

Table ONE                                   

AB
key1hi
key2bye
key3 
key4ok

 

Table TWO

KeyValue
key1hi
key2bye
key3so
key4ok

 

I want Table ONE to become:

ABC
key1hihi
key2byebye
key3 so
key4okok


I have written this code:

C = IF( isblank( `Table ONE`[B]), lookupvalue( `Table TWO`[Value],`Table TWO`[Key], `Table ONE`[A]), `Table ONE`[B])

 

But for some mysterious reason it doesn't work and Column C becomes exactly the same as Column B.

 

Anyone have a ny ideas would be appreciated.

2 ACCEPTED SOLUTIONS
Greg_Deckler
Community Champion
Community Champion

Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows. 

 

You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

amitchandak
Super User
Super User

You can try, a New Column in Table

new column Table 1= MAxx(filter(Table2,table1[A] =table2[key]),table2[Value])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

You can try, a New Column in Table

new column Table 1= MAxx(filter(Table2,table1[A] =table2[key]),table2[Value])

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Community Champion
Community Champion

Could be a couple of things. Looks like you are using the alternate result parameter for LOOKUPVALUE that makes the value your B column in the even of one of two things. Either LOOKUPVALUE is not finding a single row or LOOKUPVALUE is finding no rows. 

 

You can find out by using COUNTROWS with a FILTER that mimics your LOOKUPVALUE search. That will tell you if you are getting no rows or multiple rows. You could use a MAXX(FILTER(TWO,TWO[Key]=ONE[A]),TWO[Value]) if you are getting multiple rows. Or MINX. If you are getting no rows then perhaps one of your key's has a space at the end or something like that.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors