I have a fact table of forecasted sales orders and a table of part numbers with corresponding production rates. I would like to calculate the amount of total production hours needed per month by multiplying the forecasted sales amount by the part work rate. Unfornunately I am not able to achieve this without setting all my relationships to bidirectional, which will not be useful going forward as I would like to add in additional tables and info for drilling down, etc.
I am attempting to use measures with CROSS FILTER, but I am not very knowledable with it. How would I get the cross filter to go across multiple tables?
Ex: only between two tables.
ForecastedBacklog = CALCULATE([Backlog_Units_Total],CROSSFILTER(DateDimension[Date],SalesOrder_Backlog[SchedDate],BOTH))
Ideal output (MonthYear col is from the date dimension table):
DateDimension -> SalesOrder_Backlog by Date to SchedDate
SalesOrder_Backlog ->PartTable by PartId
PartTable ->PartWorkRate by PartId
PartWorkRate -> Workcenter by Workcenter Id
WorkCenter -> Line by an index that I derive from a lookup table.