Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Conditional formatting on matrix breaks when table filtered by date

Hi, I have a matrix table of store performance rankings over each fiscal year/week.  Stores are ranked on their performance each week, within a SQL query and that query is passed in to the data model and joined on to my date dimension.  I have created a measure for each ranking using the MAX function eg max(SER). I've placed these measures into a matrix and changed the cell element background to green for the highest, red for the lowest number.  The measures are formatted as whole numbers within the data model and when the matrix is unfiltered, the colours return correctly. 



However, when i limit the matrix in the filter pane via the date field in the date dimension to the relative date of the last 90 days, the conditional formatting somehow breaks and the numbers seem to randomly reformat. It's not a complete reverse of the conditional formatting as you can see there's a random 1 in there on the returns line that's still green 


all fields are set to the same conditional format setting




why would the addition of a date field break the format? 




Frequent Visitor

Hi @amitchandak 

the ranking is done in a SQL statment prior to the power bi report and the data is passed as a table into the PBI model with the rankings already complete.  However, due to the necessity for the ranks to be respond to filters by date and store, i created measures for all the ranks by simply writing the measure eg  MAX(SER) for each value. When I place a date filter on the matrix, this is when the conditional formatting breaks.   YOu can see when i clear the filter pane, the table highlighted in red responds appropriately where  lowest numbers are green and highest are red. 



When i apply a date filter to the visual, it breaks the conditional formatting. 




on one of the separate tables in this report you can see that it's acting appropriately and conditionally formatting even  with the date filter on 



just for clarity, every row measure (ser/sir/returns/giftcard/OIS/Add In/ATV/ATQ) in all these tables is set to MAX in the model.   The 4 tables in this report come from two separate sql data tables, so the top two tables come from sql statement 1 and the bottom two tables come from sql statement 2.





Super User
Super User

@SamMiddleton , In Rank, are using ALL. Like All(Table[Column]) . Then you should try to use allselected(Table[Column])

hi @amitchandak  apologies, replied to the original thread, please see my update above

Helpful resources

November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.