Forum Discussion
cell formatting in matrix based on row/column - error assigning measure to conditional formatting
Hi jstanley1017 ,
Please refer to the following steps.The measure is as follows.
color = var row_selection = (SELECTEDVALUE(Tiering[Tier],1)) VAR column_selection = SELECTEDVALUE(Res[Resiliency],1) RETURN SWITCH(TRUE(), column_selection IN {"AA","RR"},"Green", column_selection="AS",IF(row_selection IN{"Tier 1","Tier 2"},"Yellow","#CCFF66"), column_selection="AP",IF(row_selection IN{"Tier 1","Tier 2","Tier 3"},"Yellow","#CCFF66"), column_selection="ND",IF(row_selection IN {"Tier 1","Tier 2","Tier 3", "Tier 4" },"Red", IF(row_selection = "Tier 5","Gray","#FFFFFF") ), "#FFFFFF" )Use the measure as the background formatting for the matrix values. And the final result is as follows.
Please see the attached pbix for reference.
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello jstanley1017 .
I Made some changes to your formula, please check If it is working properly.
Future notes:
- Directly compare the text values like "Tier 1", "Tier 2", etc. instead of extracting the last character.
- Switch function makes the logic more readable and efficient for the future.
color =
VAR row_selection = SELECTEDVALUE(Tiering[Tier])
VAR column_selection = SELECTEDVALUE(Res[Resiliency])
RETURN
SWITCH(
TRUE(),
AND(row_selection = "Tier 1", column_selection = "AA"), "Green",
AND(row_selection = "Tier 2", column_selection = "AA"), "Green",
AND(row_selection = "Tier 3", column_selection = "AA"), "Green",
AND(row_selection = "Tier 4", column_selection = "AA"), "Green",
AND(row_selection = "Tier 5", column_selection = "AA"), "Green",
AND(row_selection = "N/A - No Tier", column_selection = "AA"), "Green",
AND(row_selection = "Tier 1", column_selection = "RR"), "Green",
AND(row_selection = "Tier 2", column_selection = "RR"), "Green",
AND(row_selection = "Tier 3", column_selection = "RR"), "Green",
AND(row_selection = "Tier 4", column_selection = "RR"), "Green",
AND(row_selection = "Tier 5", column_selection = "RR"), "Green",
AND(row_selection = "N/A - No Tier", column_selection = "RR"), "Green",
AND(row_selection = "Tier 1", column_selection = "AS"), "Yellow",
AND(row_selection = "Tier 2", column_selection = "AS"), "Yellow",
AND(row_selection = "Tier 3", column_selection = "AS"), "#CCFF66",
AND(row_selection = "Tier 4", column_selection = "AS"), "#CCFF66",
AND(row_selection = "Tier 5", column_selection = "AS"), "#CCFF66",
AND(row_selection = "N/A - No Tier", column_selection = "AS"), "#CCFF66",
AND(row_selection = "Tier 1", column_selection = "AP"), "Yellow",
AND(row_selection = "Tier 2", column_selection = "AP"), "Yellow",
AND(row_selection = "Tier 3", column_selection = "AP"), "Yellow",
AND(row_selection = "Tier 4", column_selection = "AP"), "#CCFF66",
AND(row_selection = "Tier 5", column_selection = "AP"), "#CCFF66",
AND(row_selection = "N/A - No Tier", column_selection = "AP"), "#CCFF66",
AND(row_selection = "Tier 1", column_selection = "ND"), "Red",
AND(row_selection = "Tier 2", column_selection = "ND"), "Red",
AND(row_selection = "Tier 3", column_selection = "ND"), "Red",
AND(row_selection = "Tier 4", column_selection = "ND"), "Red",
AND(row_selection = "Tier 5", column_selection = "ND"), "Gray",
AND(row_selection = "N/A - No Tier", column_selection = "ND"), "#FFFFFF",
"#FFFFFF" -- Default value
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Luis