Table Headers Conditional Formatting

[MFelix]

Hello to all,

One question I see currently showing up on the forum is to have different header colours, but most of the times since the conditional formatting is only applied to values people tend to say it's not possible.

As refer conditional formatting cannot be applied to a column header however you there are alowed to:

Apply the same format of the column values to the header
Apply conditional formatting to the values (text and/or background)
Based on this two things we can make a workaround:

Instead of using a column with categories create a measure per each column we want to customize:

In my case I have 3 categories A, B, C that I want to have different colours
Create the following measures:

CAT A = CALCULATE(SUM(Table1[Value]);Table1[Cat] = "A")

CAT B = CALCULATE(SUM(Table1[Value]);Table1[Cat] = "B")

CAT C = CALCULATE(SUM(Table1[Value]);Table1[Cat] = "C")

Place the measures in the columns of your matrix or table
Go to the options of the table/matrix Field Formatting
Choose each one of the measures and make the backgroung and letter the colour you want

In my example A = Green, B = Yellow, C = Red and letter for all of the white
For each one click the option Apply to header:

Now add for each measure add conditional formatting for background and colour

In my example for all of them you have black text and white background

Be sure to choose diverging and both color the same

As you can see you have headers with different colours and the values of the tables are the same.

You can also use the other conditional formatting options as color scale or field value just be sure to add one line for the "normal" values that are to be in black colour.

For the Sub column the steps are exactly the same if you are refering to a table visual however if you refer to a Matrix visual since the SUB is on the Rows you just need to format the Column header with the blue colour

So although the Condittional formatting is not actually on the headers it looks like it is.

Check the attach PBIX file.

Regards,
MFelix

Table_Headers_Colours.pbix

[CarlosOrizaba]

Hi @MFelix,

This is great and similar to what I need. In my case I have a matrix and want to conditional format the row headers and not the column headers.

Using your example to my case, CAT A, B and C would remain as the default format. However the values under SUB, aa, bb and cc would need the conditional formatting.

How can I do this? I tried changing the theme using json, my code is good but I don't know how to apply if statements so each row is formatted accordingly (i.e. aa = green, bb, yellow, cc, orange).

Thanks,

Carlos

[MFelix]

Hi @CarlosOrizaba ,

That formatting is not possible has we are doing it with the header of the columns since it's a functionality of the system.

A workaround can be the following:

• Create a measure for calculating the SUB

Sub_Category_Measure = SELECTEDVALUE(Table1[Sub])

• Add this measure before the the other 3 measures
  • This will give you a column on your matrix with the same values as the rows headers
• Decrease the size of the column Sub Category
• Create a measure for the condittional formatting

Background = SWITCH([Sub_Category_Measure]; "aa";"#5DFF0E";"bb";"#E4FF09";"cc";"#FF6E00")

Font text = SWITCH([Sub_Category_Measure] ; "DifferentTextFromSUB";"#000000";"#FFFFFF")

I'm assuming that you also wnat to change the font colour to other one, writing a code that is different from any value in the sub will give the possibility to have 2 colours if you want one per category adjust the one for background with other colors.

• Apply the condittional formatting to the Sub_Category_Measure

On the image above I didn't hide the column for you to visualize the final result of the measure created vs the row headers.

Check PBIX file attach on a wetransfer link (will be deleted after 7 days).

As refered this is a workaround and may need adjustments if you have parent and child category.

Regards,

Miguel Félix

[CarlosOrizaba]

This is great! I still have some work ahead but I think this will resolve my issue.

Thanks so much!

[rokacl]

Very nice solution, I was going crazy about this.

Thanks dude