Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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.
@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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |