Forum Discussion

FranklinPhillip's avatar
FranklinPhillip
New Member
11 months ago
Solved

Switch/IF error when expanding a matrix

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 measur...
Need Help
  • FranklinPhillip's avatar
    FranklinPhillip
    11 months ago

    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's avatar
Rolf_Vermeer
New Member
11 months ago

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's avatar
    FranklinPhillip
    New Member
    11 months ago

    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.