Forum Discussion

elliegri's avatar
elliegri
Regular Visitor
2 years ago
Solved

Colour Conditional Formating

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_'s avatar
    vicky_
    2 years ago

    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.

     

3 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    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
)
  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    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.