New Card Visual - Conditional Formatting using DAX

[Anonymous00729]

Hi all,

I'm currently doing a project and need some assistance. I'm still finding my way around DAX and I'm trying to get my MoM % change to turn red if the change is negative and green when it's positive. This is the DAX code, I've been using:

Revenue MoM % with Arrow =

VAR upArrow   = UNICHAR(9650)   // ▲

VAR downArrow = UNICHAR(9660)   // ▼

VAR change    = [MoM % ▲]

RETURN

IF(

    change > 0,

    FORMAT(change, "0.00%") & " " & upArrow,

    FORMAT(change, "0.00%") & " " & downArrow

)

But, my card is showing me this...

Please can someone assist me.

Thank you.

[Ahmed-Elfeel · Accepted solution]

Hi @Anonymous00729,

This Approach should work with you give it a try :

Revenue MoM % with Arrow = 
VAR upArrow   = UNICHAR(9650)   // ▲
VAR downArrow = UNICHAR(9660)   // ▼
VAR change    = [MoM % ▲]
VAR formattedText = 
    IF(
        change > 0,
        FORMAT(change, "0.00%") & " " & upArrow,
        FORMAT(change, "0.00%") & " " & downArrow
    )
RETURN
formattedText

To apply conditional formatting in Power BI:

• Select the Card Visual

• Go to Format → Data label → Conditional Formatting (fx)

• Choose Field value → select your measure

• Rule:
  • If value > 0     →    Green
  • If value < 0    →     Red

Alternative DAX only approach (if you want everything in one measure):

• This approach would only work with specific custom visuals designed to interpret HTML
• Standard Power BI visuals (like the Normal Card visual) dont render HTML

Revenue MoM % with Arrow = 
VAR upArrow   = UNICHAR(9650)
VAR downArrow = UNICHAR(9660)
VAR change    = [MoM % ▲]
VAR textColor = 
    IF(
        change > 0,
        "<span style='color:green'>",
        "<span style='color:red'>"
    )
VAR formattedText = 
    IF(
        change > 0,
        FORMAT(change, "0.00%") & " " & upArrow,
        FORMAT(change, "0.00%") & " " & downArrow
    )
RETURN
textColor & formattedText & "</span>"

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly

[ChielFaber · Accepted solution]

The logic behind color formatting should be done with a measure based on a numerical value. Format() returns a text value. 

My advice would be to seperate the calculation and the color designation. 

I recreated your problem and with the following code you get your solution:

Revenue MoM % :=
VAR Curr = [Total Revenue] -- huidige maand
VAR Prev = [Previous Month Revenue] -- vorige maand
RETURN
DIVIDE( Curr - Prev, Prev )

Revenue MoM % with Arrow :=
VAR upArrow = UNICHAR(9650) -- ▲
VAR downArrow = UNICHAR(9660) -- ▼
VAR change = [Revenue MoM %]
RETURN
FORMAT( change, "0.00%" ) & " " &
IF( change > 0, upArrow, downArrow )

The above on is the value you use in the card visual

Measure for color formatting:

Revenue MoM Color :=
VAR change = [Revenue MoM %]
RETURN
SWITCH(
TRUE(),
ISBLANK(change), "#808080", -- gray when no value
change > 0, "#008000", -- green
change < 0, "#C00000", -- red
"#000000" -- black as fallback
)

And then you need to put the color measure in the formatting pane.
Format panel → Data label → Color → fx → Format by: Field value → Select Revenue MoM Color.
Hope this helps.

[Anonymous00729]

Thank you so much for your help. This worked perfectly!

[Ahmed-Elfeel · Accepted solution]

Hi @Anonymous00729,

This Approach should work with you give it a try :

Revenue MoM % with Arrow = 
VAR upArrow   = UNICHAR(9650)   // ▲
VAR downArrow = UNICHAR(9660)   // ▼
VAR change    = [MoM % ▲]
VAR formattedText = 
    IF(
        change > 0,
        FORMAT(change, "0.00%") & " " & upArrow,
        FORMAT(change, "0.00%") & " " & downArrow
    )
RETURN
formattedText

To apply conditional formatting in Power BI:

• Select the Card Visual

• Go to Format → Data label → Conditional Formatting (fx)

• Choose Field value → select your measure

• Rule:
  • If value > 0     →    Green
  • If value < 0    →     Red

Alternative DAX only approach (if you want everything in one measure):

• This approach would only work with specific custom visuals designed to interpret HTML
• Standard Power BI visuals (like the Normal Card visual) dont render HTML

Revenue MoM % with Arrow = 
VAR upArrow   = UNICHAR(9650)
VAR downArrow = UNICHAR(9660)
VAR change    = [MoM % ▲]
VAR textColor = 
    IF(
        change > 0,
        "<span style='color:green'>",
        "<span style='color:red'>"
    )
VAR formattedText = 
    IF(
        change > 0,
        FORMAT(change, "0.00%") & " " & upArrow,
        FORMAT(change, "0.00%") & " " & downArrow
    )
RETURN
textColor & formattedText & "</span>"

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly

[Anonymous00729]

Thank you so much for your help, this worked! 

And thank you for the HTML bit as well, I will keep this for future reference.