How to start with this project? 3 tables multiple values

[Pillic]

Hello Community,

i am new to power bi and stuck with this project.

So I have 3 tables:

Region (70 rows) - Plant,Region,Divison etc. - Plant is unique 

RawMaterials (6500 Rows) - Plant, Material ID, Units, Price - Plant + Material = unique price for a raw material

Recipes (over 1.000.000 rows) - Date, Product, Plant, Material, Amount of Material

Each product consist of 2-8 different raw materials

The goal is to visualize the price of a product (calculated by the sum of all Rawmaterial prices) over a time period by plant. 

And now I am stuck.

I want to avoid to add a calculated column to the Recipe table, as its performance is already very bad and I think adding calculated column would increase this behavior.

Relationships between the tables: as there are multiple Plants in table RawMaterial and Recipes its not finding the expected filter.

It would be great if some one could have a look at this scenario and point me to the right direction.

Thanks in Advance

Best Regards

Pillic

[Greg_Deckler · Accepted solution]

Difficult to say exactly but it seems like you need to create a composite key between RawMaterials and Recipes. So in PowerQuery, add a column to both like:

[MaterialID][Plant]

To create a unique key for each material by plant.

[mahoneypat]

If I understand your scenario, I would do the following:

1. Add a column that merges Plant and Material in your commodity and recipe tables

2. Make a relationship between your two new columns (1:Many)

3. Use a measure like this - TotalCost - sumx(RawMaterials, RawMaterials[Preis]*Calculate(Sum(Recipes[Wert])))

I also assume that you have a date table that also filters the Recipes table.

If this works for you, please mark it as the solution. Congratulations are also appreciated. Please let me know if you don't.

Best regards

Pat

[Greg_Deckler]

Hard to say exactly, but it looks like you need to create a composite key between RawMaterials and Recipes. Therefore, in PowerQuery, add a column to both as:

[MaterialID] [Plant]

Create a unique key for each material per plant.

[Greg_Deckler]

Hard to say exactly, but it looks like you need to create a composite key between RawMaterials and Recipes. Therefore, in PowerQuery, add a column to both as:

[MaterialID] [Plant]

Create a unique key for each material per plant.

[mahoneypat · Accepted solution]

If I understand your scenario, I would do the following:

1. Add a column that merges Plant and Material in both your Raw Material and Recipes tables

2. Make a relationship between your two new columns (1:Many)

3. Use a measure like this - TotalCost = sumx(RawMaterials, RawMaterials[Preis]*Calculate(Sum(Recipes[Wert])))

I also assume you have a Date table that is filtering your Recipes table too.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Pillic]

Thanks Greg and mahoneypat, that helped me out and solved this topic. Now I am a major step further.

I created the merged tables - added the relationship - and also the measure. Great!

Thanks a lot

Pillic

[Greg_Deckler · Accepted solution]

Difficult to say exactly but it seems like you need to create a composite key between RawMaterials and Recipes. So in PowerQuery, add a column to both like:

[MaterialID][Plant]

To create a unique key for each material by plant.