Colour Conditional Formating

[elliegri]

Hi all,

I am new to Power BI and I am trying to create a graph that drills down from month to days to PCN to Practice - (I am working in healthcare). I have done this however I need to match the Practice and PCN to their individual colours.

I have managed to set up data sheet and a Measure which pulls these however when there is more than one practice in the PCN, it does not return the correct PCN colour. Therefore I need to set up a Measure which explain that if more than one practice is returned, choose the colour of the practice.

So far I have this:

Data Color Admin =

lookupvalue(

    'PCN & Practice colour'[Colour],

    'PCN & Practice colour'[Practice],

    SELECTEDVALUE('BP Admin Calls'[Registered practice]),

    "#374A92")

If you can help, this will save me more hours of research!

Thank you

[vicky_ · Accepted solution]

@elliegri 

this is what i meant by formatting the values based on heirarchy with HASONEVALUE() - except, just replace Orange with the rules you need for when there's more than 1 practice.

[elliegri]

Great - thanks so much

[vicky_]

As you've noticed, the issue arises when there's more than 1 practice - and this is because the SELECTEDVALUE() function returns a blank in that case, so the lookup fails. One way around this is by wrapping your lookup in HASONEVALUE() - i.e:

Data Colour Admin = IF(HASONEVALUE(Table[Practice]), 
    // simply look up the colour..., 
    // else, your fallback logic here
)