Data Model Design Question - multiple many to many relationships

Hi abhiram342 ,

Based on the information you have provided, this sounds like an attempt to optimise the way data is modelled in relation to pipelines, campaigns and work orders in order to efficiently calculate failure rates and campaigns by issue type.

1. Creating bridging tables is indeed a viable solution when dealing with many-to-many relationships in the data model. You can follow the steps below:
Create a new table that contains the unique identifiers from the Pipelines/Activities table and the Tickets table. This table should also include the IssueType column.
Establish a relationship between this bridged table and the other tables in the model. The bridge table will act as a central reference point for joining related records based on IssueType.
You can refer to the following link for more details:

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

2. Spreading tables into a single table can simplify the data model and may make calculations easier. However, this method may result in a significant increase in dataset size. This may affect the performance of Power BI reports and dashboards. Consider the following points before deciding on this action:
The impact on performance and whether the Power BI environment can effectively handle the increased dataset size.
The complexity of maintaining and updating a single large table.

In many cases, maintaining a normalised data model with correctly defined relationships can provide a good balance between performance and flexibility.

Both of the approaches you mentioned have their advantages and potential disadvantages. The choice between creating bridged tables or stretching tables into a single table depends on the specific requirements of the project, including performance considerations and complexity of maintenance.

If you decide to go with the bridged table approach, can you share more detailed information about the specific issues you encountered when trying to build the relationship? This information will help provide more tailored recommendations.

Best Regards,

Ada Wang

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