Automating Column colors for each year

[bdehning]

I currently use Clustered Column Chart to show data by a field called Inception Year.  I select and set the colors for eacy year by Formating Columns and applying Series settings for each year and selecting a color for each year.  But I have to each Januay go in add a new color fo the new year.  

What do I need to do to set this column chart up so it automating creates a new color going forward for 2027, 2028 and beyond? 

[v-pnaroju-msft]

Hi bdehning,

Thank you for sharing your idea in the Ideas forum. We appreciate your contribution, and we have also upvoted the idea. Please note that Microsoft regularly reviews ideas submitted by the community, and your suggestion may be considered for future updates.

Thank you once again, and we encourage you to continue engaging with the Microsoft Fabric Community.

[v-pnaroju-msft]

Hi bdehning,

Thank you for the update. If legend based conditional colour formatting is essential for your scenario, we kindly request you to submit an idea in the Ideas forum using the link: Fabric Ideas - Microsoft Fabric Community so that the product team may evaluate it.

If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-menakakota]

Hi @bdehning ,

Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback.

Best Regards, 
Community Support Team 

[bdehning]

Yes it has been submiited.     https://community.fabric.microsoft.com/t5/Fabric-Ideas/Legend-based-conditional-colour-formatting-fo...

[v-pnaroju-msft]

Hi bdehning,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[bdehning]

No it can not be done as Microsoft removed the function i needed

[v-pnaroju-msft]

Thankyou, @amitchandak, @cengizhanarslan, @RicardoTraNa , @danextian, @Thejeswar, and @Praful_Potphode for your responses.

Hi bdehning,

Based on our understanding, when a field is placed in the Legend of a clustered column chart, Power BI automatically controls the series colours. In this configuration, the “fx” Field Value conditional formatting option is not available. 

As per our research, there is no official documentation confirming that legend-based conditional formatting for column charts is being restored. Therefore, we should consider the current behaviour as the supported design.

As noted by @danextian , conditional formatting does not apply when a Legend controls the series. Additionally, as suggested by @RicardoTraNa , the correct long term approach is to remove manual colour overrides and allow Power BI to utilise the default theme palette.

Additionally, please refer to the links below:
Apply Conditional Table Formatting in Power BI - Power BI | Microsoft Learn
Customize Power BI Visualization Titles - Power BI | Microsoft Learn
Conditional formatting in Power BI visuals - Power BI | Microsoft Learn
Tips and Tricks for Formatting in Reports - Power BI | Microsoft Learn

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[Praful_Potphode]

Hi @bdehning 

try attached PBIX.please note that i have assumed that we have only X Axis and Y Axis on CLustered column chart.If we have legend field then this apparoch may not work.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

praful

[Thejeswar]

Hi @bdehning ,

In case our clustered column chart does not use a legend to split the data then you can use a calculated column to apply conditional formatting to the fields. That way every January will have a certain color, February will have a certain color and so on.

It is a limitation that you can use conditional formatting when there is a legend in use in the visual

Regards,

[danextian]

Hi @bdehning 

It depends on how the column chart is configured. If a legend is used to split the values by year, automating it isn’t currently possible. Conditional formatting only works when no legend is applied to the column or combo chart. A workaround is to pre-assign values to future years by having them exist in the model even if your current data shows only up to the current and then use a measure that will not return blank for any of the year. Example: 

Some value = 1

[RicardoTraNa]

Hey!  You can use automatic category colors instead of setting a color per year manually.

In your clustered column chart, remove the custom series color overrides and let the visual use the default color palette (Format > Data colors > Reset to default / turn off “Show all”). Then each new Inception Year (2027, 2028, etc.) will automatically get the next color with no yearly maintenance.

If later you need controlled logic, you can switch to conditional formatting with a DAX color measure ... but first step is to stop hardcoding colors per year.

Best Regards! 

[cengizhanarslan]

Power BI does not support dynamic “auto-assign new color per new category” logic for specific values. The easiest approach would be instead of manually assigning colors, let DAX control the color.

Step 1) Create a color measure

Year Color =
VAR Yr = SELECTEDVALUE('Table'[Inception Year])
RETURN
SWITCH(
    TRUE(),
    Yr = 2024, "#4472C4",
    Yr = 2025, "#ED7D31",
    Yr = 2026, "#70AD47",
    "#A5A5A5"   -- default color for future years
)

Step 2) Apply to visual

• Format → Data colors → fx

• Format by: Field value

• Based on field: Year Color

[amitchandak]

@bdehning , If you are not using legend or two measures, you can use field - conditional formatting. You can create a measure like below and use in coditional formatting using field value option 

COlor = Switch(True() ,

max(Table[Inception Year])  = Year(Today()) , "Blue", 

max(Table[Inception Year])  = Year(Today()) -1 , "Green", 

"Red"

) 

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[bdehning]

It seems from my research that the functions for conditional formatting have been removed by Microsoft with this new rollout. There is supposed to be an option to revert to Classic visual from Modern visual but for our current PBI tenent, we cannot revert.

I heard they may restore all those features over the next months, but anyone else know anything abut this.  

I tried all kinds of options but FX never appeared unless I removed the year field in Legend, so never got what I wanted.