Forum Discussion
Row by row Conditional Formatting on a Matrix
Dear all, I have a matrix formating issue. I want to conditionally format my matirx row by row to show the monthly highest and smallest values for the years and months selected. The result sh...
Hi lbendlin ! The code doesn't run properly when you select more than one year in a slicer. The reason is "color" measure isn't between 0 and 1. How could it be fixed?
Some days ago I created a similar question here:
Thank you so much for your time!
I refactored the code. Seems to work better now.
color =
SWITCH (
TRUE (),
ISINSCOPE ( 'Date Table'[Day Number] ),
VAR a =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Date Table'[Day Number] ),
REMOVEFILTERS ( 'Date Table'[Day Number] )
),
"sd", [sum data]
)
RETURN
DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) ),
ISINSCOPE ( 'Date Table'[Month] ),
VAR a =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Date Table'[Month], 'Date Table'[Month Number] ),
REMOVEFILTERS ( 'Date Table'[Month], 'Date Table'[Month Number] )
),
"sd", [sum data]
)
RETURN
DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) ),
ISINSCOPE ( 'Date Table'[Year] ),
VAR a =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Date Table'[Year] ),
REMOVEFILTERS ( 'Date Table'[Year] )
),
"sd", [sum data]
)
RETURN
DIVIDE ( [sum data] - MINX ( a, [sd] ), MAXX ( a, [sd] ) - MINX ( a, [sd] ) )
)
That's perfect! Thank you so much for your time.
Hi lbendlin! I just realized if I sort year column by other one, the code doesn't work properly.
I want to sort years inversely as you can see in the next image:
How can I solve this? I doesn't understand why doesn't run. Thank you so much.You need to include the sort column in the formula, like you do for the month.
thank you so much!!!😁