Forum Discussion
LOOKUPVALUE and Random Blanks
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 tha...
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.
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])