Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Super User!
Paul on Linkedin.
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
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
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
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |