Conditionally format bar chart column colour based on slicer value

[msommerf]

I have the following visuals:

I am looking to change the colour of each bar based on the selection of the Depot slicer.

If depot slicer is Depot 1, the Depot 1 bar is highlighted

If depot slicer is Depot 2, the Depot 2 bar is highlighted

Can anyone advise if this is possible with a measure and conditional formatting?

Any assistance appreciated.

[Icey · Accepted solution]

Hi @msommerf ,

Try this:

1. Create a separate Depot table.

Depot = VALUES ( 'Table'[Depot] )

2. Create a Measure like so:

Highlight = IF ( MAX ( 'Table'[Depot] ) = SELECTEDVALUE ( Depot[Depot] ), "Yellow", "Blue" )

3. Set conditional formatting of Data color in Clustered column chart.

4. Test.

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nsmith1]

Hi 

That's fantastic. Is there any way to change the format the axis labels using the slicer in the same way? I want to be able to only show the label of the selected column by changing the font colour of the other labels to white. I tried it in your example file by changing the conditional formatting of the X axis value settings but the only effect was that all three labels changed to yellow when I select Depot 3, they all remained blue when selecting Depot 1 or Depot 2. I guess this is something to do with the Max function in the Highlight measure. It's odd that the conditional formatting behaves differently for different elements in the same chart.

Many thanks

Nigel

[ZL140]

Hey Icey, I've implemented your solution and it works great! Thank you for that. I do however have a question. Is it possible to use the slicer as a filter for other visuals? In my model I get a circular reference error when trying to make the relationship. 

Thank you!

ZL

[amitchandak]

Create a measure like this

Color Measure = if(Max(Table[Depot] in allselected(Table[Depot])) <today(),"Yellow","Blue")

And use field options and measure in conditional formatting

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values