cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamMiddleton
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. 

SamMiddleton_2-1662104962514.png

 

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 

SamMiddleton_3-1662104982742.png

all fields are set to the same conditional format setting

 

SamMiddleton_4-1662105083200.png

 

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

 

 

 

3 REPLIES 3
SamMiddleton
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. 

SamMiddleton_1-1662121040156.png

 

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

 

SamMiddleton_2-1662121116559.png

 

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 

SamMiddleton_3-1662121303659.png

 

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.

 

SamMiddleton_4-1662121537214.png

 

 

amitchandak
Super User
Super User

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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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.