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.
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.
You are correct, I did actually try this first as a measure even though I meant to create a column. I went back and tried it as a column and received a similar message. Note, I made a couple of change to the script. I referenced SalesOderHeader for [OrderDate] and I changed the date comparisons so [OrderDate] is between the start and end dates.
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.