Millington Offices (10).png

Text based conditional formatting in Power BI

Updated: 2 days ago

Have you ever wondered if you can apply conditional formatting based on a text field/measure instead of a numeric field/measure?

If your answer is yes, then this trick is for you!

The other day I was working with a customer who asked something that I had no idea how to build.

They wanted to apply conditional formatting over some of their visuals, but they wanted the conditional formatting applied over a text field and not over a numeric field or a measure.

Let me give you a practical example. Imagine I have a table with sales data. In my table I have sales by country, product, shipping status etc.

Now, imagine that you wanted to apply conditional formatting over the status field which contains the following categories:

> Pending

> Shipped

> Cancelled

But... You don't have an "ID" column for the status, something like:

So you don't have that "Status ID" column, only the status text. I know, you could easily create the status ID column either in the Query Editor or as a calculated DAX column, but for the purpose of this post, let's imagine you can't or don't want to do that.

If you try to apply conditional formatting, you have 3 options.

You can use Rules as your formatting style, but for this you need a numeric value:

So, this option doesn't really work if your field is a text field or if your measure retrieves a text value.

The second option is using Gradient as your formatting style:

Which will allow you to use a text column, but not a measure that returns a text value.

Also, you can only have 3 options for your colors (Minimum value color, Center and Maximum) and it doesn't allow you to define the color based on the text, as the only summarisations possible for a text field are "Count" or "Distinct Count". This means that the color formatting will be based on the count of your text field, not the text itself.