Forum Discussion
Add conditional formatting in graph applicable for all measures using slicer
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.
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesI 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:Used this new measure in Conditional Formatting, Format by -> Field Value.Measure Selection for Color = IF([Measure Selection] > 0, "GREEN", "RED")
Doesn't matter which option I use in my slicer, every number above 0 is now green, and every number below 0 is red.
3 Replies
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesamitchandak , 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"
)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:Used this new measure in Conditional Formatting, Format by -> Field Value.Measure Selection for Color = IF([Measure Selection] > 0, "GREEN", "RED")
Doesn't matter which option I use in my slicer, every number above 0 is now green, and every number below 0 is red.