Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IF Formula Help AGAIN

Hi.   I am trying to categorise numerial responses into 3 different categories.   If they answered betweeb 0-1 - I want to categorise that as 0-1 Day Active If they answered between 2-4 - I want...
Need Help
  • ERD's avatar
    ERD
    5 years ago

    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.

ERD's avatar
ERD
Community Champion
5 years ago

Anonymous ,

True, sorry, here is a small change if you still want to use IF statements (I would use SWITCH). 

Ans_Cat_cln =
IF (
    AND ( 'T'[Answer] <= 1, 'T'[Answer] <> BLANK () ),
    "0-1 Active",
    IF (
        AND ( 'T'[Answer] >= 2, 'T'[Answer] <= 4 ),
        "2-4 Active",
        IF ( AND ( 'T'[Answer] >= 5, 'T'[Answer] <= 7 ), "5+ Active" )
    )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?



      • ERD's avatar
        ERD
        Community Champion
        5 years ago

        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.