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.
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 high level process is to:
- Create a measure that returns a colour as the result
- It can be a word, such as blue, red, green
- It can be a hex code for a colour, like #40E0D0″, “#FFA07A”
- 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.