Conditional Formatting on Matrix Row Header

[parameshulthi]

Hi All,

I'm looking to format Matrix row header with conditional formatting but this is only available for Matrix Values not for Matrix Row headers. Below is the required format i'm looking for

I have built this visual with Matrix visual all the columns on Rows as this is kind of visual format we need and also we need to format the Category column based on the category value for example General Risk background color should be Yellow and Policies & Procedure should be in Green
This is possible if there is aoption on conditional formatting on Row header but currently conditional formatting is only possible on values like shown below

Does some has any idea how can I build this?

[Anonymous]

Hi @parameshulthi ,

Your solution is great, @MFelix . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference:

Due to the inability to dynamically change title colors in the Matrix visual, I chose to utilize a Python visual to create a similar chart programmatically.

1\My Datasource 

2\Add a Python visual

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 

# dataset = pandas.DataFrame(Agenda_Topic, Category, First Organisation, Year, Quarter, Month, Day)
# dataset = dataset.drop_duplicates()

# Paste or type your script code here:
import matplotlib.pyplot as plt
import pandas as pd

# Load the dataset passed from Power BI into Python script, 'dataset' is the built-in variable in Power BI
df = dataset

# Get the first 'First Organisation' for each group
df_first_org = df.groupby('Meeting')['First Organisation'].first().reset_index()

# Add the first_organisation column
df['First Organisation'] = df['Meeting'].map(df_first_org.set_index('Meeting')['First Organisation'])

# Rearrange the column order
df = df[['Meeting', 'Category', 'Agenda_Topic', 'First Organisation']]

# Sort by column order
df = df.sort_values(by=['Meeting', 'Category', 'Agenda_Topic', 'First Organisation'])

# Set the color mapping based on the 'Category' value to set background color
color_mapping = {
    'Corporate Governance': '#FF9999',  # Red
    'General Risk': '#FFCC99',  # Orange
    'Policies & Procedures': '#99FF99'  # Green
}

# Create a color list, each row corresponding to the 'Category' column color
colors = [color_mapping.get(cat, 'white') for cat in df['Category']]

# Set figure size
fig, ax = plt.subplots(figsize=(10, len(df) * 0.4))  # Dynamically adjust height

# Hide axes
ax.axis('tight')
ax.axis('off')

# Create table
table = ax.table(cellText=df.values, 
                 colLabels=df.columns,
                 cellLoc='center',
                 loc='center')

# Add background color to the 'Category' column
for i in range(1, len(df) + 1):  # Start from 1 since row 0 is the header
    table[(i, 1)].set_facecolor(colors[i-1])

# Merge same cells: handle 'Meeting', 'Category', and 'Agenda_Topic' columns
prev_meeting = None
prev_category = None
prev_agenda_topic = None

for i in range(1, len(df) + 1):  # Start from 1 since row 0 is the header
    # Handle 'Meeting' column
    if df.iloc[i-1, 0] == prev_meeting:  # If the same as the previous one, hide the current cell content
        table[(i, 0)].get_text().set_text('')
    else:
        prev_meeting = df.iloc[i-1, 0]
        prev_category = None  # Reset category when meeting changes
        prev_agenda_topic = None  # Reset agenda topic when meeting changes
    
    # Handle 'Category' column - Only hide if both 'Meeting' and 'Category' are the same as the previous row
    if df.iloc[i-1, 1] == prev_category and df.iloc[i-1, 0] == prev_meeting:  # Same 'Meeting' and 'Category'
        table[(i, 1)].get_text().set_text('')
    else:
        prev_category = df.iloc[i-1, 1]
    
    # Handle 'Agenda_Topic' column - Only hide if 'Meeting', 'Category', and 'Agenda_Topic' are all the same
    if df.iloc[i-1, 2] == prev_agenda_topic and df.iloc[i-1, 1] == prev_category and df.iloc[i-1, 0] == prev_meeting:
        table[(i, 2)].get_text().set_text('')
    else:
        prev_agenda_topic = df.iloc[i-1, 2]

# Keep all grid lines
table.auto_set_font_size(False)
table.set_fontsize(10)
table.scale(1.2, 1.2)  # Adjust table size

plt.show()

Note: Before using Python visuals, you need to first install R and Python locally, and then install the matplotlib and pandas libraries.

R: https://cran.r-project.org/bin/windows/base/

Python: https://www.python.org/downloads/

How to set Windows environment variables for Python: https://www.youtube.com/watch?v=Y2q_b4ugPWk

How to Install numpy, pandas and matplotlib Python libraries on Windows:

Enter the following command in the command line

pip install matplotlib pandas

https://www.youtube.com/watch?v=2iswYOPEeHk

Best Reagards,

Bof

[parameshulthi]

Thanks @Anonymous 
Using Python solves the exact my requirements but every organization doesn't allow using all the Python libraries 
Good to know that this visual directly not possible with Power BI

[MFelix]

Hi @Anonymous ,

You are correct in terms of using the python visual however you need to be carefull with this since there are some limitations on the usage of this type of visuals online (namely if you use custom libraries) and also on the interactions with the other visuals, not to mention the coding part that most of the users do not master.

But again is a great option also.

[Anonymous]

Hi @MFelix ,

I truly appreciate your suggestion—it was incredibly helpful! Thanks so much for taking the time to share it with me.

Best Regards,

Bof

[MFelix]

Hi @parameshulthi ,

To my knowledge this is not possible with the default visuals, I have written a workaround on this blog post. please check if this works for you:

https://community.fabric.microsoft.com/t5/Community-Blog/Table-Headers-Conditional-Formatting/ba-p/7...

[parameshulthi]

Thanks @MFelix
This is a nice turnaround solution with applying the conditional formatting to the Sub_Category and though it doesn't exactly match my requirement, it is a great solution