Forum Discussion
IF Formula Help AGAIN
Anonymous ,
I suppose your Answer column is of Number type.
If you need a calculated column, then
Ans_Cat_cln = SWITCH ( TRUE (), ISBLANK(T[Answer]), BLANK (), T[Answer] <= 1, "0-1 Active", T[Answer] <= 4, "2-4 Active", "5+ Active" )If you need a measure, then
#Ans_Cat = VAR currentAnswer = MAX ( T[Answer] ) RETURN IF ( HASONEVALUE ( T[Answer] ), COALESCE ( SWITCH ( TRUE (), ISBLANK(currentAnswer), BLANK (), currentAnswer <= 1, "0-1 Active", currentAnswer <= 4, "2-4 Active", "5+ Active" ), "" ) )If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Hi ERD
If you think SWITCH would be better, how would I rewrite the DAX? Sorry I am a complete beginner and just finding my feet with this!
Anonymous ,
I suppose your Answer column is of Number type.
If you need a calculated column, then
Ans_Cat_cln =
SWITCH (
TRUE (),
ISBLANK(T[Answer]), BLANK (),
T[Answer] <= 1, "0-1 Active",
T[Answer] <= 4, "2-4 Active",
"5+ Active"
)
If you need a measure, then
#Ans_Cat =
VAR currentAnswer = MAX ( T[Answer] )
RETURN
IF (
HASONEVALUE ( T[Answer] ),
COALESCE (
SWITCH (
TRUE (),
ISBLANK(currentAnswer), BLANK (),
currentAnswer <= 1, "0-1 Active",
currentAnswer <= 4, "2-4 Active",
"5+ Active"
),
""
)
)
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
This is great thank you.
Also one last thing, people have responded with 0 as an answer but it is now reading it as a blank cell and isn't categorising the number. 0 is a valid answer as I want it to categorise as 0-1 Active?
Anonymous ,
I've changed the previous code a bit. Please, try again.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.