Having trouble with data model relationships

[JonS79]

Hello,

I am trying to build a data dashboard for a work analysis project. I've had no trouble using power query to load the data, and building a power pivot data model. At first I struggled because the two main data sources (labeled SIOP and EDI in the picture) have many repeats of the same part number (common identifier). I solved this issue by creating a master key (with no repeats) - and this allowed me to create pivot tables of the data. I'm coming across one issue I can't solve: the tables all have a relationship by part #, but when I try to create a slicer for "Manufacturer" from the SIOP table to sort the data in the EDI table - the pivot table refuses to respond. It won't sort. I would have thought that because all the tables are linked by part # - that I can use the manufacturer field. Any ideas? Help is much appreciated, thanks!

[#2]

@JonS79 Could you share some data to better demonstrate your model? One of the reasons why you can't slice EDI data on Manufacturer from the SIOP table is because that data element can't filter 'up stream'. 

And then I wonder why you have 'Manufacturer' in the the SIOP table. Does that mean that a component may have multiple manufacturers? If that's not the case, move that field to the 'Key' table. Than slicing will not be a problem.

If,  on the other hand, a component may have multiple manufacturers how would you want to slice the data from the EDI table? Good if you could give some examples of the expected results.

[#3]

@Riny_van_Eekelen thanks for your answer. 

the SIOP data contains many fields - it’s a future forecast of demand by part number by plant with a total for each month. The same part number may appear 15 times. There is only one part number per manufacturer.

The EDI is a plant demand by part number file - it has monthly demand by part number by plant, but no manufacturer data. just like the SIOP file, the same part number can appear 15 times. 

both of these files are generated by external teams - and my team needs to do monthly analysis and comparison of demand and forecast by part number, tech density, and supplier. 

the reason I don’t have manufacturer in the SIOP file is I don’t generate it. I generated the master key as I’m new to learning data models and excel and figured out quickly you need a clean master key with no reoccurrences to align data sources. 

would you mind explaining what you mean by can’t filter upstream? I had thought if i had a clean correlation by part number between tables any field could be correlated and that is my misunderstanding of how the data model works I guess! Thanks so much. 

answering on my phone so I’ll try to add data examples in a bit. 

[#4]

@JonS79 OK, I'll wait for your examples.

You wrote: "There is only one part number per manufacturer."

My question was "are there multiple manufacturers for a part number?" One manufacturer may of course supply multiple part numbers. That's no problem.

[#5]

@Riny_van_Eekelen Thank you for taking your time to answer my questions. OK, I'm on my computer now, let me try and clarify:

To answer the first question: there is only one manufacturer per part number, thankfully.

The first table, SIOP (24 month rolling customer demand forecast) looks like this:

It's basically a finance generated report of part numbers, descriptions, and monthly demand volumes, on a rolling 24 month forecast. This report has all of the detailed information - demand by plant, by project, by customer program. The part number (main identifier) can appear many times.

The second table, EDI (24 month plant order demand) looks like this:

This report is much simpler - it only includes part numbers and plants - it does NOT delineate by project, does not include manufacturer name, etc. I will note - plant codes are the SAME between the two files.

The end goal of this is to build a dashboard that will let me easily compare the two data sources, and report volume by part number, sliced by plant, manufacturer, and tech density.

It's very easy to add manufacturer to the memory master key file - and all the things I'm trying to do I can do very easily if I'm looking at ONE data source - either SIOP or EDI. The minute I try to slice or filter EDI by manufacturer (doesn't exist in SIOP), or even try and slice by SIOP by EDI plant code - it fails.

This doesn't make any sense to me - because it defeats the point of multiple tables so far, to me. If I need to create a master that contains everything, I guess I'm missing the point of the data model, or perhaps I'm just doing it wrong 🙂 Thanks for your help.

[#6]

@JonS79 If you only have one manufacturer for any product you don't need to worry about adding that info the the 'key' (dimension) table. You can use the TREATAS function to create what you could call a 'virtual' relationship between the other two (fact) tables. A bit difficult to explain without your own data and tables to work with. Can you share a file (Onedrive, Dropbox or similar) with some anonymized data. Then I or someone else can have a look at it.