topic Re: Basic "IF" not working in DAX Commands and Tips

Basic "IF" not working

Anonymous — Thu, 10 Oct 2019 12:10:36 GMT

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,

Re: Basic "IF" not working

Anonymous — Thu, 10 Oct 2019 12:14:08 GMT

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

Re: Basic "IF" not working

Anonymous — Wed, 06 Nov 2019 07:56:44 GMT

The data type and format of distance_covered is Decimal number.

Re: Basic "IF" not working

Anonymous — Wed, 06 Nov 2019 08:48:09 GMT

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

Re: Basic "IF" not working

Anonymous — Wed, 13 Nov 2019 13:01:24 GMT

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!