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
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
Solved! Go to 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
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
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
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |