LOOKUPVALUE and Random Blanks

[Greg_Deckler]

OK, I can't share the actual source data so I will try to be very clear.

I have fact_Inventory table like this:

Product_Key

367

367

367

444

I have another table, dim_Product that has data like this:

Product_Key,SourceItemId

367,14456

444,55577

dim_Product[Product_Key] -> fact_Inventory[Product_Key] is the relationship

If I create a column in Inventory table like this:

SourceProductID = LOOKUPVALUE('dim_Product'[SourceProductID],'dim_Product'[Product_Key],'fact_Inventory'[Product_Key])

I end up with random blank values for the SAME Product_Key. In other words, for some lines of 367 I will get the correct SourceItemId returned, 14456, but for other rows I will get a blank returned. 

Now, if I use this formula:

SourceProductID = MAXX(FILTER(ALL('dim_Product'),'dim_Product'[Product_Key] = 'fact_Production'[Product_Key]),[SourceProductID])

Has anyone else experienced this? Is there some kind of bug in LOOKUPVALUE?

So bizarre.

[MAYOD]

Hi Greg, FYI I encountered the same problem today for the first time, and your MAXX workaround worked fine for me as well.

Similar scenario where I was trying to add a lookup in my Spend Fact table to lookup a value in the Contract Fact table. Spend Fact not directly related to Contract Fact, and not filtering any other tables, but is filtered indirectly by tables that also filter and are filtered by Contract Fact.

I also tried creating a new calculated table with no connections summarizing the column from my Spend Fact that I'm using as the lookup search value and was able to return the expected value, so it does appear that the issue is related to the table relationships - very strange that it should be affecting calculated columns!

[MartynRamsden]

Hi @Greg_Deckler 

Do you still get blanks if you use RELATED?

Best regards,

Martyn

[Greg_Deckler]

Yeah, can't use RELATED in LOOKUPVALUE. Plus, there is not relationship in that direction.

Yes, just trying to get the ID into the table, saves on measure calculations/processing not having to look those up on the fly every time.

It's mind boggling. Wish I could share the PBIX file as I am wondering if it has something to do with the scale of the data. 

Here is an image of the model, perhaps someone can see a relationship that is causing an issue, I don't see it. Inventory filters nothing.

[MartynRamsden]

Hi @Greg_Deckler 

I wasn't suggesting you use RELATED inside of LOOKUPVALUE but in place of it.

Since you have a 1 to many relationship between dim_Product and Inventory_v, surely you can use the following as a calculated column inside the fact table:

SourceProductID = RELATED ( dim_Product[SourceProductID] )

I appreciate it doesn't explain the weird behaviour you're seeing with LOOKUPVALUE but may give you the results you're after.

Best regards,

Martyn

[Greg_Deckler]

I appreciate your suggestion @MartynRamsden . Unfortunately I cannot use RELATED because the relationship direction is from dim_Products to Inventory and I am trying to create the column in Inventory. I already have a work-a-round using FILTER and MAXX, I just can't explain the behavior of LOOKUPVALUE. 

[sjorshijgenaar]

@Greg_Deckler did you ever manage to find an answer to your question? I have the same issue and see no other option than to use merge queries in Power Query.

In my example I have two calculated columns:

Statuscode = LOOKUPVALUE(DimStatus[Statuscode], DimStatus[StatusKey], DimSnapshot[StatusKey])
AccountNaam = LOOKUPVALUE(DimAccount[Naam], DimAccount[AccountKey], DimSnapshot[AccountKey])

Both columns have random blanks (for Statuscode and Accountnaam) for the same lookup value (StatusKey and AccountKey). In my case the tables only have an indirect relationship through a fact table, so filtering is out of the question. I will try the workaround you present in your question.  

I can now succesfully remove blanks using the following formulas, thanks to @Greg_Deckler:

Statuscode = MAXX(FILTER(ALL(DimStatus), DimStatus[StatusKey] = DimSnapshot[StatusKey]), [Statuscode])
AccountNaam = MAXX(FILTER(ALL(DimAccount), DimAccount[AccountKey] = DimSnapshot[AccountKey]), [Naam])

[parry2k]

@Greg_Deckler bit confused, your original post says FACT_Inventory related with Dim_Product, many to one

In your relationship, don't see FACT_Inventory??

[Greg_Deckler]

Sorry, fact_Inventory = Inventory_v

[Anonymous]

Hi Greg,

I have used your dataset and created .pbix but i am not getting any blank value.

Can you please review attached file ?

https://1drv.ms/u/s!AmXJ3imo8FRtmCqtxaarZBgIkXw3?e=UYn670

<iframe src="https://onedrive.live.com/embed?cid=6D54F0A829DEC965&resid=6D54F0A829DEC965%213114&authkey=ACEy9EIs9..." width="98" height="120" frameborder="0" scrolling="no"></iframe>

[parry2k]

@Greg_Deckler Hey how is going? So Inventory table is just another table in which you want this column?