Blank Formula Help

[Anonymous]

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 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 categorises when a cell is left blank where it hasnt been answered as 0-1 Active.

Ans_Cat = if ('Table'[Answer] <=1, "0-1 Active", IF(AND('Table'[Answer] >=2,'Table'[Answer] <=4), "2-4 Active", "5+ Active")

So i tried 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"))

But it now seems to categorise the blank sells as 5+ Active... I don't want them to categorise the blank cells at all, I just want the answer to remain blank if they haven't responded to the question.

[Anonymous · Accepted solution]

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]

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

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

)

[amitchandak]

@Anonymous , if answer is a column it should work

Switch ( True() ,
'Table'[Answer] <=1, "0-1 Active",
'Table'[Answer] <=4, "2-4 Active"
, "5+ Active")

But if it is a measure try for segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[AllisonKennedy]

---

@amitchandak wrote:

@Anonymous , if answer is a column it should work

Switch ( True() ,
'Table'[Answer] <=1, "0-1 Active",
'Table'[Answer] <=4, "2-4 Active"
, "5+ Active")

But if it is a measure try for segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

---

@amitchandak You have not tested for blank values, so the result of your formula will give "5+ Active" if they don't fill in the field, since that's the 'alternate result' you gave in the SWITCH.

[Anonymous]

Hi there.

That DAX still categorisies when a cell under the Answer Column is blank as 0-1 Active.