Conditional-form. X-axis labels by month - entire axis turns red instead of only “backlog-growth"

[OriAshkenazi]

Hi all,

I’m trying to colour the month labels on a line chart so that:

• Red = months where more issues were opened than closed (backlog ↑)
• Grey = months where the backlog stayed the same or shrank

What I have so far

• Model:

  • dimDate calendar table
  • Issues fact table with Created on (open date) and ActualClosedDate (close date)
  • Two inactive relationships to dimDate[Date] (one for each date field)

• Measures:

  Issues Opened This Period :=
  CALCULATE (
      DISTINCTCOUNT ( Issues[ID] ),
      USERELATIONSHIP ( Issues[Created on], dimDate[Date] )
  )
  
  Issues Closed This Period :=
  CALCULATE (
      DISTINCTCOUNT ( Issues[ID] ),
      USERELATIONSHIP ( Issues[ActualClosedDate], dimDate[Date] ),
      NOT ISBLANK ( Issues[ActualClosedDate] )
  )
  
  Issue Delta (Month) :=
  VAR _y = SELECTEDVALUE ( dimDate[Year] )
  VAR _m = SELECTEDVALUE ( dimDate[MonthNumber] )
  RETURN
  CALCULATE (
      [Issues Opened This Period] - [Issues Closed This Period],
      FILTER (
          ALL ( dimDate ),
          dimDate[Year] = _y
              && dimDate[MonthNumber] = _m
      )
  )
  
  Axis Label Colour :=
  IF ( [Issue Delta (Month)] > 0, "#C62828", "#3C3C3C" )

• Chart: dual-line (Opened vs Closed) with the Month level of the date hierarchy on the X-axis.
• Conditional formatting: field-value rule on the X-axis labels using Axis Label Colour.

Problem

Every month label is red, even months where [Issue Delta (Month)] is zero or negative (the table visual confirms the delta is calculated correctly).

What I already tried

1. Verified that Issue Delta (Month) returns the expected positives/negatives in a matrix.
2. Ensured the X-axis is categorical and shows only the Month level.
3. Re-created the chart from scratch - same result.

What am I missing?

Is there a better pattern for month-level conditional formatting on an X-axis that uses the date hierarchy?
Do I need to flatten the hierarchy and use a separate Year-Month column, or is there a trick to make the current approach respect the month granularity?

Any pointers appreciated - thanks!

Ori

[v-hashadapu]

Hi @OriAshkenazi , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[v-hashadapu]

Hi @OriAshkenazi ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @OriAshkenazi ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[v-hashadapu · Accepted solution]

Hi @OriAshkenazi , Thank you for reaching out to the Microsoft Community Forum.

Power BI doesn’t currently support per-category X-axis label colouring in native visuals, even if you use a flat Year-Month column, a disconnected table or precise filtering logic. You’ll need to use the Deneb custom visual. Deneb is free from AppSource and is built on Vega-Lite, which allows full control over how axis labels are styled, including binding a measure like your X Axis Label Colour directly to the label colour. Unlike Power BI’s native visuals, Deneb respects per-category formatting and data context from slicers and filters, so it can reflect your logic precisely.

All you need is a YearMonthText column in your date table, such as "2024-Jan" and your existing measures for opened, closed and colour logic. Then, in Deneb, you use a Vega-Lite JSON template that builds a dual-line chart and colours each X-axis label according to your measure. This approach guarantees that each month label reflects the correct status of the backlog, red when it grew, black when it didn’t and avoids the formatting aggregation issue completely.

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[burakkaragoz]

Hi @OriAshkenazi ,

It sounds like you're using conditional formatting on the X-axis labels by month, and when one condition is met, the entire axis turns red – even for months that shouldn't.

This usually happens when the formatting logic is applied at the visual level instead of per data point. To fix this:

1. Make sure you're applying the conditional formatting to the field used in the X-axis, not the entire visual.
2. If you're using a measure for formatting, it should return different values per month. For example:

ColorMeasure = 
SWITCH(
    TRUE(),
    [Month] = "January", "#FF0000",
    [Month] = "February", "#00FF00",
    "#000000"
)

Then apply this measure to the Data colors > fx section in the formatting pane.

translation and formatting supported by AI

[OriAshkenazi]

To provide more specifics, the primary DAX measure I've been using to generate the color codes for the X-axis labels, based on the advice to have a measure return different values per month, is structured as follows:

DAX Measure: X Axis Label Color (SWITCH)

X Axis Label Color (SWITCH) =
VAR IssuesOpened = COALESCE([Issues Opened This Period], 0)
VAR IssuesClosed = COALESCE([Issues Closed This Period], 0)
RETURN
    SWITCH(
        TRUE(),
        IssuesOpened > IssuesClosed, "#FF0000",  // Red if Opened > Closed
        "#000000"  // Default to Black if Opened <= Closed
    )
        

This measure relies on two base measures:

• [Issues Opened This Period] (which is essentially DISTINCTCOUNT(Issues[ID]) filtered by the current period)
• [Issues Closed This Period] (which is CALCULATE(DISTINCTCOUNT(Issues[ID]), USERELATIONSHIP(dimDate[Date], Issues[ActualClosedDate]), NOT ISBLANK(Issues[ActualClosedDate])), also filtered by the current period).

How This Measure Was Employed for Conditional Formatting:

1. The X Axis Label Color (SWITCH) DAX measure was created as defined above.
2. The relevant chart visual (a line chart with months on the X-axis) was selected.
3. I navigated to the formatting options in the "Visualizations" pane:
   • Clicked on "Format your visual" (the paint roller icon).
   • Expanded the "X-axis" section.
   • Expanded the "Values" subsection.
4. For the "Color" property, I clicked the fx (conditional formatting) button.
5. In the conditional formatting dialog that appeared:
   • I set the "Format style" to "Field value".
   • For the field "What field should we base this on?", I selected my DAX measure: X Axis Label Color (SWITCH).
   • I then clicked "OK".

As mentioned previously, when I add this X Axis Label Color (SWITCH) measure to a table visual (with each month as a row), it correctly shows "#FF0000" for months where more issues were opened than closed, and "#000000" otherwise. However, the X-axis labels on the chart itself do not dynamically reflect these per-month color changes and tend to remain in a single state (e.g., all red).

[OscarTh]

When you apply conditional formatting to X-axis labels by month, sometimes Power BI turns the entire axis red if the logic isn’t quite right. Double-check your rules and make sure the formatting expression returns the correct colors for each label. Also, try testing with simpler conditions to isolate the issue.

[OriAshkenazi]

Hi there,

I've now gone back and tested every suggestion, but unfortunately none of them changed the outcome – the entire X-axis still renders in one colour.

• Measure logic: Verified in a table visual. The measure returns muted red only when backlog grows and muted green otherwise.
• Simpler test: Replaced the measure with IF(MONTH(dimDate[Date]) = 1, "#A84F4F", "#5F7D5F"). Labels still all red.
• Axis variants: Swapped from hierarchy to a flat Year-Month column, then to Year-Month numeric (e.g. 202401). CF still fails.
• Summarisation options: Tried First, Max, Min, and None inside the conditional-formatting dialog - no change.

Given all of the above, it feels like a limitation (or bug) in how label colour is resolved when any date column is on the X-axis, not a problem in the measure itself.

If anyone has managed to get per-month label colours working on a date axis without custom visuals, could you please share the exact steps (or a sample PBIX)?

Thanks in advance for any fresh ideas.