Matrix formatting

[ryan12345]

Need some help on formatting a matrix, see steps below with question at the bottom.

I have a PBI report with the matrix below.

To get it in this format I have created the metrics table below.

I then created a measure to get the measures into the columns.

Metric Value = 
VAR Metric =
    SELECTEDVALUE ( Metrics[Name] )

VAR IsSpacer =
    SELECTEDVALUE ( Metrics[IsSpacer] )

VAR SpacerType =
    SELECTEDVALUE ( Metrics[SpacerType] )

RETURN

SWITCH (
    TRUE(),
    -- Special placeholder rows (17, 19)
    SpacerType = "placeholder 2",
        [_placeholder v3],

    -- Special placeholder rows (8, 14, 22)
    SpacerType = "placeholder",
        [_placeholder v2],

    -- Normal blank spacer rows
    IsSpacer,
        UNICHAR(160),

    -- Normal metrics
    SWITCH (
        Metric,
        "CM", [_baseval],
        "YTD", [_baseval YTD],
        "PY YTD", [_baseval YTD -1],

        "CM -3", [_baseval -3],
        "CM -2", [_baseval -2],
        "CM -1", [_baseval -1],

        "CM ", [_baseval BU],
        "YTD ", [_baseval BU YTD],
        "FY", [_baseval BU FY],

        "CM  ", [_baseval CM var],
        "YTD  ", [_baseval YTD var],
        "YTD YOY", [_baseval YTD YOY var]
    )
)

& this measure to get the background highlighted.

Metric Background Colour = 
VAR Metric =
    SELECTEDVALUE ( Metrics[Name] )

VAR SpacerType =
    SELECTEDVALUE ( Metrics[SpacerType] )

RETURN
SWITCH (
    TRUE(),

    -- Placeholder rows (original)
    SpacerType = "placeholder",
        "#DADADA",

    -- Placeholder rows 2 (new rows 17, 19)
    SpacerType = "placeholder 2",
        "#595959",

    -- Normal metric formatting
    Metric = "YTD",
        "#F3F8FC",

    Metric = "YTD ",
        "#F3F8FC",

    Metric = "YTD  ",
        "#F3F8FC",

    BLANK()
)

With dynamic formatting to turn all 0's into blanks

VAR Metric =
    SELECTEDVALUE ( Metrics[Name] )

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

The reason I need 0's turned to blanks is because all my measures are based off the baseval or baseval BU measures both of which use COALESCE to return a 0.

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

I do this because, for the background colour formatting to work, I need to convert all blank values to 0. The conditional formatting is set up to highlight zeros in blue, but it does not apply to blank cells. Without this workaround, I am left with gaps in the formatting when drilling down through row hierarchies where some rows have no data in one or more YTD columns.

For example, a row may contain a value in YTD BU but no value in YTD Actuals. If the YTD Actuals cell is blank, the background colour is not applied, resulting in an inconsistent appearance. By converting blanks to 0 and then using dynamic formatting to display those zeros as blank, I can maintain consistent highlighting across the row.

I then apply a visual-level filter to exclude rows where all values are effectively zero, ensuring that rows with no meaningful data are not displayed.

Metric Filter = 
IF(
    [_baseval] = 0 &&
    [_baseval YTD] = 0 &&
    [_baseval YTD -1] = 0 &&
    [_baseval -3] = 0 &&
    [_baseval -2] = 0 &&
    [_baseval -1] = 0 &&
    [_baseval BU] = 0 &&
    [_baseval BU YTD] = 0 &&
    [_baseval BU FY] = 0 &&
    [_baseval CM var] = 0 &&
    [_baseval YTD var] = 0 &&
    [_baseval YTD YOY var] = 0,
    0,
    1
)

However I run into performance issues the further down the row hierarchy I drill into and ends up running into the following error:

Error: Resources Exceeded — rsQueryMemoryLimitExceeded. The query/calculations use more memory than the configured limit (consumed 1024 MB, limit 1024 MB).

So my question is: how can I get the format I need without these performance issues? I believe the metric filter is the issue but can't seem to find a way to remove rows without this on. Or is there a more efficient way to approach the whole task

[v-aatheeque]

Hi @ryan12345

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @ryan12345 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[krishnakanth240]

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

[Ritaf1983]

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.