Forum Discussion
Dynamical conditional formatting for multiple columns
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
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
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.
Hi ThimmaiahS ,
Just wanted to check whether the response provided was helpful. Also, have you had a chance to review the idea included above. If it aligns with your requirement, kindly upvote it so that it gains more visibility and may be considered for future implementation.
If you need any further assistance, please reach out and consider sharing sample data so that we can support you better.
Thank you.