Matrix formatting

Hi ryan12345 

The main performance issue is probably not the SWITCH measure itself. It is the fact that all base measures use COALESCE(..., 0).

This converts a naturally sparse result into a dense result. At the lower hierarchy levels, the matrix must evaluate and potentially materialize every row hierarchy combination against every item in the disconnected Metrics table. The visual filter then evaluates another 12 measures for each row to remove combinations that were created only because the measures returned zero rather than BLANK.

Microsoft specifically recommends allowing measures to return BLANK when no meaningful result exists, because visuals normally eliminate all-blank groupings and therefore process far fewer rows.

I would first change the base measures back to sparse measures:

_baseval =
SUM ( 'TBL - GL ACTUALS'[baseval] )

The same should be done for the other Actual and Budget base measures.

Your dynamic format string can still hide genuine zero values:

VAR Metric =
SELECTEDVALUE ( Metrics[Name] )

RETURN
SWITCH (
Metric,
"CM ", "+#,##0;-#,##0;",
"YTD ", "+#,##0;-#,##0;",
"YTD YOY", "+#,##0;-#,##0;",
"#,##0;-#,##0;"
)

Dynamic format strings preserve the numeric data type, so there is no need to convert the result to text.

I would also remove UNICHAR(160) from the value measure. A measure that displays financial values should return only numeric values or BLANK:

Metric Value =
VAR Metric =
SELECTEDVALUE ( Metrics[Name] )
VAR IsSpacer =
SELECTEDVALUE ( Metrics[IsSpacer] )
VAR SpacerType =
SELECTEDVALUE ( Metrics[SpacerType] )
RETURN
SWITCH (
TRUE (),
IsSpacer || NOT ISBLANK ( SpacerType ), BLANK (),

Metric = "CM", [_baseval],
Metric = "YTD", [_baseval YTD],
Metric = "PY YTD", [_baseval YTD -1],
Metric = "CM -3", [_baseval -3],
Metric = "CM -2", [_baseval -2],
Metric = "CM -1", [_baseval -1],
Metric = "CM ", [_baseval BU],
Metric = "YTD ", [_baseval BU YTD],
Metric = "FY", [_baseval BU FY],
Metric = "CM ", [_baseval CM var],
Metric = "YTD ", [_baseval YTD var],
Metric = "YTD YOY", [_baseval YTD YOY var]
)
)

There is another important simplification: your background colors are determined by the metric name, not by the value in the cell. Therefore, conditional formatting is not really required.

Try using:

Format visual → Specific column → select the relevant metric series → Background color → Apply to values

Set the background for each YTD and placeholder column directly. Current matrix formatting supports applying a fixed background to individual series.

This should allow the value measure to remain BLANK while the column formatting remains consistent.

Once the measures return BLANK, the matrix should normally remove hierarchy rows where every measure is blank, so Metric Filter might no longer be necessary.

If a filter is still required, do not evaluate all 12 displayed measures. Create one inexpensive existence measure based directly on the relevant Actual and Budget fact tables and the complete reporting date window:

Has Relevant Data =
VAR ActualRowCount =
CALCULATE (
COUNTROWS ( 'TBL - GL ACTUALS' ),
REMOVEFILTERS ( Metrics )
-- Apply the complete required Actual date window here
)
VAR BudgetRowCount =
CALCULATE (
COUNTROWS ( 'TBL - GL BUDGET' ),
REMOVEFILTERS ( Metrics )
-- Apply the complete required Budget date window here
)
RETURN
INT ( ActualRowCount > 0 || BudgetRowCount > 0 )

Use this measure as the visual filter with a value of 1.

The date filters in this measure must cover all periods shown by the matrix, including current month, previous months, YTD and prior-year YTD.

An aggregation table can be considered afterward if the fact tables are still too large, but the first priority should be restoring sparse measures and removing the 12-measure visual filter.

Also, spacer columns created through dummy values are inherently expensive because they can prevent blank hierarchy rows from being eliminated. Where possible, replace them with column widths, borders, padding, column groups or separate aligned matrices.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Hi ryan12345 

Memory limit is coming from Metric Filter measure evaluated as a row-level visual filter across every combination of row hierarchy and 12 Metrics rows - each drill level forces DAX to recompute all 12 underlying measures where many of which scans fact table for every row to decide whether to hide it. Rather than filtering with a measure, move "hide empty rows" logic to row hierarchy fields themselves by using calculated column or apply 'filter to keep rows that exist in fact table' through measure that only checks single aggregated total instead of 12 separate measure calls. For example, check with one measure like _HasData = COALESCE(SUM(baseval),0) + COALESCE(SUM(baseval BU),0) <> 0
If combination covers all cases since few measure evaluations per cell means less memory pressure. You could also try disabling 'Show items with no data' and instead blank to zero display through format strings as you are doing without separate row elimination measure