Switch/IF error when expanding a matrix

[FranklinPhillip]

Hi, I believe I have discovered an issue with the PowerBI desktop matrix visual.
When using a basic switch statement in a matrix the data shows no results when expanding individual lines. The measure is a based on the selected value of a slicer:

Budget Shortfall =

VAR Shortfall =
[Measure A] - [Measure B]

VAR MetricSwitch =
SWITCH(
TRUE(),
SELECTEDVALUE(MetricsTable[Metric]) = "Net",
Shortfall
)

RETURN
MetricSwitch

This measure works fine at the lowest level of my hierarchy in a table visual, but when putting this measure in a matrix it only works if I click the arrows to expand to the lowest level of data. Where I need it to work is expanding through the plus icons.

When returning just the variable "Shortfall", with no condtional, it works with all expansions in the matrix, leading me to believe that this may be an issue with desktop itself.

The metric table for the selected value slicer is a calculated dax table using query:

MetricsTable =

DATATABLE(

    "Metric", STRING,

    {

        {"Net"},

        {"Gross"}

    }

)

Using September 2025 - PBI

[FranklinPhillip · Accepted solution]

I think I have found a work around, my data sources are mixed with some pulling from Power BI semantic models others SQL. The report I was was working on had a local model which I used to create the dax table for the measures, which I think glitched out the other source connections. When replicating the issue in the online semantic models the tables worked fine with the code left unchanged, so I think it just didn't register all the changes locally.

[Rolf_Vermeer]

Hi @FranklinPhillip 

I tried to reproduce this, but for me this works just fine. I used some quite simple measures (each being just simply a sum over two different quantities in my fact table) to simulate this, and that worked fine, showing the result as expected over multiple levels (3 in my case).

So this leads me to believe that there is something else going on. Is the SWITCH statement you posted the full SWITCH, or is something more going on?

The reason for asking is that this SWITCH is rather elaborate for something you could also write as:

IF(SELECTEDVALUE(MetricsTable[Metric]) = "Net", [Measure A] - [Measure B] )

So I suspect somewhere else in this calculation something is happening that yields the results that you describe.

[FranklinPhillip]

Hi Rolf, 

Thank you very much for your reply, at the moment this is all that is going on with the SWITCH as I am trying to get it working initially and then scale the complexity.

The measures I am working with are complicated in that they use two different facts, so this could be what's disrupting Power BI, but they both work fine when running the dax: [Measure A] - [Measure B] . I have attached the formula below if you want to look.

I tried the most simplistic version of your if statements: 

IF(SELECTEDVALUE(MetricsTable[Metric]) = "Net" , [Measure 1])) 
and this completely breaks the measure creating no results, whereas if I put Measure 1 in a variable (VAR) then this produces all data, except the bottom level of hierarchy.

My measures are:

Measure 1 =

CALCULATE(

    SUM('phsm FactBudget'[Value]),

    'phsm FactBudget'[Budget Type] IN {

        "Legal Completion Numbers Private",

        "Legal Completion Numbers HA"

    }

)
Measure 2 = 

 CALCULATE(COUNTROWS('phsm FactPlot'),
'phsm DimStage'[ Stage] >= 6)

Thanks again for trying to help with this.

[danextian]

Hi @FranklinPhillip 

At the lowest level of the hierarchy, the metrics column is expanded. In that case, SELECTEDVALUE returns a value because each row corresponds to a single metric. When the hierarchy isn’t expanded, the metrics column contains multiple values (e.g., Net and Gross), so SELECTEDVALUE evaluates to blank.

Since BLANK() is not equal to "Net", the condition

SELECTEDVALUE(MetricsTable[Metric]) = "Net"

evaluates to FALSE(). As a result, the measure falls back to returning the variable Shortfall.

Try rewriting your measure

VAR _Metric =
    SELECTEDVALUE ( MetricsTable[Metric] )
VAR Shortfall =
    [Measure A] - [Measure B]
RETURN
    IF (
        -- If more than one metric is in context (not at lowest hierarchy level)...
        NOT ( HASONEVALUE ( MetricsTable[Metric] ) ),
        -- ...then return a fallback measure.
        [some other measure],
        -- Otherwise, when exactly one metric is selected, decide based on its value:
        SWITCH (
            _Metric,
            "Net", Shortfall,             -- If Net, return Shortfall.
            "Gross", [some other measure] -- If Gross, return another measure.
        )
    )

[FranklinPhillip]

Hi @danextian ,

Thank you very much for your help. I tested the measure only looking at the SELECTEDVALUE(MetricsTable[Metric]) and it reads all of it correctly across all layers - Gross/Net when either are selected, so I don't think this is a case of picking up multiple values. Also the Metrics table is stand alone with no relationships so I don't think it's filtering multiple values.
 

I gave some more context to the measures above in my previous reply if this is any help, if not don't worry and thank you for your help.