Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Basic "IF" not working

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:

1.png

 

Any ideas why??

Thanks, 

4 REPLIES 4
Anonymous
Not applicable

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

 

 
Anonymous
Not applicable

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!

Anonymous
Not applicable

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 🙂

Anonymous
Not applicable

The data type and format of distance_covered is Decimal number.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors