Power BI Conditional formatting using Measures

⚡⚡⚡More information in the description below⚡⚡⚡
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic...
⏬Download a copy of my demo file
aasolutions.sh...
🖌️Power Toys (Colour Picker)
learn.microsof...
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@BrianHurn]

This is a great explanation of the idea. Here are some bonus tips: 1. Power BI supports 8-character RGBA (Red Green Blue Alpha) hex codes, so you can append a 00 to the end of the hex code to make formatted items transparent, FF to make them fully opaque (the default if omitted), or any value in between. This is a great way to make things appear, disappear, or fade based on any condition you can code in a measure. 2. You can add a color column to a dimension table to associate colors with a selected item by creating a measure that uses SELECTEDVALUE on that color column. I do this with brand colors sampled from the web, and it's a highly efficient way to color code a column chart (with brand on the x-axis) or a card value based on the associated company.

[@AccessAnalytic]

Nice tips Brian. Thanks for the inspiration.

[@lape36]

Hi Brian Very interesting tips. I'm quit interested in your bonus tip #2, can you elaborate on the SELECTEDVALUE measure and where to apply this when using legend in a stacked bar chart? Possible link to a tutorial?

[@CasperSeve]

Have the same challenge as @lape36 - How do you do this on a stacked column bar chart for each series?

[@BrianHurn]

@@CasperSeve @lape36 You can use a measure defined as SELECTEDVALUE(dim[Color]) (with your actual table and color value column names) wherever color conditional formatting is available. I too find it frustrating that many visuals don't support this capability yet.

[@CasperSeve]

@@BrianHurn Hmm - did not work. I don't even see an ability to select conditional formatting by fx on color on the columns when I have a legend applied.

[@robrayborn1349]

Bloody brilliant! AND, I'm happy to know that I'm not the only one that thinks the imbedded Conditional Formatting in Power BI is a pain. I will keep this video in my favourites and reference it often. Thank you!

[@AccessAnalytic]

You’re very welcome. I appreciate you taking the time to let me know you found it useful

[@zzota]

I've used single measures for conditional formatting, but your combined measure if far superior! Thanks Wyn.

[@AccessAnalytic]

You’re welcome

[@WendyLockhart-h6w]

I was struggling with a homework assignment for conditional formatting using measures and I had forgotten the hashtags in the hexcodes!! Thank you for fixing this for me!!!

[@AccessAnalytic]

Glad to help 😀

[@huseyinburaktasci]

That's a great solution I have been looking for. I was searching for how to demonstrate the actual and budget comparison for the periods past so far. Thanks to your content, I will create a parameter that will highlight past periods with different colors. Regards. :)

[@AccessAnalytic]

Glad to help. I appreciate you taking the time to let me know you found it useful

[@lape36]

Hi Wyn Thanks for a good explanation. Can you provide instruction on potential similar method when using a stacked bar chart where each "series" defined by the legend need to have a specific color? I'm looking for a method where lines in line chart, pies in pie char and bar in bar chart used the same specific colors for specific "series" defined in the legend throughout the report.

[@AccessAnalytic]

Check out Brian’s pinned comment in this comments section

[@krishnaRaog21]

I am a big fan of yours ...Like your tutorials

[@AccessAnalytic]

Great to hear. Glad you enjoy them 🙏🏼

[@JuanIArana]

Great explanation Wyn !!!

[@AccessAnalytic]

Thank you

[@unnikrishnansanthosh]

super clear explanation, one for my next db

[@AccessAnalytic]

Glad to help

[@workstuff5253]

I'm glad you called out the Rules based formatting as being utter garbage.

[@martyc5674]

It is pure Junk! - they really need to improve it, I can never get my head around it!!

[@AccessAnalytic]

I don’t see it happening sadly

[@marlongardley]

Awesome explanation

[@AccessAnalytic]

Cheers

[@katerynapopovych1637]

Hi and thanks for a great tutorial! What is the correct way to add more number intervals and thus colors to this measure?

[@AccessAnalytic]

You would edit the formula to add more conditions at 2:17

[@kensimpson6659]

Great tool! I'm using this to highlight errors vs threshold. In my application, if errors exceed threshold, then highlighted red (out of spec). Some errors do not have thresholds, so these should not be highlighted. What can I add so these fields are not formatted?

[@AccessAnalytic]

Maybe some sort of early if statement to check if error should be evaluated. A helper column with a y/n flag might help with this.

[@kensimpson6659]

@@AccessAnalytic Added the statement IF(NOT(ISBLANK([MEASURE])) at the beginning and it worked flawlessly.

[@AccessAnalytic]

@@kensimpson6659 Great, glad you got it working

[@bhaskaraggarwal8971]

Awesome✨. Thanks for sharing. Just one question - I need to format 15 measures with the same ranges. Is it possible to create one dax cf-measure and use it for formatting multiple measures or I have to create 15 cf -measures?

[@AccessAnalytic]

Not that I’m aware of unless you write a script to do it in Tabular editor ( external tool )

[@bhaskaraggarwal8971]

Thank you. Appreciate it!

[@AccessAnalytic]

@bhaskaraggarwal8971 look up Sue Bayes on LinkedIn - I think she said she wrote a script to do this just the other week

[@bhaskaraggarwal8971]

@@AccessAnalytic - Sure. I will reach out to her. That's so nice of you to help

[@lloydstoner5872]

Great tip. I'm new to PowerBi and this was super simple to follow. One question: Given I have a lot of switch conditions (due to a lot of colours) - is there a way to get the measure to return a colour based on lookup from a table ?

[@AccessAnalytic]

Do you mean looking up the colour for Good, Bad, Average etc. or a colour code listing based on the column name, or a sliding scale, so like a 0-100 RED, 100-200 yellow etc

[@FranzL608]

This is a great suggestion. Really useful. I tried to create some kind of Income statement and colour the subtotals. But it does not work. I have the cf measure, I have the value measure (and if I put both in the value section the cf colour value is displayed as expected) but when entering the cf measure as background colour function ... nothing happens. Any idea why?

[@FranzL608]

Sorry, I think I found the issue: multiple rows. I have the following cf measure: cf=switch( max([header]), "Net Sales", "#b1f1ea", ...) It works if I have only one header in the row area. If I expend the row area for a subheader, the cf does not longer work. How can I force PBI to ignore any additional fields in the row area? And, is it possible to conditionally format the rows as well?

[@AccessAnalytic]

Good questions but I don’t know the answers sorry.

[@wojtektopiko5677]

Thanks! Can you suggest a way to create a conditional formatting measure / rule without having to refer to a specific measure?

[@AccessAnalytic]

What would the rule / use case be?

[@wojtektopiko5677]

​ @AccessAnalytic Sorry, I am just starting with DAX and might be not precise in formuling the questions. I meant the cf without fixed measure it's reffering too. For example replacing definied measure in VAR _Measure = [Usage v Prior Year] with something like SELECTEDMEASURE. I could create one universal conditional formatting measure, instead of multiple ones for each of the calculation measures.

[@AccessAnalytic]

@wojtektopiko5677 not that I know of

[@wojtektopiko5677]

@@AccessAnalytic thanks a lot :-)

[@bloodomen6919]

Ty Sir ! I have tried to replicate it on my end and i cant select the Cf measure as a conditional formatting independent of the visual i use. the measure is not grayed out , but when i select it nothing happens and i can't select it. any recommendations ?

[@AccessAnalytic]

Not sure sorry, maybe post a screenshot to www.reddit.com/r/PowerBI/

[@bloodomen6919]

@@AccessAnalytic i think the issue for me is that i am using calculation items where i want to apply the cf. Do you have any ideea how can we adapt it ? Please ? ty once again for everything you do for the community.

[@AccessAnalytic]

Sorry I don’t understand still

[@bloodomen6919]

I am using a matrix and calculation group item as values. When i am trying to select the measure and to use it as field i can't select the measure that applies the CF. did i explained it better ?

[@AccessAnalytic]

It’s not something I have experience of sorry

[@reanalytics1863]

I have this measure that returns text such as 50/12% BAC+ = IF([# BAC+]>0, FORMAT([# BAC+],"0") & "/" & FORMAT([% BAC+],"0%"),"") I want to conditionally format this measure using the following measure Met Bac+ target = IF( [bac+ target]

[@AccessAnalytic]

I’m not quite following by maybe you need to read up on dynamic format strings? learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

[@muhammadsadiq8885]

Nice

[@AccessAnalytic]

Ta