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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Funk-E-Guy
Helper II
Helper II

LOOKUPVALUE is returning blank when a seemingly unrelated field is null?

It seems that LOOKUPVALUE is returning blank values, but only when a seemingly unrelated field is null. This is strange to me because I thought that it effectively works like a VLOOKUP function regardless of what other fields are (that aren't actively a part of the LOOKUPVALUE function). But please let me know if I'm wrong.

 

I have recreated this issue in a test model with only a few data points.

 

Here is a screenshot of my entire test dataset (pulled from Excel files, and just shown here as PBI tables). The "Problem Table" is generated in DAX (more details under the pictures):

 

All Tables and Null LOOKUPVALUE 2.png


And here are the relationships in my model:

Relationships 2.png


My "Problem Table" is a table that is created purely in DAX. Here's my code:

 

Market Name & Amazon ASIN columns

 

Problem Table = GENERATE(DISTINCT(Markets[Market Name]),DISTINCT('Amazon Items'[Amazon ASIN]))

 

Parent Item ID column

 

Parent Item ID = LOOKUPVALUE('Items (All)'[Item ID], 'Items (All)'[Amazon ASIN], 'Problem Table'[Amazon ASIN])

 

Market Specific Item column

 

Market Specific Item = LOOKUPVALUE('Market ↔ Item Relationships'[Market Specific Item Name], 'Market ↔ Item Relationships'[Parent Item ID], 'Problem Table'[Parent Item ID], 'Market ↔ Item Relationships'[Market Name], 'Problem Table'[Market Name])

 

Marketplace Specific Item ID column this is where null values are being returned.

 

Marketplace Specific Item ID = LOOKUPVALUE('Items (All)'[Item ID], 'Amazon Items'[Item Name], 'Problem Table'[Market Specific Item])

 


I don't get it. I'm just trying to lookup the Item ID of the Marketplace Specific IItem, but for some reason it's returning null values for the INT items.

 

The only thing I can see if that the null LOOKUPVALUE only happens on the items that don't have ASINs, but I wouldn't think that the null ASIN would be forcing the LOOKUPVALUE to be null, would it?

 

If so, is there an alternate way that I can lookup the Marketplace Specific Item ID based on the text only in "Marketplace Specific Item"?

1 ACCEPTED SOLUTION
Jos_Woolley
Solution Sage
Solution Sage

Hi,

Not sure I understand. Your LOOKUPVALUE function is searching in the [Item Name] column of the 'Amazon Items' table, which does not contain TestSKU1INT or TestSKU2INT. So naturally the formula returns blank for those rows.

Regards

View solution in original post

2 REPLIES 2
Jos_Woolley
Solution Sage
Solution Sage

Hi,

Not sure I understand. Your LOOKUPVALUE function is searching in the [Item Name] column of the 'Amazon Items' table, which does not contain TestSKU1INT or TestSKU2INT. So naturally the formula returns blank for those rows.

Regards

Ugh, how embarrasing... Unfortunately that was the problem on my test model, but it appears to be a separate issue on my production model. I will review more and submit a new discussion when I get it figured out.

 

Thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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