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 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.
 

 

 

Need Help

10 Replies

    • 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.

       

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        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??

         

  • MAYOD's avatar
    MAYOD
    Regular Visitor
    4 years ago

    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!