Queue Time

[smather]

Hi there

I have a list of calls, with their queue time, which looks a little like this:

Handled	00:00:10
Abandoned	00:01:06
Handled	00:00:52
Handled	00:04:00

and there's a couple of things I want to do but for the life of me I can't figure out. The first thing is: 

I want to say if the status is handled and the queue time is -

equal to or less then 00:00:10 then say "blah"

more than 00:00:10 and less than or equal to 00:00:20 then say "blah2"

more than 00:00:20 and less than or equal to 00:00:30 then say "blah3"

more than 00:00:30 and less than 00:00:60 then say "blah3"

equal to or more than "00:00:60" then say "blah5"

but i cannot figure out either the right M code or Dax for this (I don't care if it's a measure or a custom column)

The second thing I want to do is pretty much the same, but if the status was abandoned and only anything 10 secs or above. 

Any ideas would be a massive help, thank you 🙂 

[DataInsights · Accepted solution]

@smather,

This solution uses both M and DAX. Since the Queue Time is text, you need to convert it to seconds. In Power Query, split the Queue Time column on the colon delimiter and change the type to whole number:

Create a calculated column (DAX) to convert Queue Time to seconds:

Total Seconds = 
( Table1[Queue Time Hour] * 3600 ) + ( Table1[Queue Time Minute] * 60 ) + Table1[Queue Time Second]

Create a calculated column (DAX) for the Category:

Category = 
VAR vStatus = Table1[Status]
VAR vSeconds = Table1[Total Seconds]
VAR vResult =
    SWITCH (
        TRUE (),
        vStatus = "Handled"
            && vSeconds <= 10, "<= 10",
        vStatus = "Handled"
            && vSeconds > 10
            && vSeconds <= 20, "10 - 20",
        vStatus = "Handled"
            && vSeconds > 20
            && vSeconds <= 30, "20 - 30",
        vStatus = "Handled"
            && vSeconds > 30
            && vSeconds < 60, "30 - 60",
        vStatus = "Handled"
            && vSeconds >= 60, ">= 60",
        vStatus = "Abandoned"
            && vSeconds >= 10, "Abandoned"
    )
RETURN
    vResult

Result: