many to many between dim and facts

[blaster]

Hi,

can you please advise to modify the model or it is fine to have many to many relationship between dim and fact tables. There are two dims Calendar and Client and I need to have them as 2 single tables that users can build their visuals. Is that so bad to have many to many relationships in theese two cases? Please let me know should I create some bridge tables or I don't have to. Thanks in advance.

[v-tangjie-msft]

Hi @blaster ,

Depending on your problem and the description of your requirements, you want to convert many-to-many relationships in your data model into one-to-many relationships, right? If this is the case, in fact, the many-to-many relationship in the data model is not a recommended data modeling relationship, and some incorrect filtering may be caused when the data is filtered interactively, my suggestion is that you can build a new dimension table separately in Power query for the columns "Period" and "Client" in your two many-to-many relationship tables, and then use a one-to-many relationship to connect to the two tables of the many-to-many relationship respectively to ensure that the filtering direction is correct. This will avoid the problems you encounter. 

For a more detailed analysis of many-to-many relationships in Power BI and a sharing of solution ideas, you can also refer to these links:

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

Many-to-many relationship guidance - Power BI | Microsoft Learn

Different options to model many-to-many relationships in Power BI and Tabular - SQLBI

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[blaster]

Hi Neeko Tang, thank you for your reply. I am confused a little bit. PLease look at the pictures. If I use a bridge table created in PQ just by keeping one column and removig duplicates and put it betweem two tables - I have a problem to combine fileds from those tables in a visual. I had to change the relationship betwee the calendar table and the bridge table to many-to-many. Is that the best practice?

[v-tangjie-msft · Accepted solution]

Hi @blaster ,

Based on your data model example screenshot, after creating the intermediate table, does Power BI also need to create a many-to-many relationship between your dimension table and the Calender table? If convenient, you can go to power query again to check if your dimension table UniqPeriodsTable contains any duplicate or null values? If so, I don't think this many-to-many relationship will cause any problems with the flow of data between tables and data filtering. Thanks for your time!

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.