Add conditional formatting in graph applicable for all measures using slicer

[Namoh]

Hi everybody, let me explain my issue.

I made 5 (the same) graphs, with each showing 1 measure.

For each graph when the value was above 0 the bar would turn green and below 0 it turned red.

Using 5 graphs cost a lot of space.

Note: the Werkeenheid / Facility (y-axis) is a different order depending on the measure shown (because of classified data I had to remove the names).

Therefore I combined the 5 measures in 1 graph using a slicer (users can only select one option).

This looks much better then 5 different graphs and is easy to use.

But here comes my issue, I can only put a conditional format on 1 measure.

This means that for the other 4 measures my graph doesn't correctly show the colors green and red.

The only thing I want is a condition that makes every value (no matter which measure is selected via the slicer) greater then 0 turn green, and any value below 0 turn red. Nothing more, just that, red or green.

It seems a simple request that Power Bi should be able to do.................but I don't know how.

[amitchandak · Accepted solution]

@Namoh , Create a color measure based on this slicer and use that with "field value."

Example of color measure, please add logic based on the selected measure

https://www.youtube.com/watch?v=RqBb5eBf_I4

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[Namoh · Accepted solution]

I had my slicer made, like this:

Measure Selection = SWITCH( TRUE(),

VALUES('Navigatie'[Losses]) = "Deviation from Target Utilisation Downtime", [Deviation from Target Utilisation Downtime],

VALUES('Navigatie'[Losses]) = "Deviation from Target Planned Downtime", [Deviation from Target Planned Downtime],

VALUES('Navigatie'[Losses]) = "Deviation from Target Unplanned Downtime", [Deviation from Target Unplanned Downtime],

VALUES('Navigatie'[Losses]) = "Deviation from Target Speed Downtime", [Deviation from Target Speed Downtime],

VALUES('Navigatie'[Losses]) = "Deviation from Target Quality Downtime", [Deviation from Target Quality Downtime],

BLANK())

I made another measure that looked at the slicer-measure value:

Measure Selection for Color = IF([Measure Selection] > 0, "GREEN", "RED")

Used this new measure in Conditional Formatting, Format by -> Field Value.

Doesn't matter which option I use in my slicer, every number above 0 is now green, and every number below 0 is red.

[Namoh]

@amitchandak , thanks for the video.

I had a look, but I'm not quite familiar with DAX / SWITCH function.

Hope you can give me a push in the right direction for the SWITCH function.

The column the 5 measures are located in, is called: kpiexport

The measures are called: 

- Deviation from Target Utilisation Downtime

- Deviation from Target Planned Downtime

- Deviation from Target Unplanned Downtime

- Deviation from Target Speed Downtime

- Deviation from Target Quality Downtime

I can't get it working. I might be going complete the wrong way.

How should the 1ste line of the measure look like?

SWITCH( TRUE(),
IF('kpiexport'[Deviation from Target Utilisation Downtime])= >=0, "green",
IF('kpiexport'[Deviation from Target Utilisation Downtime])= <0, "red",
IF('kpiexport'[Deviation from Target Unplanned Downtime])= >=0, "green",
IF('kpiexport'[Deviation from Target Unplanned Downtime])= <0, "red",
IF('kpiexport'[Deviation from Target Planned Downtime])= >=0, "green",
IF('kpiexport'[Deviation from Target Planned Downtime])= <0, "red",
IF('kpiexport'[Deviation from Target Speed Downtime])= >=0, "green",
IF('kpiexport'[Deviation from Target Speed Downtime])= <0, "red",
IF('kpiexport'[Deviation from Target Quality Downtime])= >=0, "green",
IF('kpiexport'[Deviation from Target Quality Downtime])= <0, "red",
"silver"
)

[amitchandak · Accepted solution]

@Namoh , Create a color measure based on this slicer and use that with "field value."

Example of color measure, please add logic based on the selected measure

https://www.youtube.com/watch?v=RqBb5eBf_I4

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values