Forum Discussion
Question on Many to many relationship
Question related to many to many joins;
we have 2 tables , with multiple values eg; Table1 have Date , Item , Region, Sales, and we have multiple lines as we have multiple items and region,
Table 2 has date , Item, region and Cost, again multiple dates and multiple items,
We need to make one calculative measure that gives the total sales and total cost.
Again it should be compatable with slicers for Region and Items?
Any Ideas and Suggestions are welcome.
You shoukd create a Date Table with continuous dates covering the range of dates in the model and dimension tables with unique values for Item and Region. Then join these tables in one-to many relationships with the corresponding fields in both fact tables and use the fields from the Date and Dimension tables in measures, slicers filters and visuals
Hey Swapn22 ,
when ever we are talking about a data model in Power BI we are thinking about a data modeling concept that often is called dimensional modelling or star schema where different table types, dimension and fact tables, are forming this data model.
A dimensional model can contain multiple fact tables (sales and costs) and multiple dimension tables: Date, Item, and Region. One dimension filters both fact tables.
My recommendation is, create the dimension tables and use these dimension tables to create the relationships. Use columns from the dimension tables for the slicers, axis, and column and row headers..
Here you will find an introduction to data modeling: https://docs.microsoft.com/en-us/learn/paths/model-power-bi/
Hopefully, this provides ideas on how to tackle your challenge.
Regards,
Tom
3 Replies
PaulDBrownCommunity Champion
You shoukd create a Date Table with continuous dates covering the range of dates in the model and dimension tables with unique values for Item and Region. Then join these tables in one-to many relationships with the corresponding fields in both fact tables and use the fields from the Date and Dimension tables in measures, slicers filters and visuals
- TomMartens
Super User
Hey Swapn22 ,
when ever we are talking about a data model in Power BI we are thinking about a data modeling concept that often is called dimensional modelling or star schema where different table types, dimension and fact tables, are forming this data model.
A dimensional model can contain multiple fact tables (sales and costs) and multiple dimension tables: Date, Item, and Region. One dimension filters both fact tables.
My recommendation is, create the dimension tables and use these dimension tables to create the relationships. Use columns from the dimension tables for the slicers, axis, and column and row headers..
Here you will find an introduction to data modeling: https://docs.microsoft.com/en-us/learn/paths/model-power-bi/
Hopefully, this provides ideas on how to tackle your challenge.
Regards,
Tom
Hi Tom,
Thank You so much for the in-depth logic and it really helps !!
Thank you
