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.
almagp The issue is almost certainly caused by a poor performing DAX query meaning that it is likely tied to some measure that you have in your matrix visual. You can see this in your Performance Analyzer logs, the DAX query is taking up all of the time. Can you post your DAX measure(s) that you are using in your matrix visual?
Hi Gerald,
That is actually what surprises me the most: I don't have any columns or sub-rows in my matrix that use DAX. As you can see in the attached image showing the fields, none of them are DAX measures or calculated columns; they are all raw fields.
However, I am using 'Shipment' (Envío) as a sub-row to show the data at a detail level. I wonder if adding this 'Shipment' level triggers complex internal DAX queries even if they aren't explicitly shown.
On the other hand, if I remove the 'Shipment' sub-row, I don't get a correct 'count' of my data. For instance, if I remove it from the rows and add it directly to the 'Values' section as a Distinct Count (as seen in the 'Missing Shipments' field), the count is different from what I see in the hierarchy. Do you know why this might be happening as well?
