Looking up rows related to a measure

[amyWMFS]

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:

CF Applications = switch(

true(),

[% Deadline met] < 0.7,"#000000",

and( [% Deadline met]>= 0.7, [% Deadline met]<0.96),"#D64550",

and([% Deadline met]>= 0.96, [% Deadline met]<0.99),"#E66C37",

[% Deadline met]>= 0.99,"#21BF75")

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

[MFelix · Accepted 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.

[amyWMFS]

Thanks 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.