I’m currently building a complex sales dashboard and encountered a many-to-many relationship between my product categories and regional promotions. I’ve heard that using a bridge table is better for performance than the built-in many-to-many cardinality settings. What is the best practice here to ensure my DAX measures don't slow down as the dataset grows to millions of rows?
3 answers
From my experience, you should definitely opt for a bridge table containing unique keys rather than relying on the default many-to-many settings. The default behavior often leads to ambiguous results and significant performance overhead because it relies on a hidden internal table. By creating a physical bridge table and using a one-to-many relationship on both sides, your DAX filters will propagate more predictably. This approach also allows you to use the CROSSFILTER function more effectively when you need to control the direction of the filter flow for specific calculations.
Have you considered if you can flatten your data into a Star Schema instead of using a bridge table? Sometimes denormalizing the data at the SQL or Power Query level can eliminate the need for complex relationships altogether. Would your current data architecture allow for that kind of transformation before the data even hits the Power BI model?
I always recommend the bridge table approach for clarity. It makes the data model much easier to audit and troubleshoot when your row counts start hitting the millions.
I agree with David. Keeping the model clean with a visible bridge table is vital for long-term maintenance, especially when handing the project over to other analysts later on.
Robert, that is a great point! In my case, the source data is from a legacy ERP where I can't change the schema. If I use Power Query to flatten the tables, will that significantly increase the size of my PBIX file due to data redundancy, or does the VertiPaq engine compress it?