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.
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.