topic Re: Colour Conditional Formating in DAX Commands and Tips

Colour Conditional Formating

elliegri — Wed, 22 Nov 2023 11:08:00 GMT

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

Re: Colour Conditional Formating

vicky_ — Wed, 22 Nov 2023 22:12:36 GMT

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 )

Re: Colour Conditional Formating

vicky_ — Thu, 23 Nov 2023 21:44:35 GMT

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.

Re: Colour Conditional Formating

elliegri — Fri, 24 Nov 2023 09:08:36 GMT

Great - thanks so much