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

Hi Anonymous ,

You can try this:

Ans_Cat = if (AND('Table'[Answer] <=1, 'Table'[Answer] <> BLANK() ), "0-1 Active", IF(AND('Table'[Answer] >=2,'Table'[Answer] <=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

Hi ERD 

 

Thanks for this but when I used it, it seemed to now categorise the blank cells as "5+ Active" instead of just returning it blank.

  • 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.