Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

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, curren...
Need Help
  • tez147's avatar
    tez147
    2 years ago

    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

tez147's avatar
tez147
Regular Visitor
2 years ago

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's avatar
cottrera
Post Prodigy
2 years ago

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