Create Category Values Based on Another Column Range

[bendsteel6]

I've search the forums and can't find an issue quite like this...but it should be simple.  I simply want to create a "range" column based on a number in another column, in the same way I would a CASE-WHEN statement...

case
  when (COUNT(t1.CUST_NAME)) = 1 then "1 Truck"
  when (COUNT(t1.CUST_NAME)) = 2 then "2 Trucks"
  when (COUNT(t1.CUST_NAME)) = 3 then "3 Trucks"
  when (COUNT(t1.CUST_NAME)) = 4 then "4 Trucks"
  when (COUNT(t1.CUST_NAME)) = 5 then "5 Trucks"
  when (COUNT(t1.CUST_NAME)) between 6   and 10   then "6 to 10 Trucks"
  when (COUNT(t1.CUST_NAME)) between 11  and 20   then "11 to 20 Trucks"
  when (COUNT(t1.CUST_NAME)) between 21  and 50   then "21 to 50 Trucks"
  when (COUNT(t1.CUST_NAME)) between 51  and 100  then "51 to 100 Trucks"
  when (COUNT(t1.CUST_NAME)) between 101 and 250  then "101 to 250 Trucks"
  when (COUNT(t1.CUST_NAME)) between 251 and 500  then "251 to 500 Trucks"
  when (COUNT(t1.CUST_NAME)) between 501 and 1000 then "501 to 1000 Trucks"
  else "Over 1000 Trucks"
end

I tried creating a conditional column but it doesn't have a 'between' condition.  Is there a better/easier way? 

Thanks!

[v-chuncz-msft · Accepted solution]

@bendsteel6 ,

You may also use SWITCH.

https://community.powerbi.com/t5/Desktop/Creating-groups-based-on-numbers/td-p/196817

[bendsteel6]

Hi,

This solution works just as well as the previous...and a bit less code. 🙂

[Anonymous]

You can try this as a new column:

new column = 

IF(calculate(count(t1.CUST_NAME)) = 1 , "1 Truck",

IF(calculate(count(t1.CUST_NAME)) = 2 , " 2 Trucks",

IF(calculate(count(t1.CUST_NAME)) = 3 , " 3 Trucks",

IF(AND(calculate(count(t1.CUST_NAME)) <= 6, calculate(count(t1.CUST_NAME)) <=10, “6 to 10 Trucks”,"Over 1000 Trucks")))

[bendsteel6]

Thanks!  Works wonderfully!