cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PC2790
Solution Sage
Solution Sage

Best practice for Data Model

Hello,

 

I am a little stuck with deciding on the data model.

Till now what I have done is created a master table, say MTable containg values as below:

 

MeasureCategory
M1C01
M2C01
M3C01
M4C01
M5C01
M6C01
M1C02
M2C02
M3C02
M4C02
M5C02
M6C02
M1C03
M2C03
M3C03
M4C03
M5C03
M6C03

 

This is to address a purpose for creating a matrix like:

 

 C01C02C03
M1Values  
M2Values  
M3Values  
M4Values  
M5Values  
M6Values  

 

I have connected the MTable with other 6 Tables from which these Measures(M1-M6) are coming.

Now I want this matrix to get filtered with another column say Sub Category:

Sub Category
Tableau
Power BI
Python
AWS
Qlik

 

The sub-category is present in all the 6 tables and so whenever I choose filter from table1, the other Measures( M2-M6), stop updating.

Please let me knwo what will be the best way to get it fixed?

1 ACCEPTED SOLUTION

As there is one to many relationship between the tables , follow the below steps and try and update the model:

1 Merge all the Tables 1:6 in Mtable (merge using category)and only bring the associated sub category (only consider sub category column) while merging.
2. the above step will help you bring Category and Sub Category in Mtable
3. create a key for Category and sub category
4. As we have merged all the six tables there might be a many to many relation with those tables.
5. To overcome the many to many relationship create a bridge table with distinct values of Key (Category and Sub Category)

 

Please update the ticket if you need any further clarification.

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@PC2790 , you need to share the full data model.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Becasue of the criticality of the data, I am not able to share the exact model.

Though I have tried to replicate the same as below:

PC2790_0-1620149099241.png

Now, I want to filter the matrix using the Sub Category

This is where I am not getting the desired result.

 

As there is one to many relationship between the tables , follow the below steps and try and update the model:

1 Merge all the Tables 1:6 in Mtable (merge using category)and only bring the associated sub category (only consider sub category column) while merging.
2. the above step will help you bring Category and Sub Category in Mtable
3. create a key for Category and sub category
4. As we have merged all the six tables there might be a many to many relation with those tables.
5. To overcome the many to many relationship create a bridge table with distinct values of Key (Category and Sub Category)

 

Please update the ticket if you need any further clarification.

View solution in original post

Yes! That's the right way to do it.

Thanks a lot.

It worked

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors
Top Kudoed Authors