Forum Discussion

ThimmaiahS's avatar
ThimmaiahS
New Member
10 months ago
Solved

Dynamical conditional formatting for multiple columns

Hello Community,

 

I have 

 

Is there any possible way to apply conditional formatting for multiple columns with dynamic dax.

 

suppose if columns contains values like:

A1, then fill the Green colour as background,

B1,then full the blue colour as background,

.......... I1, then fill the yellow colour as background.

 

Regards,

ThimmaiahS

  • Praful_Potphode's avatar
    Praful_Potphode
    10 months ago

    Hi ThimmaiahS ,

    if you have 365 columns for 365 days then its advisable to unpivot the data in Power Query and then use it for filtering and conditional formatting.

    if you still want to keep all columns intact then you can write switch statement for managing the conditional formatting.something like below.

    Dynamic Background Color = 
VAR CurrentValue = SELECTEDVALUE( 'YourTable'[YourValueColumn] )

-- Note: If your values are aggregations (like count/sum), use that measure instead 
-- e.g., VAR CurrentValue = [Total Count]

RETURN
    SWITCH( TRUE(),
        CurrentValue = "A1", "#00FF00",  -- Green
        CurrentValue = "B1", "#0000FF",  -- Blue
        CurrentValue = "I1", "#FFFF00",  -- Yellow
        "#FFFFFF"                        -- Default White
    )

     

    Please give kudos or mark it as solution once confirmed.

    Thanks and Regards,

    Praful

     

     

7 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    10 months ago

    sure sir you can apply dynamic conditional formatting across multiple columns in a Power BI table/matrix using “Format by: Field value” with a DAX measure (or with Field Parameters if you want the column to switch dynamically).

    you can try give below step and apply them:

    If your table has columns like A,B,C and each cell contains values like A1, B1 … I1, you can create one measure per column returning a color and then set conditional formatting to “Format by > Field value > pick that measure”.

     

     

    • A1= Green background #00B050 
    • B1= Blue background #0070C
    • H1 / I1→ Yellow background #FFD966

       

       

    • ThimmaiahS's avatar
      ThimmaiahS
      New Member
      10 months ago

      Hello Sir,

      Thanks for the solution,
      we have many columns example we are taking calendar days 365 days and cannot have 365 measures, so we need one measure which can work for all the columns.

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 months ago

        Hi ThimmaiahS ,

        Thanks for the follow up.

        As 123abc already pointed out, Power BI applies background-color formatting at the field level, so a single DAX measure cannot automatically drive color across many separate physical columns. The “Format by -> Field value” option always expects one measure or one field per formatted column, which is why your model structure becomes key.

         

        If your 365 day fields exist as separate columns in the table, Power BI does not support a method for one DAX expression to detect which column is being formatted and return column-specific colors.

        For more details, pease visit: Apply Conditional Table Formatting in Power BI - Power BI | Microsoft Learn

         

        Most solutions involve reshaping the data so that day values are unpivoted into rows rather than maintained as separate columns. Once the data is in a normalized form (one date-or-day field, one value field), you can apply a single color measure across the visual.

        A similar recommendation and discussion is found here: Solved: How to apply the same conditional formatting on mu... - Microsoft Fabric Community

         

        If keeping all columns is unavoidable, the remaining options are per-column “rules” formatting or individual color measures per column. There is currently no built-in “apply same conditional format to multiple columns” feature in Power BI.

        Also an idea related to this already exists: Conditional Formatting - Microsoft Fabric Community

        Please upvote the idea if this matches your requirement.

         

        If you are able to share a small example of your table structure showing how day values are stored, the community could help suggest whether reshaping (unpivoting) might allow you to use a single measure instead of many.

        How to provide sample data in the Power BI Forum - Microsoft Fabric Community

         

        Hope this helps. Please reach out for further assistance.

        Thank you.