how to write a measure to count duration of Helpdesk Calls lasting only some seconds

[thomasreick]

Hello there,

I need to have a hint on how to write a measure within a call center analysis , that counts number of Callid's but only for calls meeting some criteria. One of the criteria is that the duration in hh:mm:ss has to be below a defined value. the other one is wether the call had been taken by  callcenter agent (cca) or was lost due to inactivity of the cca.

Lost Calls <5seconds = calculate(count(Calls[CallId]), Calls[Callstatus]="Lost", Calls[CallDuration]< 00:00:05 ).

I do not see how to phrase the second filter 'Calls[CallDuration]< 00:00:05'.

Any hint or help is appreciated.

thx & brgds

Thomas

[thomasreick]

Hi Valtteri,

thx for your answer.

I understood your approach to extract only the seconds from the given Value of time. I did not consider that one

But actually I am not quite confident enough on what the data format has to be for the column holding the time consumption.

• I have a starting Timestamp (format DateTime) and a finished Timestamp (format DateTime).
• The difference can be interpreted as the total running time for the call.

What data format is needed for that difference (consumption-column) to  get your approach implemented?

Does it have to be DateTime or Time or Duration?

Help is appreciated.

thx

Thomas

[ValtteriN · Accepted solution]

Hi,

If you have date time format for start and end you can use datediff.

E.g. modified from my last example:

Dax:

Measure 21 = COUNTROWS(FILTER('Table (27)',DATEDIFF('Table (27)'[Start],'Table (27)'[End],SECOND)<5 && 'Table (27)'[Status]="Lost"))

[thomasreick]

Good Morning Valtteri,

I figured out that using your approach will return not only the ones  with a time less or equal to 00:00:05, but on top those with e.g. 00:04:05 as well. 

So basically your approach was the right hint. I needed to check the raw data to see what was included. By that I figured out that all time  with Seconds <=5 had been used, not only thos lasting just only 5 s.

The correct definition is now:

LostCallsBelow5s =

calculate(

count(table[callid]),

table[Callstatus]="lost",

second(table[duration])<=5,

minute(table[duration])=5,

hour(table[duration])=0

 )

Your approach to the solution is accepted, thank you very much.

Thomas

[ValtteriN]

Hi,

Here is one way to do this:

Data:

Here two out of four rows have values that fullfill both conditions.

Dax:

Measure 20 = COUNTROWS(FILTER('Table (27)',SECOND('Table (27)'[Duration])<5 && 'Table (27)'[Status]="Lost"))

End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/