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

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