Conditional formatting of columns in a matrix visual using multiple conditions

Have you ever wondered if it’s possible to apply conditional formatting on a matrix visual based on multiple conditions or rules? For instance, say you have multiple columns and you want to format one of these columns based on values of two or three other columns.

You probably noticed that you can only format by a selection of one field in Power BI and not multiple.

However, I am here to tell you that you can actually format by multiple conditions or columns, with a bit of trickery by creating a calculated measure and using it in the format by rule option.

In this blog, you will learn how to use a measure as a field within the conditional formatting visualization pane. For this example, I wanted to place multiple conditions on the “Completion date” column, as seen below. So that special attention can be given to those tasks that need to be completed.

The resultant outcome of  which looks like this:

So, let’ jump over to the steps:

(Step 1) Navigate to the Conditional Formatting option for the column that you want to format:

Once you’ve created your matrix visual, select Format from the Visualizations pane

Then scroll down until you see Conditional formatting. So, I’ve selected the Completion date from the dropdown list.

For this example, I am using Icons for the Completion date. So I switched Icons on. Next, you need to click on the Advanced controls option

(Step 2) Create a Measure to be used when formatting as a Rule.

Before I show you the calculated measure that I have created for this specific example. I want to first explain the obstacle that we are dealing with.

When I want to format the Completion date column, by Field value, I am unable to select the completion date column. So that is not an option.

Then when I want to format by rules, my only two options are to count or distinct count the completion date, which obviously does not make sense.

So that brings me to the measure:

I have created a measure that assigns numerical values to specific conditions. I have named it “Condition Completion date” so that I can immediately see that this is the condition that will be used as a rule in the formatting pane for the Completion date column.

So, for this specific example, I have used more than one column, namely, “Days until due date” and ” status”, and I’ve made use of the values 1 if the condition holds, and 2 if not.

(Step 3) Apply the Calculated measure within the Conditional formatting 

Now that the measure is created, you can format your column by the measure (Condition Completion date), instead of the date column/s itself, and then select the desired icons that you want which will link to the values, 1 and 2 that you have set in the calculated measure.

Then after you’ve pressed OK, you will see the icons on your matrix visual, and it will look something like this:

Mission accomplished!

For other interesting blogs, you can also visit our blog site at: https://databear.com/blog/

Official Microsoft information pertaining to this topic: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-tooltips

Learn more about Power BI in our training course:  https://databear.com/power-bi-training/