Forum Discussion
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 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 |
6 Replies
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 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-kamitchandak 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
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"
)
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"
)
