Forum Discussion
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
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
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
6 Replies
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" )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 ) )RETURNSWITCH (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" )
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
RichardHi 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
