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

Funk-E-Guy — Fri, 24 Sep 2021 22:48:32 GMT

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):

And here are the relationships in my model:

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"?

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

Jos_Woolley — Sat, 25 Sep 2021 12:25:23 GMT

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

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

Funk-E-Guy — Mon, 27 Sep 2021 15:24:37 GMT

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!

topic Re: LOOKUPVALUE is returning blank when a seemingly unrelated field is null? in DAX Commands and Tips

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

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

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