Forum Discussion
cell formatting in matrix based on row/column - error assigning measure to conditional formatting
I would like to assign conditional formatting to a matrix based on row/column values.
I have created a measure called color to use for this conditional formatting.
Since I did copy the concept from another I found, I probably missed something important here. Any help is appreciated.
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.
4 Replies
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- lbendlin
Super User
You are doing integer conversions on text values.
Your logic can also be simplified by using validity ranges rather than specifying every single combination.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 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.Thanks Luis!
