Best practice for Data Model

[PC2790]

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:

Measure	Category
M1	C01
M2	C01
M3	C01
M4	C01
M5	C01
M6	C01
M1	C02
M2	C02
M3	C02
M4	C02
M5	C02
M6	C02
M1	C03
M2	C03
M3	C03
M4	C03
M5	C03
M6	C03

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

	C01	C02	C03
M1	Values
M2	Values
M3	Values
M4	Values
M5	Values
M6	Values

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?

[amitchandak]

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

[PC2790]

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:

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

This is where I am not getting the desired result.

[ZikoPowerBI · 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.

[PC2790]

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

Thanks a lot.

It worked