Forum Discussion
Critical Matrix Performance (230s+): Optimization tips and technical documentation?
Hi almagp ,
Yes, adding shipment triggers complex internal DAX, even if no measures or calculated columns are visible. A Matrix evaluates data per cell (Row × Column × Hierarchy × Totals). Shipment is typically high-cardinality, so adding it multiplies the number of evaluation contexts, forces Power BI to resolve relationships, grouping, and totals at shipment grain. This results in very large internal DAX queries and storage engine scans, which explains the 230s runtime.
Avoid placing fact-table IDs (Shipment) on Matrix rows.
Use pre-aggregated dimensions, separate detail/drillthrough pages or summarized tables for detail views.Measures not used in the Matrix are NOT calculated. Matrix slowness is almost always due to model design + cardinality, not whether measures are visible. Calculated columns are evaluated if they belong to tables used in rows/columns or relationships. Matrix performance cost grows as Rows × Columns × Hierarchies, even without visible measures.
High load times in a “simple” Matrix are usually caused by:High-cardinality fields (IDs like Shipment) on rows/columns > cell explosion
Calculated columns on fact tables
Totals / subtotals
Relationship complexity
Conditional formatting or tooltips invoking additional measuresNo, splitting models just to isolate measures is NOT best practice.
Recommended approach is to keep one shared semantic model, move heavy row-level logic to Power Query / ETL, avoid calculated columns on fact tables, use display folders / perspectives to manage complexity
Splitting into “lean + heavy” models increases duplication and maintenance without guaranteed performance gains.Documentations:
Optimization guide for Power BI - Power BI | Microsoft Learn
Automatic tuning - SQL Server | Microsoft Learn
Hope this helps.
Thank you.
Hii almagp
Subject: [SOLVED] Critical Matrix Performance: Optimizing Queries Exceeding 230s
The Challenge:
Matrix visuals are "expensive" because they generate a separate query context for every single cell (intersection of row and column). When you have complex DAX measures combined with deep hierarchies, the number of sub-queries explodes, leading to the "Query exceeded resources" error or massive load times like the 230,000ms seen in your Performance Analyzer.
The Solution:
To resolve this, you must address the "Invisible" calculations and optimize the way the Semantic Model interacts with the visual.
1. The "Invisible" Calculation Myth
- Are they calculated? No, measures not in the visual are not calculated.
- What causes the load then? Even a "simple" visual can be slow if your DAX uses Iterators (like SUMX or FILTER) over large tables, or if Auto Date/Time is enabled, creating hidden date tables that bloat the filter context.
- Check Conditional Formatting: "Invisible" load often comes from conditional formatting rules. If you have "Color by Value" set up, Power BI calculates those measures for every cell even if the text isn't displayed.
2. Semantic Model Architecture: To Split or Not?
- Best Practice: It is generally not recommended to split models just to hide measures. Instead, use Perspective or Display Folders to keep the model "lean" for users.
- The "Lean" Model approach: Rather than splitting, move heavy logic from DAX measures into Calculated Columns or Power Query (M) during the ETL phase if the logic doesn't need to be dynamic. This shifts the processing cost from "Report View Time" to "Data Refresh Time."
3. Technical Optimization Steps
To bring that 230s load time down:
- Reduce Column Headers: Matrices scale poorly with many columns. Try to move attributes from "Columns" to "Rows" to reduce the total number of cell intersections.
- DAX Fusion: Use DAX Studio to check for "VertiPaq Cache" hits. If your measures are repetitive, rewrite them to use VAR (Variables) to ensure the same logic isn't calculated multiple times within a single measure.
- Turn off Totals: If you don't need them, turn off Row and Column totals. Totals require a separate, broader calculation pass that often takes longer than the individual cells.
Official Documentation & Deep Dives:
- Microsoft Docs: Search for "Optimization guide for Power BI" which has a dedicated section on Matrix performance.
- The "SQLBI" Methodology: For advanced deep-dives, look up "The Whitepaper on Power BI Performance" by Marco Russo and Alberto Ferrari.
If these optimization steps helped you resolve your performance bottleneck, please mark this as an "Accepted Solution"!
Best regards,
Vishwanath