Perfromance Optimization | Many to Many Relationships Analysis

[BI-user25]

Hi Team,

I have a Power BI dataset with several many-to-many relationships. Some relationships use single-direction filtering, while others use bidirectional filtering. I would like to analyze and optimize the performance of this dataset by reducing the number of many-to-many relationships.

However, the columns involved in these relationships contain duplicate values, which is why Power BI creates many-to-many relationships. Please suggest how I can optimize the dataset in this situation

[rodrigosan]

Hello @BI-user25  , I don’t see your model, but maybe creating bridge tables is an option?

https://community.fabric.microsoft.com/t5/Desktop/How-to-create-bridge-table-with-composite-keys/td-...

[v-menakakota]

Hi @BI-user25 ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @Zanqueta  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @BI-user25 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team  

[lbendlin]

Consider using Qlik instead - its associative model is better suited for many-to-many relationships and network graphs.

[Zanqueta · Accepted solution]

Hello @BI-user25,

To optimise performance in Power BI models with many-to-many relationships, two complementary strategies are recommended:

1. Adjust Filtering Logic in DAX

Avoid bidirectional relationships wherever possible. Use single-direction filtering and, when cross-filtering is required, apply functions such as TREATAS or CROSSFILTER within measures. This approach reduces unnecessary filter propagation and improves calculation efficiency without compromising business logic.

2. Redesign the Model in Power Query

Many-to-many relationships often exist because the columns involved contain duplicate values. A practical solution is to create composite keys that guarantee unique identifiers for each row, enabling you to convert many-to-many relationships into one-to-many. This can be achieved by combining relevant columns (for example, CustomerID and ProductID) before loading the data into the model. This approach supports a star schema design, which is considered best practice for scalability and performance.

Official Guidance:

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

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.