topic Get value from another table using intermediary table in DAX Commands and Tips

Get value from another table using intermediary table

jjolk — Mon, 06 Dec 2021 00:42:21 GMT

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

Re: Get value from another table using intermediary table

AlexisOlson — Mon, 06 Dec 2021 01:02:02 GMT

Can you show your relationship diagram?

When I download the AdventureWorks .pbix file it looks like this:

Re: Get value from another table using intermediary table

jjolk — Mon, 06 Dec 2021 01:10:40 GMT

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.

Re: Get value from another table using intermediary table

AlexisOlson — Mon, 06 Dec 2021 01:22:27 GMT

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] )

Re: Get value from another table using intermediary table

smpa01 — Mon, 06 Dec 2021 01:32:11 GMT

jjolk Is it kindly possible to post the pbix please?

Re: Get value from another table using intermediary table

jjolk — Mon, 06 Dec 2021 01:34:02 GMT

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.

Re: Get value from another table using intermediary table

AlexisOlson — Mon, 06 Dec 2021 01:35:17 GMT

Are you trying to define this as a measure? Your original post asked for a calculated column.

Re: Get value from another table using intermediary table

AlexisOlson — Mon, 06 Dec 2021 01:43:13 GMT

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.

Re: Get value from another table using intermediary table

jjolk — Mon, 06 Dec 2021 02:09:44 GMT

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.

Re: Get value from another table using intermediary table

AlexisOlson — Mon, 06 Dec 2021 02:19:30 GMT

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.

Re: Get value from another table using intermediary table

jjolk — Mon, 06 Dec 2021 02:41:09 GMT

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