Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional formatting background_empty cells

Hi,

Need help with conditional formatting. I have a table with activities and statuses (closed/open). So I added this measure (called Measure) but it gives value 2 to blank cells as well:

 

Measure =
SWITCH(MAX([Table]),
"Closed",2,
"Open",1
)
 
                                                                                                   Now
 
So how do I change the Measure so that it leaves blank cells without value and they thefore not colored at all:       
                                                                                                     To be
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    According to your description, I think you can create a measure, It only judge the two states of open and closed.

    like this:

    Measure = IF(MAX('Table'[Status])="open","Red",IF(MAX('Table'[Status])="closed","Green"))//Omit blank()

    Then use it in condifitional formatting.

    Like this:


    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Blank cell can not be colored

     

    Try this

    color measure =

    var _1 = coalesce(MAX([Table]), "Closed")

    return

    SWITCH(_1,
    "Closed","Green"
    "Open","Red"

    )

     

    Use this in conditional formatting using field value option
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak, thanks! but I need this measure as an input in conditional formatting for background color of cells thus the values 1 and 2. And with your modification I can not choose the measure in conditional formatting anymore.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    According to your description, I think you can create a measure, It only judge the two states of open and closed.

    like this:

    Measure = IF(MAX('Table'[Status])="open","Red",IF(MAX('Table'[Status])="closed","Green"))//Omit blank()

    Then use it in condifitional formatting.

    Like this:


    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.


    Best Regards,

    Community Support Team _ Janey