Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Blank Formula Help

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

    Hi Anonymous ,

     

    You could use ISFILTER() function.

    Column = IF(ISBLANK('Table'[Answer]),BLANK(),IF('Table'[Answer]<1,"0-1 Active",IF('Table'[Answer]>=2&&'Table'[Answer]<=4,"2-4 Active","5+ Active")))

     

    Best Regards,

    Jay

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

Anonymous 

 

When using an IF, the last argument will be what all other values get coded as, so you need to explicitly define BLANK as BLANK. This will also probably be simpler using a SWITCH function: 

 

This has no 'alternate result' so should keep blank for blank.

Ans_Cat =

SWITCH( TRUE()
, 'Table'[Answer] <=1, "0-1 Active"

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

, 'Table'[Answer]  > 5, "5+ Active"

)

 

If it doesn't work, try: 

Ans_Cat =

SWITCH( TRUE()

, ISBLANK( Table'[Answer] ), BLANK()
, 'Table'[Answer] <=1, "0-1 Active"

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

, 'Table'[Answer]  > 5, "5+ Active"

)

 

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

Anonymous  You may also need to check for empty value (not just blank): 

 

Ans_Cat =

SWITCH( TRUE()

, ISBLANK( 'Table'[Answer] ) ||  'Table'[Answer] = "", BLANK()
, 'Table'[Answer] <=1, "0-1 Active"

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

, 'Table'[Answer]  > 5, "5+ Active"

)