topic Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation? in Developer

Critical Matrix Performance (230s+): Optimization tips and technical documentation?

almagp — Fri, 02 Jan 2026 07:39:10 GMT

Hi everyone,

I am currently optimizing a report and have encountered a critical performance issue with a couple of Matrix visuals. According to the Performance Analyzer, a single matrix is taking over 230,000 ms to load, often resulting in the "The query exceeded the resources" error.

I have a few specific questions:

"Invisible" Calculations: If certain DAX measures are not explicitly displayed in the matrix but are part of the model or related to the data shown, are they still calculated during the visual rendering? What could cause such high load times if the visual setup appears "simple"?
Semantic Model Architecture: Is it considered best practice to split semantic models? I currently use a single model with many complex DAX measures shared across multiple reports. Would it be more efficient to have a "lean" model (data/schema only) and a separate derivative model containing heavy measures only when needed?
Documentation: Is there any official or advanced technical documentation that specifically deep-dives into Matrix visual performance and the impact of row/column hierarchies?

I would appreciate any advice on best practices to resolve this bottleneck. Thanks in advance!

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

GeraldGEmerick — Fri, 02 Jan 2026 16:34:49 GMT

@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?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

lbendlin — Fri, 02 Jan 2026 18:35:29 GMT

Copy that DAX query and run it through DAX Studio. Study the query plan and the server timings. Then decide your plan of action accordingly.

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

AshokKunwar — Sat, 03 Jan 2026 05:11:14 GMT

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

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

cengizhanarslan — Sun, 04 Jan 2026 15:39:09 GMT

Matrix evaluates measures per cell. If you have (Rows × Columns × Measures) in the thousands+ and the measures do iterators / complex FILTERs, it explodes. Big offenders:

High cardinality on rows/columns (IDs, dates at day level, many levels expanded)
Measures using SUMX/FILTER over large tables, ALLSELECTED, RANKX in-cell
Many-to-many / bi-directional relationships causing large filter propagation
Using a fact-table column directly on rows instead of a dimension
Conditional formatting calling heavy logic again per cell

Quick sanity check: collapse to one row level + one column level. If time drops massively, it’s “cell explosion”.

What to do:

Reduce the number of cells: fewer row/column levels, avoid high-cardinality fields, default to collapsed state.
Remove conditional formatting/tooltips temporarily to see if they’re the cause.
Replace expensive iterators in measures with simpler patterns (push logic to ETL, pre-aggregate, use dimensions).
Ensure proper star schema: matrix axes from dimensions, single-direction relationships.

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

almagp — Mon, 05 Jan 2026 10:26:43 GMT

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?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

almagp — Mon, 05 Jan 2026 10:28:31 GMT

I had columns with DAX in my model, but none of them are visible in that Matrix, that the main question...

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

v-echaithra — Mon, 05 Jan 2026 12:04:24 GMT

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 measures

No, 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.

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

v-echaithra — Thu, 08 Jan 2026 10:29:24 GMT

Hi @almagp ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you
Chaithra E.

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

v-echaithra — Mon, 12 Jan 2026 12:29:23 GMT

Hi @almagp ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

AshokKunwar — Mon, 12 Jan 2026 12:48:21 GMT

Hii @almagp

If these optimization steps helped you resolve your performance bottleneck, please mark this as an "Accepted Solution"!

topic Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation? in Developer

Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

​1. The "Invisible" Calculation Myth

​2. Semantic Model Architecture: To Split or Not?

​3. Technical Optimization Steps

​Official Documentation & Deep Dives:

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

Re: Critical Matrix Performance (230s+): Optimization tips and technical documentation?

1. The "Invisible" Calculation Myth

2. Semantic Model Architecture: To Split or Not?

3. Technical Optimization Steps

Official Documentation & Deep Dives: