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