Tracking Concurrent Calls in Call Center

blackautomation · June 30, 2021, 1:16am

I am working with just over 2 years of data. My PBIX file is very large and contains some sensitive data.
Let me explain how my data looks, and the relevant columns to complete this task.

Call Flow Column 1
In/Out

Call From Column 2
4199091234

Call Initiated Column 3
1/1/2021 08:30:20

Call Duration Column 4
00:02:05

Call End Time Column 5
1/1/2021 08:32:25

DNIS Column 6
4169671111

I have at current approx. 12 million lines of data like this. However, for some proposed current update we would like to show evidence of Concurrent calls OVER our approve threshold for each number (DNIS), and Trunk Lines.
Since the durations of calls has such a wide duration range, I have been trying to use an Time Interval table holding the per Second count throughout a 24 hour period. (No Success)
I am aware I will need to use Slicers to drill down to some of the finer information I am looking for, however I have not been able to come up with the correct DAX formula to give me a Concurrent Calls count.
Can anyone help me work this out. I have taken a long look to see if a Solution for this was previously posted and I haven’t found anything. If there is a link to something I would appreciate it, any assistance would be appreciated.

Required result:

List of Trunk Lines List of Phone Numbers Interval Times #of Concurrent Calls*

OR

I can do each Trunk Line separately and look for the following result;
Interval Times #of Concurrent Calls

Anurag · June 30, 2021, 6:44am

Hi,

Without pbix it is hard to solve your problem .Just share your pbix with dummy data so we can look into it.

Thanks,
Anurag

BrianJ · June 30, 2021, 6:54am

@blackautomation ,

Per @Anurag’s suggestion, we have available a number of techniques/tools to make it easier for you to mask existing data, or create representative dummy data.

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

In addition to providing a PBIX with nonconfidential data, it’s also always helpful to provide a mockup of the results you are looking for, so that responders can validate their solution before providing it to you.

Thanks.

Brian

EnterpriseDNA · July 1, 2021, 10:29pm

Hi @blackautomation, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

EnterpriseDNA · July 5, 2021, 2:03am

Hi @blackautomation, we’ve noticed that no response has been received from you since the 1st of July. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · July 5, 2021, 9:24pm

Hi @blackautomation, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

blackautomation · July 6, 2021, 3:50pm

Hi Everyone, I have enclosed a CSV file to use from a Call Center. Please let me know if you can calculate Concurrent Calls using his data.
CallCenterData.csv (29.0 KB)

thank you in advance for your assistance.

KimC · July 7, 2021, 12:25am

Hi @blackautomation,
That file is helpful however there are acronyms which makes it difficult to understand. Also when is the finish time, does it include ringing etc.
As @BrianJ mentioned, can you give a mockup of the desired result.

EnterpriseDNA · July 8, 2021, 11:20pm

Hi @blackautomation, we’ve noticed that no response has been received from you since July 7th.

This is a reminder for the mockup the users were requesting. This will help them give you a solution.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

blackautomation · August 17, 2021, 9:41pm

Hello everyone,

Due to other obligations brought on by the current situation we are all facing with COVID -19, I was unable to stay on top of this question to the Forum. I am still trying to find a solution to this problem, any assistance would be helpful.
To remind everyone, this is a question to see if we can get insights into the attached file to find out the number of concurrent calls at any given period. The file has 1million line as I also need to know if the solution can handle this amount of data.
The desired result I am seeking would allow me to select a date (using a slicer) and be able to see number of concurrent calls throughout the day from 00:00:00 to 23:59:59 to see when thresholds are being threatened or surpassed.

Call History Test.csv (57.4 MB)

eg.

IntervalStart	#Active Calls	#Active Calls OUT	#Active Calls IN
0:00:00	3	1	2
0:00:01	3	1	2
0:00:02	3	1	2

0:00:56	3	1	2
0:00:57	3	1	2
0:00:58	3	1	2
0:00:59	3	1	2
0:01:00	9	2	7
0:01:01	9	2	7

0:01:08	9	2	7
0:01:09	9	2	7
0:01:10	10	2	8

0:01:15	10	2	8
0:01:16	11	2	9

0:01:26	11	2	9
0:01:27	12	2	10

0:01:40	12	2	10
0:01:41	13	2	11

0:01:47	13	2	11
0:01:48	14	2	12
0:01:49	14	2	12
0:01:50	15	2	13
0:01:51	15	2	13
0:01:52	16	2	14
0:01:53	17	2	15

0:01:59	17	2	15
0:02:00	22	3	19

0:02:06	22	3	19
0:02:07	23	3	20
0:02:08	23	3	20
0:02:09	23	3	20
0:02:10	23	3	20
0:02:11	22	3	19

0:02:19	22	3	19
0:02:20	21	3	18

EnterpriseDNA · August 19, 2021, 11:51pm

Hello all, bumping this post for more visibility.

ankit · August 21, 2021, 2:43pm

Hi @blackautomation

I have came up with below solution, check if this is helpful. Few Points

Loaded Data for only first 1000 Rows from CSV for testing
Created a new Date Table. This will allow to filter for a particular date and check concurrency.
Created a Time Table using Initiated Field, can use same or create separate table. To be used in Visualization.
Once done, created a new DAX formula as below. It is using Date Difference to identify Rows with Initiated Time <= Selected DateTime and Ended Time >= Selected DateTime.

Active Calls =
VAR SelDate =
SELECTEDVALUE ( ‘Date’[Date] )
VAR ContextTime =
SELECTEDVALUE ( ‘Time Table’[Time] )
VAR DateVal =
CONVERT ( SelDate & " " & ContextTime, DATETIME )
RETURN
CALCULATE (
COUNTROWS ( ‘Call History Test’ ),
FILTER (
‘Call History Test’,
DATEDIFF ( DateVal, ‘Call History Test’[Call initiated], MINUTE ) <= 0
&& DATEDIFF ( DateVal, ‘Call History Test’[Call Ended], MINUTE ) >= 0
)
)

Below is how output will look like.

Also, attached the PBIX file
Call Center.pbix (61.5 KB)

Thanks
Ankit J

EnterpriseDNA · August 23, 2021, 2:07am

Hello @blackautomation. Good to see @ankit was able to help you with your query.
Kindly mark as solution his answer is this solved your query. Thanks!

blackautomation · August 23, 2021, 1:48pm

Thank you @ankit

Great work, your solution works great even with my extremely large data. Very much appreciate your time and effort. Also want to thank EnterpriseDNA for bumping up my post to assist me in getting a very quick resolution.

The Forum WORKS!!

Thanks everyone

Jerryspencer · October 17, 2024, 4:40am

I’d jump in since this is an area I’ve dabbled in a bit. That solution with the time interval table looks pretty solid, and I think it might work if you tweak it a bit more. I once worked on a similar setup, but we were tracking SIP softphone apps for iOS and Android. What helped me was breaking down the time into smaller chunks—like per second—then using those to count overlapping calls, which I think is what you’re aiming for right here. You might want to look into segmenting your data that way and see if it helps!