Forum Discussion

aulona12's avatar
aulona12
Frequent Visitor
4 years ago
Solved

How to apply the same conditional formatting on multiple columns?

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 usi...
Tips and Tricks
  • speedramps's avatar
    speedramps
    4 years ago
     

    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

     

     
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.