Conditional formatting of card values - depending on how often in a row a value is below target

[nick1097]

Hi all,

I have a question and hope you can help me. I want to implement a conditional formatting for card visuals.

Scenario:

I have a simple table with actual values and target values per month. For the colouring I want to use green, yellow and red. When should what be coloured:

Green = actual value is better or equal than the target

Yellow = actual value is below target for the first time in a row

Red = actual value is below target for at least two times in a row

This is an exemplary table:

Month	Value	Target	(to be colour)
Jan	22	20	green
Feb	34	20	green
Mar	12	15	yellow
Apr	13	16	red
May	18	18	green

Kind regards

Nick

[nick1097]

I did some experiments and it seems that the "new" card visual is the problem. In the "old" card visual the colouring with the above formula is working. Why is that the case? Or am I doing something wrong. Does anyone have a similar problem?

[v-veshwara-msft · Accepted solution]

Hi @nick1097 ,

Thanks for reaching out and sharing your scenario.

We’d like to confirm that there is currently a known issue with the new Card visual in Power BI related to conditional formatting. Specifically, when no small multiples or categories are used, newly added or modified conditional formatting rules may not be applied correctly. Existing rules will continue to work as long as they are not edited or re-saved.

This behavior does not affect the classic Card visual, which is why your formatting logic works correctly there.

The issue has been reported and is being tracked by the product team. As a workaround, we recommend continuing to use the old Card visual for conditional formatting scenarios until a fix is available.
Related community discussion: Previously setup/working conditional formatting no... - Microsoft Fabric Community

Please let us know if you need further assistance.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best regards,
Vinay.

[nick1097]

I saw some solutions with conditional formatting by field value and "color measures". I tried this "color measure" already for a more simple example:

(please ignore the red lines, I just deleted text for confidentiality)

The measure works somehow, as I inserted it as additional data in the card visual. It shows the Crimson text but the color is not given for the font. I really don´t know why. See here is still everything black:

I can´t share the pbix unfortunately. I can´t explain the missing colouring. I inserted the "Status" measure for the field value option in the conditional formatting.

[nick1097]

Thanks for your answer!

With "in a row" I mean consecutive. And my exemplary table was only an abstraction. Of course I have a proper date table and date column.

If the target wasn´t reached in Mar 25 and Apr 25, then the actual value for Apr 25 should be coloured red in the card visual (because the target was missed twice in a row).

Regarding your code, I want to add that I do not necessarily need to do it directly in the table (PowerQuery). I just wanted to explain the logic with the exemplary table.

The code seems difficult to me. Is there a more simple solution? Maybe in DAX?

[lbendlin]

No, DAX does not support this kind of conditional aggregation. It has no concept of "in a row", it doesn't even know what a row number is unless you tell it.  Some of this might be possible with Visual calculations.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

what's "a row"? you mean over time? Do you have a better date column than just the month name?

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTwSsxTMDJV0lEyMgIRBkqxOiBht9QkiLCxCbKwb2IRRNgQpNrQFCrsWAATNgYRZnDVlVBhCwgRGwsA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Month = _t, Value = _t, Target = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Month", type date}, {"Value", Int64.Type}, {"Target", Int64.Type}}
  ), 
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Colour", 
    (k) =>
      if k[Value] >= k[Target] then
        "green"
      else
        let
          prevrow = Table.Last(Table.SelectRows(#"Changed Type", (h) => h[Month] < k[Month]))
        in
          if prevrow[Value] >= prevrow[Target] then "yellow" else "red"
  )
in
  #"Added Custom"