Forum Discussion
Fix a Many to Many relationship? How to build correct data model...
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
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 _valueOutput:
Please check the pbix file.
Best Regards,
Gao
Community Support TeamIf 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
6 Replies
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💡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 😄
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 👍
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 👍
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 _valueOutput:
Please check the pbix file.
Best Regards,
Gao
Community Support TeamIf 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
