- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Conditional formatting background color with column containing both text and numeric values
i have a matrix visual which displays info about a employees skills
* Java
* C#
* Customer Service
* Punctuality
* Technical strength
the above are ranked from 1-3 with 1 being bad (red color) to 3 being good (green)
however some of the values doesnt apply to every employee and in that case we put n/a (technical team need not respond to customer)
how do i conditionally format the background color to display the values as specified , im facing difficulty as n/a is not a number DT and only count summarisation is available when i make te column as text DT .
Sample Data
SKill | Marc | Jake | Steve |
Java | 2 | 3 | n/a |
C# | 2 | 1 | n/a |
Customer Service | n/a | n/a | 3 |
Punctuality | 1 | 3 | 3 |
Technical Sttrength | 2 | 2 | n/a |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You could create a separate relationship table which gives values like N/A a numeric value (see below). Create the relationship, then use the Colour Label as your conditional formatting 'rule'. This should allow you to change background colours (For example, set colour Blue if Colour Label > 3). (Accept as solution if this works).
Rank / N/A | Colour Label |
1 | 1 |
2 | 2 |
3 | 3 |
N/A | 4 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hey can you provide me with the measure we can use to assign values and put those values in a new column as you have suggested above. Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

No measure needed.
Step 1. Unpivot the Sample Data so that you can create a 'Rank' column that will have your assigned number rating.
Step 2. Copy table above / create / insert a table that follows the columns I have used above.
Step 3. Create a relationship between newly created table and then your Sample Data table using 'Rank' as a link.
Step 4. On your Matrix visual, go to the conditional formatting area and create a Rule based on the 'Colour Label' field from the new table you created. For example If Colour Label value = 1 (Green), If Colour Label value > 3 (Blue), If Colour Label value = 3 (Red).
Should all be working. No measure needed, just a new table to make this relationship between 'N/A' and a numeric value.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Is there some formula i can use to create a measure such that i can hold those values in the table itself and after it is unpivoted . i have seen a question in the community on using switch but not sure on the implementation . do you have an idea in this regard ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Result of the above suggestion.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

If you wanted to keep the N/A too you could just use another column which has N/A value still in it but still link through the Rank column.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Well this is a new perspective and thanks for giving me an idea this way .
However i have created a new column with the formula
colorvalue = switch("Column name ","n/a",6,"4",4,.......) this created a new col in the table and i have formatted using this newly created column.
Thanks
Jagadeesh

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
76 | |
72 | |
56 | |
38 | |
35 |
User | Count |
---|---|
83 | |
68 | |
59 | |
46 | |
45 |