Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to use an IF statement to create categories for my data based in a number value.
I have a table with a column distance_covered (I checked and is number type). I want to create a new column that categorize the data in "Less than 5", "5 to 15", "15 to 25", "25 to 75" and "75 to 300".
The formula I'm using is: Distance Category = IF(bi_goto_session[distance_covered] > 0 && bi_goto_session[distance_covered] < 5, "Less than 5", IF(bi_goto_session[distance_covered] >= 5 && bi_goto_session[distance_covered] < 15, "5 to 15", IF( bi_goto_session[distance_covered] >= 15 && bi_goto_session[distance_covered] < 25, "15 to 25", IF(bi_goto_session[distance_covered] >= 25 && bi_goto_session[distance_covered] < 75 , "25 to 75", IF(bi_goto_session[distance_covered] >= 75 && bi_goto_session[distance_covered] < 300, "75 to 300")))))
But the results are:
Any ideas why??
Thanks,
Maybe there is something wrong with the data type of your distance_covered column. I created a same table that has distance_covered's data type is Decimal number and it's working correctly with your IF formula
But I have some tip for you, instead of using many IF in a formula, we can use SWITCH, it will make your code look cleaner
Distance Category =
VAR distance = bi_goto_session[distance_covered]
RETURN SWITCH(TRUE(),
distance > 0 && distance < 5, "Less than 5",
distance >= 5 && distance < 15, "5 to 15",
distance >= 15 && distance < 25, "15 to 25",
distance >= 25 && distance < 75, "25 to 75",
distance >= 75 && distance < 300, "75 to 300")
Hi,
I did opened a new report, got the data "as new" and the same formula is working fine now...
Thanks for the help and for the tip!
Hi,
Can you let us know of the Data types of the Distance_Covered Column
Best Regards,
Vignesh M
If what I suggested worked for you feel free to Drop a "Kudos" and Consider to "Accept as Solution" if I solved your Issue 🙂
The data type and format of distance_covered is Decimal number.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |