Forum Discussion
Conditional formatting based on slicer range selection
Hi all,
In a matrix, I need to show a different color for the values in a conditional column "Success%" based on a threshold selected by a slicer (using a range 'between'), while still keeping all the values in the matrix shown (for this I can use the 'edit interaction' feature of the slicer). I can set the rules based on field "success%" but I need to be able to set it to a dynamic field which depends on the filter instead. Any idea?
Example table:
| Name | Success% |
A | 1 |
| B | 0.9 |
| C | 0.6 |
| D | 0.5 |
i.e.: if threshold is set to 90% I need the values >= 90% in green and anything below in red.
Thanks,
Make a duplicate of the table ('Color Succes%) , put the duplicat in the slicer and apply the following measure in the format of the matrix (Conditional Formatting / Succes% in the text box / Background color / advanced control / Field Value.
ColorSuccesMeasure = VAR __MaxValueVisible = MAX('Color Succes%'[Succes%]) RETURN IF(MAX('Table'[Succes%]) > __MaxValueVisible, "#01B8AA", "#FD625E")Hi, Anonymous
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Slicer(a calculated table):
Slicer = GENERATESERIES( 0, 1, 0.01 )There is no relationship between two tables. You may create a measure as below.
Color Control = IF( SUM('Table'[Success%])<MAX(Slicer[Value]), "red", "green" )Conditional formatting:
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks everyone! All good solutions. I ended up using a mix of the codes that you provided, as the matrix (which I forgot to mention that I was using 🙏) reacted a bit differently to the measures. My final setup:
Percent Table (I needed some more accuracy hence the 0.0001):
Percent = GENERATESERIES(0 , 1 , 0.0001)Threshold Measure:Succ_Threshold =var Threshold = MAX('Percent'[Success% Threshold])var Succ = SUMX(Table1,Table1[Success %])returnIF(Threshold > Succ, "#FF3154", "#4BDD33")Then I applied the measure to the Conditional Formatting option Based on Field as suggested.Thanks!Thanks!
3 Replies
Make a duplicate of the table ('Color Succes%) , put the duplicat in the slicer and apply the following measure in the format of the matrix (Conditional Formatting / Succes% in the text box / Background color / advanced control / Field Value.
ColorSuccesMeasure = VAR __MaxValueVisible = MAX('Color Succes%'[Succes%]) RETURN IF(MAX('Table'[Succes%]) > __MaxValueVisible, "#01B8AA", "#FD625E")Hi, Anonymous
Based on your description, i created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Slicer(a calculated table):
Slicer = GENERATESERIES( 0, 1, 0.01 )There is no relationship between two tables. You may create a measure as below.
Color Control = IF( SUM('Table'[Success%])<MAX(Slicer[Value]), "red", "green" )Conditional formatting:
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks everyone! All good solutions. I ended up using a mix of the codes that you provided, as the matrix (which I forgot to mention that I was using 🙏) reacted a bit differently to the measures. My final setup:
Percent Table (I needed some more accuracy hence the 0.0001):
Percent = GENERATESERIES(0 , 1 , 0.0001)Threshold Measure:Succ_Threshold =var Threshold = MAX('Percent'[Success% Threshold])var Succ = SUMX(Table1,Table1[Success %])returnIF(Threshold > Succ, "#FF3154", "#4BDD33")Then I applied the measure to the Conditional Formatting option Based on Field as suggested.Thanks!Thanks!