cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdriscol Regular Visitor
Regular Visitor

Production Capacity Model and Primary/Secondary key columns

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

 

 

Model for Plant Production Forecast.JPGChart Example.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
jdriscol Regular Visitor
Regular Visitor

Re: Production Capacity Model and Primary/Secondary key columns

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Production Capacity Model and Primary/Secondary key columns

Hi @jdriscol ,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jdriscol Regular Visitor
Regular Visitor

Re: Production Capacity Model and Primary/Secondary key columns

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.