Conditional Format a single Cell based on another cell

[lardo5150]

I have created this matrix.

I have engineer name, and then the numbered columns are the day of the month.

For each engineer, for each day of the month, we are showing how many cases they took.

In the data for each day, there is also another field called Availability.

What I would like to do is for each day, regardless if they took a case or not, if the value for Availability is "unavailable", I want that specific cell to be a different color.

I can't figure out how to do this.

I tried creating a custom column where if that day they are unavailable, it populates the column with a 1.

if they were available, it populates it with a 2.

I have tried multiple scenarios with conditional formatting and I can't get it to work.  

Using this as an example:

This test engineer was:

1st - available

2nd - unavailable

3rd - available

4th - unavailable

So the cell with a total of 4 cases, the cell with a total of 0 cases, these cells should be a different color.

Does anyone know how to do this correctly?

Here I am marking unavailable with a 1.  This is not working either.

[amitchandak · Accepted solution]

@lardo5150 , BAsed on what I got create a measure like

Either a measure of max(Table[Availabile])

Then have color measure

Switch([Measure],

1, "Red",

2,"Green",

3, "Blue", "Yellow")

And us that is conditional formatting using field value option

another example

Color = Switch( True() ,
Max(Table[Status]) < 200 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Max(Table[Status]) < 500 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
)
)

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...