Matrix Visual - Alternating Row /Background Color

[jrieman]

Hello, I have two fields, 'user name', and 'category', as Rows in a matrix visualization.  The rest of the report is comprised of multiple fields, some text, some numeric, loaded as Values.   Users require that 'user name' and 'category' remain visible as users scroll to the right to view the report's values, much as they would in Excel.  

Another user requirement is to alternate background color to enhance readibility of rows.  

I froze 'user name' and 'category', so that when users scroll to the right, they're able view the values laid out as columns 'user name' and 'category' remain visible. 

What I need help with --> get Row 'user name'., which  appears as a column of data in the report itself, to alternate background color much as the rest of the report does.  'category' alternates background color as do the Values columns.  

When I collapse 'categoryy' into 'user name', then the formatting does apply but that's not an option.

  Below:  how to get 'user name''s background color to alternate in sync with 'category' and Values columns.

[marcelsmaglhaes · Accepted solution]

Hey   @jrieman 

There isn't a direct way in Power BI to achieve alternating background colors for specific rows like "user name" while other rows ("category" and Values) follow a different formatting rule. However, we can achieve a similar effect using a combination of conditional formatting and a calculated column.

Here's how you can accomplish this:

1. Create a Calculated Column:

1. Go to the Modeling tab.
2. In the Fields pane, click New Column.
3. Name the new column something descriptive like "RowType."
4. In the formula bar, enter the following DAX formula:

RowType = VAR CurrentRow = SELECTEDVALUE('Table'[user name]) RETURN IF(ISBLANK(CurrentRow), "Category", "User Name")

This formula checks if the current row is a "user name" by looking for a value in the user name column. If a value exists, it returns "User Name." Otherwise, it returns "Category."

2. Apply Conditional Formatting:

1. Go back to the Report tab.
2. Click on your matrix to activate it.
3. In the Formatting pane, expand Rows.
4. Click on Background color.
5. Choose Conditional formatting.

6. Set Up Formatting Rules:

Here, we will set two formatting rules:

• Rule 1 (For "Category" and Values):

  • Set the Field value to be your existing formatting rule for alternating background colors (likely based on some other condition).
  • Leave the Background color section untouched (it will use your existing formatting rule).

• Rule 2 (For "User Name"):

  • Set the Field value to be equal to "User Name" (the value returned by your calculated column when the row is a "user name").
  • Choose a different background color for this rule to visually distinguish the "user name" row.

This approach should give you the desired result of alternating background colors for "user name" rows while maintaining the formatting for other rows in your matrix.