Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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...
Need Help
MartynRamsden's avatar
MartynRamsden
Solution Sage
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    sjorshijgenaar
    Frequent Visitor
    5 years ago

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