Question on Many to many relationship

[Swapn22]

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. 

[PaulDBrown · Accepted solution]

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 · Accepted solution]

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

[Swapn22]

Hi Tom,

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

Thank you

[PaulDBrown · Accepted solution]

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