Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have several measures, each using a different query, that calculate various KPIs, such as the proportion of requests that were dealt within a specified deadline (they're all %).
All those have a colour coded rating, for instance if fewer than 70% of applications are dealt with within the deadline, it's black, if it's between between 70% and 96% it's red, etc. The rating thresholds are different for each measure.
I'm using a 'switch' measure to apply the conditional formatting. Here's one of the CF measures:
However, there is a good likelihood that those rating thresholds will be reviewed and changed in the future, so my next step is to try and find a way to bring those CF thresholds from another query rather than type them in the measure, a query coming from a dataset saved somehwere that can be easily accessed (for example from a spreadsheet).
And now I'm stuck. I can't use 'related' because these are measures. Any suggestions on what to do next?
Thank you
Solved! Go to Solution.
Hi @amyWMFS ,
The option you have for this is to create a table witht the settings you need something similar to this:
ID | Min | Max | Condittional Formatting |
1 | 0 | 0,7 | #000000 |
2 | 0.7 | 0,96 | #D64550 |
3 | 0,96 | 0,99 | #E66C37 |
4 | 0,99 | 100 | #21BF75 |
Now create the following measure:
Condittional formatting =
VAR temp_table =
FILTER (
ALL ( Formatting ),
Formatting[Min] <= [% completed]
&& Formatting[Max] >= [% completed]
)
RETURN
MINX ( temp_table, Formatting[Condittional Formatting] )
Check PBIX file attach. You can make the table connected to an excel file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @amyWMFS ,
The option you have for this is to create a table witht the settings you need something similar to this:
ID | Min | Max | Condittional Formatting |
1 | 0 | 0,7 | #000000 |
2 | 0.7 | 0,96 | #D64550 |
3 | 0,96 | 0,99 | #E66C37 |
4 | 0,99 | 100 | #21BF75 |
Now create the following measure:
Condittional formatting =
VAR temp_table =
FILTER (
ALL ( Formatting ),
Formatting[Min] <= [% completed]
&& Formatting[Max] >= [% completed]
)
RETURN
MINX ( temp_table, Formatting[Condittional Formatting] )
Check PBIX file attach. You can make the table connected to an excel file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks Miguel, it works perfectly.
I modified it so that I can have all the different min and max for the various indicators in one table: I have a column in the table that identifies which indicator each row is for, and I added another condition for this in the 'filter' part, and so far it seems to work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |