How to deal with many to many dimensions in Tabular?

[ovonel]

I have this model:

Emp Roles is an unpivot that has Manager1 and Manager2 from Contracts and Manager1 and Manager2 from Clients:

• Each Contract/Client can appear multiple times in Fact.
• Each Fact row belongs to only one Contract and only one Client

As expected, a given client can have different contracts… (And, a given contract can belong to multiple clients too).

Therefore clients and contracts is Many to Many too.

My problem is that when in Power BI I combine a manager from Emp Roles with a fact measure I get some wrong results.

Is there any way for this to work?

[ibarrau · Accepted solution]

Hi. Unless you need to check fact numbers of EmpRoles exactly by client or by contracts, the snowflake it's not recommended. By data modeling star schema theory, you could just add a column to the fact with the unique identifier of the emp role that you can reach from contract from example before loading the model. That way you don't need relationships between emp role and client or emp role by contract. You can just have emp role with the fact directly.

I hope that helps, it's a lot to explain about how to handle different scenarios when doing data modeling and the answer usually is "it depends".