Conditional format bar chart based on column output

[cottrera]

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

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

[cottrera]

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

thanks
Richard

[tez147 · 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

[cottrera]

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

[cottrera]

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"

    )

[MFelix]

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"
    )

[MFelix]

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"
    )