Forum Discussion

dedelman_clng's avatar
dedelman_clng
Community Champion
2 months ago
Solved

Conditional Formatting breaks with "forced" zero

I have a relatively simple matrix display with a single measure, on which I am using Conditional Formatting. 

 

Threshold Count UC-MV = 
var __ThisType = SELECTEDVALUE(Event_Tbl[UC])
var __Cnt = SWITCH(__ThisType,
            "MV-C", COUNTROWS(FILTER(SUMMARIZE(DimValves, DimValves[Valve], "Cnt", [Movement-Close]), [Cnt] > 0)),
            "MV-O", COUNTROWS(FILTER(SUMMARIZE(DimValves, DimValves[Valve], "Cnt", [Movement-Open]), [Cnt] > 0)),
            "UC-C", COUNTROWS(FILTER(SUMMARIZE(DimValves, DimValves[Valve], "Cnt", [Uncommanded-Close]), [Cnt] > 0)),
            "UC-O", COUNTROWS(FILTER(SUMMARIZE(DimValves, DimValves[Valve], "Cnt", [Uncommanded-Open]), [Cnt] > 0))
                        
) + 0  // +0 added here to force a value to be shown
return __Cnt

 

The measures inside this measure are of the form

Movement-Close = COUNTROWS(FILTER(Event_Tbl, Event_Tbl[UC]="MV-C"))

(This has to be 4 separate measures based on user-requirements for the report - a story for another day)

(Also, do not want to add "+0" here, as a different matrix will show more detail and we want to leave blanks to be blanks in that matrix)

 

I am doing conditional formatting based on the "Train" column, which is a parent of DimValves.

Event_Tbl from the inner measure is a child of DimValves.

 

When the measure has a non-zero value everywhere, conditional formatting works. 

 

 

But if the measure returns 0 (meaning it would return BLANK if I hadn't added the "+0" line), the columns with zero are not formatted

 

I have tried both with Rules based on the the Train column (Text = Train 1, etc), and using "Field Value" with a measure that returns the proper color based on the value of Train; neither works for the zero columns.

 

Is there something about the measure that is breaking the conditional formatting, or possibly a bug in Power BI itself?

 

I should also mention I have tried without the forced 0 and using "Show items with no data", but that supresses the blanks completely.

 

The report is difficult to anonymize, as would providing sample data, but if there is nothing obvious in the code above, I can try to put together a sample showing the behavior.

 

Thanks in advance,

David

  • dedelman_clng I think the issue might be this condition: can you test by adding color for catch all and see if blank / 0 shows in that color

     

    Summary Color = var __Train = SELECTEDVALUE(DimTrain[Train])
    return 
        SWITCH(__Train,
                "Train 1", "#BFDEF0",
                "Train 2", "#F8CA93",
                "Train 3", "#C4C4C6", "Red"
    )

     

    In the attached file, I tried to reproduce your issue, but the conditional formatting for blank (shown as zero) is working fine.

     

     

  • dedelman_clng I don't think it is a bug; it seems like a modelling-related issue. It will be very hard to pinpoint without knowing all the modelling details. 

6 Replies

  • dedelman_clng I think the issue might be this condition: can you test by adding color for catch all and see if blank / 0 shows in that color

     

    Summary Color = var __Train = SELECTEDVALUE(DimTrain[Train])
    return 
        SWITCH(__Train,
                "Train 1", "#BFDEF0",
                "Train 2", "#F8CA93",
                "Train 3", "#C4C4C6", "Red"
    )

     

    In the attached file, I tried to reproduce your issue, but the conditional formatting for blank (shown as zero) is working fine.

     

     

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion

      parry2k - when giving an "else" value, the 0s do appear with the "else" color. Is this possibly a bug, or a problem in my model/code?

       

      I've never seen this behavior before, but admittedly almost all of the times I've used conditional formatting it was based on the value of the measure, not on one of the column headers.

       

      David

  • dedelman_clng I don't think it is a bug; it seems like a modelling-related issue. It will be very hard to pinpoint without knowing all the modelling details. 

  • dedelman_clng what is your conditional formatting requirement?

     

    If Train 1 = Blue. Train 2 = Orange and Train 3 = Grey, is this correct understanding?

     

    How you are fomatting right now?

     

    • dedelman_clng's avatar
      dedelman_clng
      Community Champion

      Yes - apologies. The formatting is exactly that - Train 1 = Blue, Train 2 = Orange, Train 3 = Gray

       

      Under "Specific Column" I have left the default settings, and under Cell Elements -> Background I have tried both Rule based formatting (Train = "Train 1" then Blue, etc) and Field Value based formatting

      Summary Color = var __Train = SELECTEDVALUE(DimTrain[Train])
      return 
          SWITCH(__Train,
                  "Train 1", "#BFDEF0",
                  "Train 2", "#F8CA93",
                  "Train 3", "#C4C4C6")

      Both leave the 0s with no formatting, but work correctly if the value is non-zero.

       

      Thanks,

      David

  • dedelman_clng 

     

    Fix: apply the same logic to your color measure.

    Color Measure = SWITCH(SELECTEDVALUE('DimValves'[Train]), "Train 1", "#FF0000", "Train 2", "#00FF00", "#CCCCCC")

    If it still skips zeros, the matrix isn't truly rendering those cells, so force the zero into a real value earlier in the chain, not appended at the end.