Forum Discussion
Helper IIIperformance issue in model for many to many
Dear experts
I really need your help to understand whether there’s a problem with the given model and how to fix it.
It was built given many-to-many relationships between KPI and Template, and also between Template and Project.
The model works fine for all the visuals we wanted, but we’re having performance issues with the two visuals at the bottom below — a table and a Treemap summarizing that table.
I highlighted in yellow the columns I’m pulling into the table.
Can you please take a look and tell me if there’s an obvious problem in the model itself?
Why am I asking about the model? Because when I tried pulling those 5 columns from dim and bridge tables into the table visual, Power BI gave an error saying it couldn’t identify the relationship between the data.
As soon as I add a measure based on the FACT table, it does calculate, but I suspect it’s doing so over a huge number of irrelevant combinations, and thus takes very long time to run.
P.S. for the treemap visual, I have a measure that simulates the columns in the table using SUMMARIZECOLUMNS.
Here is the code.
Thanks a lot in advance 🌼
Hi karimm , Thank you for reaching out to the Microsoft Community Forum.
The original problem came from chaining multiple many-to-many bridge tables between dimensions and the fact table. Power BI struggled to resolve a clear filter path across those bridges, so measures like the one using SUMMARIZECOLUMNS had to evaluate huge, ambiguous combinations of rows, causing the model to slow down and visuals to return blanks.
The fix was to simplify the model by merging the two bridges into a single one that directly links Projects, Templates and KPIs, then connecting that cleanly to the fact table with single direction relationships. Once we did that and replaced the overcomplicated DAX with a straightforward grouped count, the visuals, including the Treemap, calculated instantly and correctly. In short, the performance issue wasn’t DAX itself but an unclear model structure; flattening the relationships restored both accuracy and speed.
Please see the attached .pbix file for your reference.
5 Replies
v-hashadapuCommunity Support
Hi karimm , Thank you for reaching out to the Microsoft Community Forum.
Your model’s problem is the chained many to many bridges. Power BI can’t clearly trace relationships when Project -> Template -> KPI are linked through two bridge tables, so it gets confused when you pull fields from all those tables. When you add a measure from the fact table, it finally finds a path but ends up calculating across a huge number of irrelevant Project–Template–KPI combinations, which slows everything down.
The fix is to simplify the model. Flatten the two bridges into one table or push the Template, Project and KPI IDs directly into the fact table. That way, Power BI has one clean path per dimension. Keep relationships single direction (from dimensions to fact).
For the treemap, don’t use SUMMARIZECOLUMNS to simulate the table, just calculate directly from the fact table using a simple COUNTROWS or conditional measure. Once you simplify the relationships and the DAX, performance will be fast and stable.
Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Relationship troubleshooting guidance - Power BI | Microsoft Learn
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
karimmThank you for your reply.
I eventually improved the performance of the measure by removing the ADDCOLUMNS that was wrapping the SUMMARIZECOLUMNS as this function can add new measure columns directly after grouping by the first columns.I think I have to use SUMMARIZECOLUMNS to simulate the table as the table is doing a group by. I don't see how COUNTROWS can do that... Please correct me if I'm wrong.
I will consider restructuring the model by merging the 2 bridge tables into a single one. Didn't have the chance to test this yet.
- v-hashadapu
Hi karimm , Thank you for reaching out to the Microsoft Community Forum.
The original problem came from chaining multiple many-to-many bridge tables between dimensions and the fact table. Power BI struggled to resolve a clear filter path across those bridges, so measures like the one using SUMMARIZECOLUMNS had to evaluate huge, ambiguous combinations of rows, causing the model to slow down and visuals to return blanks.
The fix was to simplify the model by merging the two bridges into a single one that directly links Projects, Templates and KPIs, then connecting that cleanly to the fact table with single direction relationships. Once we did that and replaced the overcomplicated DAX with a straightforward grouped count, the visuals, including the Treemap, calculated instantly and correctly. In short, the performance issue wasn’t DAX itself but an unclear model structure; flattening the relationships restored both accuracy and speed.
Please see the attached .pbix file for your reference.
- v-hashadapu
Hi karimm , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
- v-hashadapu
Hi karimm , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.