Conditional formatting if measure is within measure

[Anonymous]

Hi there,

my ambition is to conditionally format a scattar chart. 

The size of the scattar/bubble chart is determined by the measure [Value]. 

Value = 
[Done Risk green] + [Done Risk yellow] + [Done Risk red] + [Done Risk unplanned]+ [Sheduled Risk green] + [Sheduled Risk yellow] + [Sheduled Risk red] + [Sheduled Risk unplanned]

As the picture bellow shows, measure [Value] is the sum of multiple [measures]. These [measures] have only one value per row and are all structured the same but search for different search variables (r/y/g).

Sheduled Risk red = 
IF(MAX('NPLM Import'[Risk status (color)]) = "r", 
    IFERROR([Ø Annual Turnover (EUR) Sheduled], 0))

Therefore, my Measure for conditional formatting of [Value] shall check in each case whether the returned 'Value' is e.g. in the column 'Done Risk red'. If so, it should return a HEX code

Basically, it is this idea:

Value Colour = 
SWITCH(TRUE(),
    [Value] = [Done Risk green], "#D7E4BD",
    [Value] = [Done Risk yellow], "#FFE8A1",
    [Value] = [Done Risk red], "#FFA1A1",
    [Value] = [Done Risk unplanned], "#D0D0D0",
    [Value] = [Sheduled Risk green], "#92D050",
    [Value] = [Sheduled Risk yellow], "#FFFF26",
    [Value] = [Sheduled Risk red], "#FF3300",
    [Value] = [Sheduled Risk unplanned], "#A6A6A6")

But that's not working, neither is Lookupvalue returning the anticipated result

Value Colour2 = 
SWITCH(TRUE(),
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk green]), "#D7E4BD", 
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk yellow]), "#FFE8A1",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk red]), "#FFA1A1",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Done Risk unplanned]), "#D0D0D0",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk green]), "#92D050",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk yellow]), "#FFFF26",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk red]), "#FF3300",
    [Value] = LOOKUPVALUE('NPLM Import'[Yearly average turnover (EUR)], 'NPLM Import'[Yearly average turnover (EUR)], [Sheduled Risk unplanned]), "#A6A6A6")

Is there a better way to do so?

[Anonymous · Accepted solution]

Apparently, it does work with a minor addition:

Value Colour = 
SWITCH(TRUE(),
    ISBLANK([Value]), BLANK(),
    [Value] = [Done Risk green], "#acdc6f",
    [Value] = [Done Risk yellow], "#ffcc5d",
    [Value] = [Done Risk red], "#ff9594",
    [Value] = [Done Risk unplanned], "#D0D0D0",
    [Value] = [Sheduled Risk green], "#619822",
    [Value] = [Sheduled Risk yellow], "#ca8d00",
    [Value] = [Sheduled Risk red], "#ac1838",
    [Value] = [Sheduled Risk unplanned], "#A6A6A6")

[Pragati11]

HI @Anonymous ,

What error are you getting in your measure "Value Colour"??

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati