How to shape my data base

[FChambon]

Hi, 

I am a  engineer in charge of the Quality who begin with Power BI.

I signed in on this forum because I am struggling to shape my data base for Power BI.

Where I work, we have products that go on different production lines.

Each product goes on every production lines in a linear way (Line A then line B then line C, etc)

Each product as a specific weight on each production line. I need this specific weight to calculte the amount of good product in comparison of the total amount of product which went through the line 

On each production lines, each product can receive one or more than one defaults because of the process.  

Each default can be OK or not OK (NOK) depending on my decision.

Then, each product is NOK if there is at least one default which is NOK

Production line A => Product 1 (20kg) => Default a => OK

                                                               => Default c => NOK           Then Product 1 is NOK on line A

                             => Product 2 (18kg) => Default b => OK

                                                               => Default e => OK             Then Product 2 is OK on line A

Then the ratio good production/total production is 20/(20+18)=52.6%

The problem I have is

• To link a production line to a list of products (1 to *), and a product of this list to its list of default (1 to *), knowing that the same product go on all the different lines.
• Then to say that if a default is NOK then the associated product is NOK on one of the production lines.

Maybe someone can help me with this problem ?

Thanks in advance,

FChambon

[Anonymous · Accepted solution]

---

The problem I have is

• To link a production line to a list of products (1 to *), and a product of this list to its list of default (1 to *), knowing that the same product go on all the different lines.
• Then to say that if a default is NOK then the associated product is NOK on one of the production lines.

Maybe someone can help me with this problem ?

So, you're asking for a model, as much as I understand. It's rather simple 🙂 Create dimensions and then one fact table that will capture your process. You want a star chema (Understand star schema and the importance for Power BI - Power BI | Microsoft Docs). 

Here's how I'd do it. Create dimensions for Products, Lines and Defaults. Then create a fact table that will hold keys to these 3 dimensions and any statistics you have to capture about the product, line and default. From such a model you'll be able to SIMPLY calculate anything you want.

Your dimensions don't need to be linked to one another. The linkage will be provided by the fact table indirectly but that's all you really need. And of course the relationships will be 1-to-many from dimensions to the fact table.

[parry2k]

@FChambon maybe this is what you are looking for, add following 3 measure and of course, you can combine all these in one but I prefer separate measures:

Total Weight = SUM ( 'Product'[Weight] ) //simple sum of weight 

//get NOK weight 
Weight NOK = 
CALCULATE ( 
[Total Weight], 
CROSSFILTER ( Default[Product], 'Product'[Product], Both ), 
Default[Default] = "NOK" )

//calculate good production %
DIVIDE ( [Weight NOK], [Total Weight] )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

Now if you drop the Line column in a table visual and above % measure, you will see the value of 52.6%

I hope this will get you started and you can tweak it from here as per your need.