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.
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.
Thank you. Added the Related function fixed it. This gives me a solution that I can now explore and try to make sure I understand it better. I appreciate your help.
Julie