Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
blaster
Frequent Visitor

many to many between dim and facts

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.

blaster_0-1675279359896.png

blaster_0-1675325573841.png

 

 

 

1 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.

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

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. 

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?

bridge table many to one.pngbridge table many to many.png

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.