IF Formula Help AGAIN

[Anonymous]

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.

Name	Answer	Catergory
Mary	2	2-4 Active
Joe	5	5+ 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")

[PaulDBrown]

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]

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]

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?

[Anonymous]

Thank you so much!!

[ERD]

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]

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]

@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]

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 · Accepted solution]

@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]

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]

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