The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"?
Solved! Go to Solution.
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
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!
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |