Introduction
In this blog post I will share with you how to effectively leverage conditional formatting with a low code approach suitable for enterprise grade solutions.
I have found that there are two key components that are critical for success:
Field Values – predefined measures that dynamically specify colour values, icons and even Unicode characters.
Power BI dataflows – a low code method that creates a single source of truth of field values and is effortless to manage and ensure consistency across all your Power BI reports.
There are alternative approaches, but I have found that the combination of field values and Power BI dataflows has delivered the quickest and most effective results for my own customer solutions.
Field Values
Field values enable you to specify customised colour values, icons and even Unicode characters. You can create a measure to dynamically calculate different values depending on business logic rules you define, which instructs the conditional formatting for a set of visuals.
This is an excellent choice for enterprise grade solutions because it is usually quicker than the alternative of creating many rules for each individual visual.
I’ve listed some examples of field values you can use below, and there are also some lovely examples in this Power BI community blog post: Tips on displaying icons based on field values in ... - Microsoft Fabric Community
Note: It is also possible to create custom icons using an SVG xml approach, however, it falls outside the scope of this blog as I do not consider it a low code approach suitable for enterprise grade solutions.
Colour Values
Supported formats include:
hex codes e.g. "#93B856"
RGB or RGBA values e.g. "rgb(147, 184, 86)"
HSL or HSLA values e.g. "hsl(83, 41%, 53%)"
colour names e.g. "Green"
I tend to pick a theme colour, choose a tint for a lighter background colour and contrast this with a shade for a darker font colour. There are many websites available to do this, but my favourite is this one because it generates an entire range of tints and shades:
Icon sets
There are a set of text values that can be used to specify an icon within the built-in icon sets in Power BI. For more details, please refer to the screenshot below provided by Francisco Mullor Cabrera and in his insightful blog here: Names of the Icons. Update- (powerbisp.com)
You can also define custom icon sets via a theme file. More details are explained here.
Unicode HTML
DAX supports Unicode encoding which means Power BI can use Unicode characters for conditional formatting. Each symbol has its own HTML-code and the two relevant DAX functions to parse the HTML-code are:
UNICODE ( <Unicode character> ) = Unicode HTML number
e.g. UNICODE ( “!” ) = 33
UNICHAR ( <Unicode HTML number> ) = Unicode character
e.g. UNICHAR ( 33 ) = “!”
The wonderful thing about Unicode characters is that a lot of them also enable you to change the font colour. This means there are a lot of possible combinations so you can be extremely flexible in crafting the desired effect.
Power BI dataflows
Power BI dataflows enable you to create reusable transformation logic that can be referenced by any number of semantic models in Power BI.
This is an excellent choice for enterprise grade solutions because it enables you to create a single source of truth for field values that can be used by conditional formatting.
Furthermore, it makes management of the field values extremely agile and flexible because you can update the field values very easily just by updating a single dataflow. All solutions that reference this dataflow will reflect the changes upon the next model refresh.
The alternative is to either spend valuable time manually updating the definitions in each model or create a script which requires deeper coding knowledge and third-party tools to execute e.g. using C# in Tabular Editor.
Here is an example of a version I use in my solutions. You can manage this either directly in the dataflow itself, or a convenient location of your choosing e.g. an Excel spreadsheet on SharePoint.
Create Measures
Once the dataflow output is imported into the semantic model, you can then extract the relevant field value. For example, the background colour for green is detailed below:
The next step is to create a measure that dynamically adjusts the output depending on your key performance indicator (KPI).
For example, the [Margin Delta %] is the percentage difference between the KPI (Margin in this case) and the target. If the percentage is less than zero then it is below target (and red), and if the percentage is equal to zero or above then it is on or above target (and green).
The last step is to reference this measure in your visual. Most field values are compatible with the conditional formatting dialog box (e.g. colour values, icons), though if you have opted for Unicode characters then these can be dropped directly into the visual.
The results for Margin % against a target of 55% are shown below for completeness:
Finally, it’s worth noting that these measures can be nicely organised and hidden away from end users using folders. There are many ways to organise measures, so I’ve shared what has worked well for me below as an example.
Summary
By using field values for conditional formatting, you can dynamically specify colour values, icons and even Unicode characters. Implementing this with Power BI dataflows makes it effortless to manage and ensure consistency across all your Power BI reports.
Comments