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
GrahamR99
Resolver I
Resolver I

If statement to build Time Range

Hello

I have custom column that has this code.

Talk_Time_Range = IF(Maintel_Data[Talk_Time_Seconds] >= 0 && Maintel_Data[Talk_Time_Seconds]<=30,"Under 30 secs",IF(Maintel_Data[Talk_Time_Seconds] >= 31 && Maintel_Data[Talk_Time_Seconds]<=60,"30 - 60 secs",IF(Maintel_Data[Talk_Time_Seconds] >= 61 && Maintel_Data[Talk_Time_Seconds]<=120,"1 - 2 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 121 && Maintel_Data[Talk_Time_Seconds]<180,"2 - 3 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 181 && Maintel_Data[Talk_Time_Seconds]<240,"3 - 4 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 241 && Maintel_Data[Talk_Time_Seconds]<300,"4 - 5 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 301 && Maintel_Data[Talk_Time_Seconds]<360,"5 - 6 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 361 && Maintel_Data[Talk_Time_Seconds]<420,"6 - 7 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 421 && Maintel_Data[Talk_Time_Seconds]<480,"7 - 8 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 481 && Maintel_Data[Talk_Time_Seconds]<540,"8 - 9 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 541 && Maintel_Data[Talk_Time_Seconds]<600,"9 - 10 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 601 && Maintel_Data[Talk_Time_Seconds]<1200,"10 - 20 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 1201 && Maintel_Data[Talk_Time_Seconds]<1800,"20 - 30 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 1801 && Maintel_Data[Talk_Time_Seconds]<2400,"30 - 40 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 2401 && Maintel_Data[Talk_Time_Seconds]<3000,"40 - 50 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 3001 && Maintel_Data[Talk_Time_Seconds]<3600,"50 - 60 mins",IF(Maintel_Data[Talk_Time_Seconds] >= 3601 && Maintel_Data[Talk_Time_Seconds]<7200,"1 - 2 hours",IF(Maintel_Data[Talk_Time_Seconds] >= 7201 && Maintel_Data[Talk_Time_Seconds]<10800,"2 - 3 hours",IF(Maintel_Data[Talk_Time_Seconds] >= 10801 && Maintel_Data[Talk_Time_Seconds]<14400,"3 - 4 hours",IF(Maintel_Data[Talk_Time_Seconds] >= 14401 && Maintel_Data[Talk_Time_Seconds]<18000,"4 - 5 hours",IF(Maintel_Data[Talk_Time_Seconds] >= 18001 && Maintel_Data[Talk_Time_Seconds]<21600,"5 - 6 hours","6 plus hours")))))))))))))))))))))
 
But if the column Maintel_Data[Talk_Time_Seconds] is over 60 it says "30 - 60 secs" but it should say "1 - 2 mins", have I done something wrong?
 
Regards
 
GrahamR99
1 ACCEPTED SOLUTION
edhans
Super User
Super User

It works fine for me. It returns 1-2 minutes. I'd need to see your data to understand what is going on.

 

Rather than nested IF statements, consider switching to the SWITCH function. For example:

 

New Talk Time = 
SWITCH(
    TRUE(),
    Maintel_Data[Talk_Time_Seconds] >= 0 && Maintel_Data[Talk_Time_Seconds] <= 30, "Under 30 Seconds",
    Maintel_Data[Talk_Time_Seconds] > 30 && Maintel_Data[Talk_Time_Seconds] <= 60, "30-60 Seconds",
    Maintel_Data[Talk_Time_Seconds] > 60 && Maintel_Data[Talk_Time_Seconds] <= 120, "1-2 Minutes",
    Maintel_Data[Talk_Time_Seconds] > 120 && Maintel_Data[Talk_Time_Seconds] <= 180, "2-3 Minutes",
    Maintel_Data[Talk_Time_Seconds] > 180 && Maintel_Data[Talk_Time_Seconds] <= 240, "3-4 Minutes"
)

  



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

It works fine for me. It returns 1-2 minutes. I'd need to see your data to understand what is going on.

 

Rather than nested IF statements, consider switching to the SWITCH function. For example:

 

New Talk Time = 
SWITCH(
    TRUE(),
    Maintel_Data[Talk_Time_Seconds] >= 0 && Maintel_Data[Talk_Time_Seconds] <= 30, "Under 30 Seconds",
    Maintel_Data[Talk_Time_Seconds] > 30 && Maintel_Data[Talk_Time_Seconds] <= 60, "30-60 Seconds",
    Maintel_Data[Talk_Time_Seconds] > 60 && Maintel_Data[Talk_Time_Seconds] <= 120, "1-2 Minutes",
    Maintel_Data[Talk_Time_Seconds] > 120 && Maintel_Data[Talk_Time_Seconds] <= 180, "2-3 Minutes",
    Maintel_Data[Talk_Time_Seconds] > 180 && Maintel_Data[Talk_Time_Seconds] <= 240, "3-4 Minutes"
)

  



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hello

I didn't think it was the data but after checking it was.

 

I was converting seconds but didn't relise it was only getting the seconds, I needed to add the hours and the minutes to my seconds field.

 

I used your switch code and it also worked.

 

Regards

 

GrahamR99

Excellent. Glad you got it sorted out, and learned a new function! 😁 SWITCH is so much easier to read and edit.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

By the way @GrahamR99 I noticed a small bug in your code:

It will not handle 300 seconds. It will drop down to the "Over 6 hours" section. Make sure when you are coverting to SWITCH you fix that.

 

EDIT: actually, it is all of your code the rest of the way down. Seconds of exactly 420, 480, 540, etc. will fail.

 

2020-01-21 08_46_32-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I agree with @edhans,

 

Your code works fine for me, but a switch to Switch would be much cleaner and easier to read.  Below are 2 columns, one with your code and one with edhans.

 

 

 

 

Time.PNG

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.