Forum Discussion

smather's avatar
smather
Icon for Helper III rankHelper III
5 years ago
Solved

Queue Time

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'...
  • DataInsights's avatar
    5 years ago

    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: