topic Re: Conditional format bar chart based on column output in DAX Commands and Tips

Conditional format bar chart based on column output

cottrera — Mon, 17 Jun 2024 09:14:25 GMT

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

Re: Conditional format bar chart based on column output

MFelix — Mon, 17 Jun 2024 10:14:51 GMT

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

Re: Conditional format bar chart based on column output

cottrera — Mon, 17 Jun 2024 10:33:58 GMT

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"

)

Re: Conditional format bar chart based on column output

MFelix — Mon, 17 Jun 2024 10:58:24 GMT

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

Re: Conditional format bar chart based on column output

cottrera — Mon, 17 Jun 2024 11:28:03 GMT

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

Re: Conditional format bar chart based on column output

tez147 — Mon, 17 Jun 2024 12:49:49 GMT

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

Re: Conditional format bar chart based on column output

cottrera — Mon, 17 Jun 2024 12:55:47 GMT

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