OK, I can't share the actual source data so I will try to be very clear.
I have fact_Inventory table like this:
I have another table, dim_Product that has data like this:
dim_Product[Product_Key] -> fact_Inventory[Product_Key] is the relationship
If I create a column in Inventory table like this:
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!
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.
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.
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])
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])
@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??
Sorry, fact_Inventory = Inventory_v
I have used your dataset and created .pbix but i am not getting any blank value.
Can you please review attached file ?
<iframe src="https://onedrive.live.com/embed?cid=6D54F0A829DEC965&resid=6D54F0A829DEC965%213114&authkey=ACEy9EIs9..." width="98" height="120" frameborder="0" scrolling="no"></iframe>
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.