Production Capacity Model and Primary/Secondary key columns

[Anonymous]

Hi,

I'm trying to create a production capacity forecast model, where the main fact table contains sales forecast data and several dimension tables contain data on our production plants' production capacity and machine quantities. The main takeaways of the model are as follows.

Fact Table attributes:

• Month-date
• Part type to be produced
• Part quantity to be produced
• Plant at which part is to be produced
• Plant-Part concatenation to link to next dimension table

MachineUtilization:

• Production plant
• Part type
• Primary machine type that the plant and part combination uses
• Secondary machine type that the plant and part combination uses
• % of time spent on primary machine type for the plant/part combination
• % of time spent on secondary machine type for the plant/part combo
• # of parts produced per hour on primary machine type for a given plant/part combo
• # of parts produced per hour on secondary machine type for a given plant/part combo
• Plant-machine type concatenation for plant-primary machine (to link to next dimension table)
• Plant-machine type concatenation for plant-secondary machine (to link to next dimension table)

MachineQuantities:

• Plant
• Machine type
• Quantity of machines at given plant

What I'm trying to achieve is a graph that shows how many days of use we are forecasting on each machine type at each plant against how many days of machine capacity we have at each plant, all by machine type. The issue I'm having is that we have 7 machine types across all of our production plants, and each machine type can be considered a primary or a secondary machine for any plant-part pairing. I've created a primary and a weak relationship between the machine utilization table from the plant-primary machine type and the plant-secondary machine type to the plant-machine type in my machine quantity table, but I don't know how to sum up the machine days used by the secondary machines (on the weak relationship). Please advise, and thank you for reading this far! Below I've included screenshots of my current model and of what our desired output is (taken from an existing Excel model I'm trying to recreate in BI).

[v-yulgu-msft]

Hi @Anonymous ,

To help us better understand your requirement, please simplify the data model and relationship, just provide some dummy data which can reproduce the same sceanrio. Also, please show details for calculation and desired result.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[Anonymous · Accepted solution]

Hi,

I was able to solve the issue by creating a series of inactive relationships and using the "USERELATIONSHIP" DAX function to get what I needed. I didn't want to upload any files because the data set was pretty elaborate and there was a lot of sensitive information in my file. Thanks for the response.