Data Modeling - Multiple Fact Columns in one dimension Table / one Hierarchy

[jdusek92]

Hello!

I need to create a report where I can display multiple granularity details in a single fields/column of a Matrix.

This basically means that some rows from the source table will be counted multiple times

Here is the example of the data:

 So Far I have come up with these sutions:

1. Create a Composite Key and a Dim Table with all the possible Composite Keys. This works well, but it uses many to many relationship and does not follow the Star Schema. Can you see any possible future problems with this solution?
2. Create a disconnected Dim Table and create a measure with a condition on Dim Table SELECTEDVALUE. The problem is I will have a lot of measures and I dont want to have basically tripled the amount of DAX count in each measure due to SWITCH cases 
3. Create a connected Dim Table, connected on Grade. Then for SELECTEDVALUE(Dimension) = Managers/Senior use a custom calculation. But again this would triple the amount of DAX code.

I do not want to expand the rows of the source table to multiple rows, I want 1 row = 1 employee

I would prefer the first solution using Many to many relationship, because it seems the most efficient when it comes to writing the measures. But I am not sure of any future complications/implications this solution might bring.

I would like to know your opinion or maybe you have a different solution!

Thank you 

Jakub

[lbendlin · Accepted solution]

Your dimension table 2 needs to stay disconnected for this to work. Your composite key (table 1) is not required, and table 3 is not required either.

Repeating data in the same visual is a design red flag.

see attached.