Conditional Formatting with a Text Field in Power BI

One of the things I like about my live, online training courses is that I get to hear great questions from the trenches from people learning DAX and Power BI.  Last week, John asked me how to apply conditional formatting with a Text field (not a numeric field).  I knew it could be done, but it required some brief research before I could give an answer. It is pretty easy to apply conditional formatting on a numeric field – this  feature was added to Power BI some time back. However Microsoft has also added the ability to apply conditional formatting to a Text field too in some circumstances.

Example

To understand the process of configuring this, consider the following simple data table.

I just entered some simple sample data using the “Enter Data” menu option. As you can see Project 2, Project 3 and Project 5 have departments associated with them while Project 1 and Project 4 do not.

Suppose you want to use conditional formatting to highlight (colour code) which of the Projects have Departments associated with them and which do not.

The Method

The high level process is to:

1. Create a measure that returns a colour as the result
   1. It can be a word, such as blue, red, green
   2. It can be a hex code for a colour, like #40E0D0″, “#FFA07A”
2. Use conditional formatting and use the measure to apply the formatting on the text as a rule.

Here is the step by step process explained.

To start with, I created a test measure as follows.

Test =
     VAR Dept = SELECTEDVALUE(Projects[Department]) 
     RETURN IF(Dept <> BLANK(), Dept, "No Dept")

Next, I placed a table visual in the report and added  the columns project, department and the test measure.

As you can see, the measure identifies which of the projects have a department and which do not.  So this test measure has the logic required to go to the next step.

Next, I created a new measure [Colour Project], taking the logic from the test measure that I created above and modifying it to output names of colours.  This can be simply achieved by returning hex codes or common names of colours.

Colour Project =
    VAR Dept = SELECTEDVALUE(Projects[Department])
    RETURN IF(Dept <> BLANK(), "#40E0D0", "#FFA07A")

The next step is to turn on the conditional formatting for the project column so that it is coloured based on the measure [Colour Project] above.

To apply the conditional formatting, I clicked on the down arrow (#1 below) next to project and then on conditional formatting (#2 below) and then on background color (#3 below).

In the background color dialog that appears, select format by field value (#1 below) and based on field color project (#2 below). Click on OK.

The text field “project” is now conditionally formatted by the logic given by the measure [Colour Project] using the chosen Hex Codes.

It is also possible to apply conditional formatting using words, such as “Green” and “Red”.

To illustrate this, I created the measure [Colour Test] based on the logic previously used as follows.

Colour Test =
    VAR Dept = SELECTEDVALUE(Projects[Department])
    RETURN IF(Dept <> BLANK(), "Green", "Red")

Next, I applied the conditional formatting on the original measure [Test] using font color. I used format by color test. And the result is as follows.

A Final Note

It is worth noting that I am using the table visual for this article.  All columns and measures are are placed in the Values section of the visual. Conditional formatting works only when a column or measure is in Values section of a visual. If we use a matrix instead and place project on rows, you cannot apply conditional formatting to that field.