Fix a Many to Many relationship? How to build correct data model...

[Jtbonner1986]

Hi Everyone, 

I have an issue i want to resolve and im not sure of the best way;

I have two tables with mulitple rows for 'Material ID'; one is a 'Demand' table and one is a 'Product' Table. I want to be able to filter on one 'Material ID' and for it to return only the values of that material in the demand table. 

The tables are structured like the below

Demand Table 

Material No.	Date Required	Quantity
123	01/02/2023	50
456	01/02/2023	25
789	01/02/2023	10
123	01/03/2023	10
123	01/04/2023	5
123	01/02/2023	10

Product Table

Product ID	Material ID	QTY Needed
AAA	123	5
AAA	789	1
BBB	999	1
BBB	123	1
CCC	555	1
CCC	123	10

I want to create a Matrix visual that displays the Demand table like the below (using a pre created Calendar table)

and i want to be able to filter by Product ID - and for it toonly return the Material ID relating to the product. 

Material ID	01/02/2023	01/03/2023	01/04/2023
123	60	10	5
456	25
789	10

But i am unbable to join both tables on Material ID as there are multiple rows for this in the Demand and the Product table as a single material can below to multiple products

any help is really appreciated 

Many thanks,

Josh

[v-cgao-msft · Accepted solution]

Hi @Jtbonner1986 ,

Please create a measure like:

Measure = 
VAR _material = SELECTEDVALUE('Product'[Material ID])
VAR _value = CALCULATE(SUM('Demand'[Quantity]),'Demand'[Material No.]=_material)
RETURN
_value

Output:

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Idrissshatila]

Hello @Jtbonner1986 ,

This video will show you how to create this dimension table https://youtu.be/dN3QYYbdk9c

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡

[Idrissshatila]

Hello @Jtbonner1986 ,

You should build it as a star shcema Model, check this microsoft documentation to learn how https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[Jtbonner1986]

What is the fact table here? Sorry for being stupid.... But as i could do with as much info as possible rather than... Just build the right schema 😄

[Idrissshatila]

Hello @Jtbonner1986 ,

so they are both fact tables, now create a table that had the material ID without duplicates to be the dimension and link it to both tables through the material ID. then use it to filter by material ID.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡

[Jtbonner1986]

This is back to the original queation. I need to filter the report by Product. Each Product has multiple Material ID's and the same material ID feature across multiple products, so how can i create a single Dimension table with material ID linked to multiple products?