Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cottrera
Post Prodigy
Post Prodigy

Conditional format bar chart based on column output

Hi
I require a DAX measure so that I can conditionally format the SurveyYear column in a bar chart

cottrera_1-1718614225328.png

with the following criteria

If the survey year is current year, current year -1, current year -2, current year -3, current year -4 then #53DA65 -- Green 

If the survey year is current year -5, current year -6, current year -7, current year -8, current year -9  then #FFA500 --Orange
If the survey year is current year -10, current year -11, current year -12, current year -13, current year -14  then #FF0000 --Red

If survey year is 'No Survey' thent hen #FF0000 --Red

Does anyone know the best apprach for this.
thank you 
Richard

1 ACCEPTED SOLUTION

Hi @cottrera,

The error is because your "Year" column has some string called "No Survey" and the subtraction happening below is not able to process the string. So, we can modify @MFelix 's measure as following:

 

Year condittional formatting =
VAR _YearValue =
    SELECTEDVALUE(Table[SurveyYear])
RETURN
    IF(_YearValue = "No Survey", "#FF0000",
      SWITCH (
              TRUE (),
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 5, "#53DA65",
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 10, "#FFA500",
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 15, "#FF0000",
              "#FF0000"
             )
    )

 

Please mark it as a solution.

Regards,

Tez147

View solution in original post

6 REPLIES 6
cottrera
Post Prodigy
Post Prodigy

HI thank you for this. However I am receiving this message error messsage.
Cannot convert value 'No Survey' of type Text to type Number.

This is probably because the column is TEXT as it contains

cottrera_0-1718623670131.png

thanks
Richard

 

Hi @cottrera,

The error is because your "Year" column has some string called "No Survey" and the subtraction happening below is not able to process the string. So, we can modify @MFelix 's measure as following:

 

Year condittional formatting =
VAR _YearValue =
    SELECTEDVALUE(Table[SurveyYear])
RETURN
    IF(_YearValue = "No Survey", "#FF0000",
      SWITCH (
              TRUE (),
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 5, "#53DA65",
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 10, "#FFA500",
              CONVERT(_YearValue,INTEGER) - YEAR ( TODAY ) > - 15, "#FF0000",
              "#FF0000"
             )
    )

 

Please mark it as a solution.

Regards,

Tez147

Thank you tez147 for amending this code and thank you again @MFelix  for starting the code

cottrera
Post Prodigy
Post Prodigy

Hi MFelix thank youfor your quick response. I have added the DAX as a column measure and amended the table / column name. However I am getting this error

The MAX function only accepts a column reference as an argument.

 

Year condittional formatting =
VAR _YearValue =
    MAX ( CONVERT (Last_Stock_Survey[SurveyYear], DOUBLE ) )
RETURN
    SWITCH (
        TRUE (),
        _YearValue - YEAR ( TODAY ) > - 5, "#53DA65",
        _YearValue - YEAR ( TODAY ) > - 10, "#FFA500",
        _YearValue - YEAR ( TODAY ) > - 15, "#FF0000",
        "#FF0000"
    )

Hi @cottrera ,

 

I changed the order of the syntax must be:

Year condittional formatting =
VAR _YearValue =
    CONVERT ( MAX(Table[SurveyYear]), DOUBLE )
RETURN
    SWITCH (
        TRUE (),
        _YearValue - YEAR ( TODAY ) > - 5, "#53DA65",
        _YearValue - YEAR ( TODAY ) > - 10, "#FFA500",
        _YearValue - YEAR ( TODAY ) > - 15, "#FF0000",
        "#FF0000"
    )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

Hi @cottrera ,

 

Try the following code:

Year condittional formatting =
VAR _YearValue =
    MAX ( CONVERT ( Table[SurveyYear], DOUBLE ) )
RETURN
    SWITCH (
        TRUE (),
        _YearValue - YEAR ( TODAY ) > - 5, "#53DA65",
        _YearValue - YEAR ( TODAY ) > - 10, "#FFA500",
        _YearValue - YEAR ( TODAY ) > - 15, "#FF0000",
        "#FF0000"
    )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors