• Samuel Woldu

Dynamic Conditional Formatting in Power BI

Updated: Aug 28, 2019

When using Power BI out of the box, it's not be apparent straight away that you can dynamically conditional format the colour of your values.... but you can! Where there's a idea and a bit of brains there's a way!!


Before you begin lets categorise the conditional formatting options to avoid confusion.


Static Conditional Formatting (SCF): Where DAX is used to hard code the colours dependant on the value of the row being evaluated.

Dynamic Conditional Formatting (DCF): Where DAX is used to dynamically change the colours dependant on end user colour selection and the value of the row being evaluated.


I've broken down the steps required below to set up DCF, if you want to create SCF you can find the DAX required in the PBIx.



Create 3 disconnected tables that allow the end user to select the colour formatting they wish to apply.


Base Colour


On the home ribbon select the 'Enter data' button and create a Base Colour table with 2 columns.




Middle Colour




Repeat the process again, this time call the table 'Middle Colour'.







Top Color



Repeat the process for the last time, this time call the table 'Top Colour'.









 


After you have created the tables, make sure the colour selections are available to the end user on the report page in the form of a slicer. Enable single select on all the slicers to ensure only the colour combinations option's we captured in the DAX are available to end users.


To avoid any unwanted filtering, make sure no relationships have been created in the data model between the tables themselves and the rest of the data model.



Next you need to create a measure which uses a combination of the following DAX functions:


  1. SWITCH and TRUE which will evaluate to true dependant on the combination of filters selected, the value of measure being evaluated, and the HEX colour to apply to the evaluated value

  2. SELECTEDVALUE returns the combination of filters selected by the end user back to SWITCH function to allow it to evaluate to true and apply to the correct formatting accordingly Screenshot of 'Dynamic RAG Conditional Formatting' measure below.




Next you need to apply the logic of DCF measure to a visual.



Select the visual, then the column you want to apply the DCF to. There are a number of options you can apply DCF too, in this example I've chosen Background colour option.

















Next select the Field value from the Format by drop down. This will allow you to select the DCF measure we have created.




Now you can select the measure that you have created.













The DCF should now be applied to the visual and column you've selected. Example of a report that I've created.





Hope you have found this useful, using the same logic above you can apply dynamic conditional formatting based on any colour combination you want.


In an upcoming post I will use the same logic to take into consideration people with visual impairments using colour bling friend colour pallets and icons.


Let me know if you have found this useful 😊


Sam



1,105 views1 comment