Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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])

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.