Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
@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"
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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"
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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-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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi there.
That DAX still categorisies when a cell under the Answer Column is blank as 0-1 Active.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |