cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Swapn22
Frequent Visitor

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. 

2 ACCEPTED SOLUTIONS
PaulDBrown
Super User
Super User

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

TomMartens
Super User
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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hi Tom,

Thank You so much for the in-depth logic and it really helps !!

Thank you

PaulDBrown
Super User
Super User

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.