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

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors