Forum Discussion
Get value from another table using intermediary table
I'm using the Microsoft provided Adventureworks data for SQL Server in a Power BI project. I want to get the unit cost of a product but it requires three tables. I was going to create a new column in the SalesOrderDetail to calculate the UnitCost by getting the StandardCost during the correct period (StartDate, EndDate) from the ProductCostHistory table. But I don't know how to reference all three tables.
I need the SalesOrderHeader for the OrderDate and the SalesOrderDetail for the ProductID so that I get the correct historical cost.
Below is the SQL query which gives me the proper relationships.
SELECT SOD.UnitPrice, SOH.OrderDate, PCH.StandardCost, PCH.StartDate, PCH.EndDate, PCH.ProductID, SOD.ProductID
FROM sales.SalesOrderDetail sod
JOIN sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID
JOIN [Production].[ProductCostHistory] pch on pch.ProductID = sod.ProductID
AND soh.OrderDate BETWEEN pch.startdate AND pch.EndDate
Any guidance would be appreciated. Thanks
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.
10 Replies
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] )
Pedantic side note: The sale order date isn't really the best match for looking up a historical price. Ideally, you'd use the purchase date but that requires more detailed inventory tracking than this model appears to support. Assuming inventory turnover is relatively quick, it's not a terrible approximation.
