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 to categorise that as 2-4 Days Active

If they answered between 5-7 - I want to categorise that as 5+ Days Active

If the box is not filled - I don't want it to categorise

 

See the example of my data set below, the column in bold is what I need.

NameAnswerCatergory
Mary22-4 Active
Joe55+ Active
John  
 
I have tried the below DAX and it works except it categories when a cell is left blank where it hasnt been answered as 0-1 Active. Is there a way to alter the dax below so it doesn't categorise the blank cells?
 
Ans_Cat = if ('Table'[Answer] <=1, "0-1 Active", IF(AND('Table'[Answer] >=2,'Table'[Answer] <=4), "2-4 Active", "5+ Active")
 
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.

11 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Try

    For a calculated column:

    Ans_Cat =
    SWITCH(TRUE(),

    ISBLANK(Table [Answer]), BLANK(),

    Table[Answer] <= 1, "0-1 Active",

    Table [Answer] <=4, "2-4 Active",

    "5+ Active")

     

    If you need this as a measure, you will need to wrap the expression with SUM, as in SUM(Table[Answer])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi PaulDBrown 

       

      It didn't seem to work... it said I had too many agruements in place but I can't figure out where!

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        What is the data type for the column Table[answer]?

        Also, are you adding this as a calculated column to a table or are you creating a measure?

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