Power BI Conditional Formatting by Rows
Recreating Excel table in Power BI having applied conditional formatting on a row-level
Introduction
The Conditional Formatting capabilities of Power BI allow you to apply either rule based or dynamic formatting on different kind of visuals. You can change colors, add icons or data bars together with the ability to control various color options such as color gradients and lower and upper thresholds. Though, in spite of these advanced formatting capabilities, there are still cases where you might reach the limit of what is possible out-of-the-box. For instance, this is very likely to arise by shifting Reporting from Excel to Power BI.
Requirement
The requirement in my particular case was to apply dynamic color scaling beyond the values of a single column, but the values of different columns in the corresponding row. The following Excel table needed to be transitioned to Power BI:
The table shows date values having a conditional formatting rule defining the color gradient. The greener, the higher the date. In a perfect world, the dates should be arranged chronologically from the left to the right for each row. This helps to identity outliers. In Excel, this is a quite easy task since you can manually define the value range for each row.
Problem
Power BI on the other hand, only allows you to format values by color scale using either the lowest and highest value of a specific column OR setting fixed values as the thresholds. Unfortunately, neither of these options worked for the given requirement. Firstly, since the different dates within a column have no relation to each other and secondly, fixed thresholds cannot be set as the values can vary and therefore they should be allocated dynamically. Besides, conditional formatting on date values isn't actually supported at all in Power BI. However, you can transform them into VALUEs using DAX, which we will also do next.
Solution
The idea is to relativize the different values of each single column by transforming them into percentages for making them comparable with each other. This then also allows to set fix thresholds as the lower and upper limit.
For this, we first need to determine the lowest and highest date value for each row. We can achieve this by creating two new measures based on temporary tables in DAX involving all desired fields through UNION and finally return the lowest (MIN) and highest (MAX) value as a single value measure. Here the example for “Date MAX”:
Next, we need to define the respective percentage of all fields involved based on the new MIN and MAX measures. Here the example for the field “Date 1”:
Once we have this done for all our fields, we can finally configure the conditional formatting by color scale based on the corresponding percentage measure. The Minimum need to be set to 0 and the Maximum to 1, expressing 0-100 %.
Repeating this conditional formatting step for each column brings us finally to the desired end result.
Conclusion
This logic can be applied to as many columns as you wish holding quantifiable measures. Power BI enables you to achieve lots of pretty cool things thanks to its flexibility and through the joint usage of different functionalities. I hope you enjoyed reading this post and it helps you out in case you are facing a similar requirement.
