Forum Discussion
smather
Helper III
5 years agoQueue 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'...
- 5 years ago
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 vResultResult:
DataInsights
Super User
5 years ago
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: