How to apply the same conditional formatting on multiple columns?

[aulona12]

Hello all, 

Is there a way to apply the same conditional formatting, in this case red colored font for all values that are less then zero, for multiple columns at a time? The method that i am using at the moment is selecting the drop down arrow on every single column, choosing conditional formatting, then font color, then : 

There are 50+ columns that need the same conditional formatting as described above and it is taking way too much time. 

Thanks in advance for any help. 

[speedramps · Accepted solution]

Please consider this solution and remember I am an unpaid Power BI volunteer, so click the thumbs up button if you like me helping you and the solved button if I fix your problem.

Click here for an example solution 

This example has the targets in table but you could hard code them in a switch statement.
A table is better because the users can edit the targets without a PBI developer changing DAX. 

Create a dax measure like this

KPI Colour =

VAR mytarget = SELECTEDVALUE(Facts[Target])

RETURN

IF(SUM(Facts[Value])> mytarget, "Red","Green")

Then in conditioinal formating select
Format Style = Field Value

What field should we base this on = KPI Colour

View solution in original post

[KellyLancaster]

Hi This helps, but do I still have to apply conditional formatting to EACH individual column?

I'm trying to apply a background colour to 100s of columns where the percentage is greater than 100%
Thanks

[klinn]

This has been requested years ago in Fabric Ideas but hasn't gained much traction. Hope it's ok if I plug it here in case others are having trouble with this and want to draw some attention so the devs can put it on higher priority:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=d4c8e896-897c-ed11-a76e-281878bd0b04

[Anonymous]

@KellyLancaster - I have the same problem.  I was reading this...

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

and half way down there's a a section called 'Color based on a calculation' (see screen shot below). 

While this is not the complete solution, I'm going to try and do something in power query to do my formatting for multiple columns - I only have 8 or so, but it's still winding me up!!!

I'm hoping that at best it will be one statement, and at worst it will be 8 x copy and paste which will go lots quicker than opening the dialogue and clicking.  

I'll report back... and in the mean time, if there are any Power query wizards out there that know how to do this, or know that I'll be wasting my time, please let me know 🙂

[Anonymous]

OK - to answer my own question - I can't find a way of doing formatting in Power Query M without having to use the conditional formatting dialogue as well.

Creating a format color column as described above does make things quicker.  It reduces the amount of clicks in the diaglogue and if your rules change, you only need to change the rules once in the power query.

But a plea to Microsoft - please build something that makes applying formatting across multiples columns quicker.

[dproso]

I can not state how much i apreciate your contribution. I wasted hours figuring this out. The docmentation is ok but without an example i could not figure it out. Your example saved me. Chat GPT was worthless also.

[speedramps]

Thank you. Glad you aprecaite the effort I put into creating an example solution