Forum Discussion
Get value from another table using intermediary table
There are a variety of ways to traverse relationships but if you want to avoid worrying about stuff like context transitions and expanded tables, then you can go with a straightforward filter approach that doesn't use the relationships.
Not super efficient but it should work:
UnitCost = VAR ProductID = SalesOrderDetail[ProductID] VAR OrderDate = SalesOrderDetail[OrderDate] RETURN MAXX ( FILTER ( ProductCostHistory, ProductCostHistory[ProductID] = ProductID && ProductCostHistory[StartDate] <= OrderDate && ProductCostHistory[EndDate] >= OrderDate ), ProductCostHistory[StandardCost] )Since OrderDate is coming from a different table, try this:
VAR OrderDate = RELATED ( SalesOrderHeader[OrderDate] )I think I already had the date inequalities correct. Assuming StartDate <= EndDate, you have
OrderDate <= StartDate <= EndDate <= OrderDatewhich can only be true if these are all equal.
Can you show your relationship diagram?
When I download the AdventureWorks .pbix file it looks like this:
Yes, unfortunately there are multiple versions of the Adventureworks database. Here's the data model for the one I'm using. Let me know if the screenshot below works. I know I'm ignoring the Products table, but since I have ProductID in the SalesOrderDetail and ProductCostHistory table, at least in SQL Server, I'm okay.
There are a variety of ways to traverse relationships but if you want to avoid worrying about stuff like context transitions and expanded tables, then you can go with a straightforward filter approach that doesn't use the relationships.
Not super efficient but it should work:
UnitCost = VAR ProductID = SalesOrderDetail[ProductID] VAR OrderDate = SalesOrderDetail[OrderDate] RETURN MAXX ( FILTER ( ProductCostHistory, ProductCostHistory[ProductID] = ProductID && ProductCostHistory[StartDate] <= OrderDate && ProductCostHistory[EndDate] >= OrderDate ), ProductCostHistory[StandardCost] )Well, I was too quick to accept the solution. Reading it through, it seemed perfect. However, Power BI has other thoughts on the matter. I get the following error message.
Not sure why it doesn't like the statement since it is using MAXX.
Are you trying to define this as a measure? Your original post asked for a calculated column.