Filling a column based on other tables column values

[Anonymous]

Hi. I can't figure out how to copy values based on other table column values.

How do i copy the values of [Cost] from 'ProductCostHistory' to [UnitCost] in 'OrderDetail' WHERE the [OrderDate] of 'OrderHeader' is BETWEEN [StartDate] and [EndDate] of 'ProductCostHistory'? Preferably in DAX.

If i can get the values, then i can calculate the profit. I can't use the [Cost] of 'Product', because that is the most latest value, and most orders happened before the latest value.

Thank you!

[Jihwan_Kim · Accepted solution]

Hi,

Please correct me if I wrongly understood your data model.

I could not find any date column in the OrderDetail Table, so I brought it from SalesSalesOrderDetail Table.

And I wrote the below for the Calculated Column.

UnitCost =
VAR currentproducid = OrderDetail[ProductID]
VAR currentdate = OrderDetail[Date]
RETURN
CALCULATE (
SELECTEDVALUE ( ProductCostHistory[Cost] ),
ProductCostHistory[ProductID] = currentproducid
&& ProductCostHistory[StartDate] <= currentdate
&& ProductCostHistory[EndDate] >= currentdate
)

Please kindly check if it is what you are looking for.

thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Julianaz]

This is still helpful. Thanks,

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

If you want to look up the value from not-direct-connected table, perhaps you can try to use LOOKUPVALUE function. Furthermore, if you don't have direct-compare-columns between the two tables, then you can use RELATED inside the LOOKUPVALUE. Because there is one table in the middle.

If it is OK with you, please share your sample pbix file's link, then I can try to look into it and come up with a more accurate measure.

Thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

hi @Jihwan_Kim ,

thank you for the reply. Here is the pbix file: https://easyupload.io/6c896y 
There are errors in the visualizations, but they don't really matter at the moment, i will fix them later. Also, there are 2 models, which are very similar with similar table names. The posted issue is in the second (the right one) model. The second model takes the column values (only certain ones) from the first one. It's just a part of the project.