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