Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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 <= OrderDate
which can only be true if these are all equal.
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.
@jjolk Is it kindly possible to post the pbix please?
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.
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 <= OrderDate
which 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
User | Count |
---|---|
69 | |
42 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
41 | |
39 | |
28 | |
24 |