cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kennwort Frequent Visitor
Frequent Visitor

Count active/concurrent calls every second

I have a dataset (call data records) which captures the start and end date/time of calls, along with the direction (INBOUND/OUTBOUND) and the location (Store number) from/to where that call was made.

 

My client would like to know a) in aggregate across all stores how many calls are made every second, and b) the same stats filtered by criteria such as store #, call direction, month (or other selected timeframe).

 

This is similar to the "events in progress" problem and I addressed it by creating a table with a row per second in a day and adding a calculated column. That allowed me to address requirement a), but I am unable to figure out how to address requirement b), filtering while dynamically recalculating the number of concurrent calls every second (for billing purposes).

 

Apologies in advance for the lengthy post, but any help is appreciated as I am quite a newbie to DAX!

 

Here is my sample dataset (named CDR "Call Data Records"):

 

Call StartTime Call StartTime Call EndCall DurationStore#Call Direction
7/15/2017 12:00:00 AM12:00:00 AM12:04:57 AM29720369INBOUND
7/19/2017 12:00:00 AM12:00:00 AM12:02:59 AM17930214INBOUND
8/3/2017 12:00:00 AM12:00:00 AM12:03:49 AM22916074OUTBOUND
7/19/2017 12:01:00 AM12:01:00 AM12:02:20 AM8016074INBOUND
7/19/2017 12:01:00 AM12:01:00 AM12:05:52 AM2922149OUTBOUND
7/19/2017 12:01:00 AM12:01:00 AM12:06:27 AM32730214INBOUND
8/18/2017 12:01:00 AM12:01:00 AM12:02:11 AM7116074INBOUND
8/22/2017 12:01:00 AM12:01:00 AM12:02:42 AM1023157INBOUND
8/24/2017 12:01:00 AM12:01:00 AM12:02:50 AM1102149INBOUND
9/18/2017 12:01:10 AM12:01:10 AM12:06:28 AM31816074INBOUND
9/5/2017 12:01:16 AM12:01:16 AM12:03:53 AM15730214INBOUND
9/7/2017 12:01:27 AM12:01:27 AM12:03:18 AM11116074INBOUND
9/11/2017 12:01:41 AM12:01:41 AM12:02:43 AM6220369INBOUND
9/14/2017 12:01:48 AM12:01:48 AM12:03:18 AM9011836INBOUND
9/14/2017 12:01:50 AM12:01:50 AM12:04:49 AM17930214INBOUND
9/11/2017 12:01:52 AM12:01:52 AM12:04:35 AM16330214INBOUND
9/19/2017 12:01:53 AM12:01:53 AM12:03:20 AM877282INBOUND
7/15/2017 12:02:00 AM12:02:00 AM12:05:39 AM21911836INBOUND
7/19/2017 12:02:00 AM12:02:00 AM12:02:27 AM2711836INBOUND
7/25/2017 12:02:00 AM12:02:00 AM12:03:41 AM10111836OUTBOUND
8/22/2017 12:02:00 AM12:02:00 AM12:03:48 AM1084320INBOUND
8/24/2017 12:02:00 AM12:02:00 AM12:03:16 AM7616074INBOUND
9/14/2017 12:02:07 AM12:02:07 AM12:06:07 AM24016074INBOUND
9/3/2017 12:02:24 AM12:02:24 AM12:04:40 AM1364320INBOUND
7/23/2017 12:03:00 AM12:03:00 AM12:04:34 AM9416074INBOUND
7/25/2017 12:03:00 AM12:03:00 AM12:04:41 AM1013157INBOUND
7/25/2017 12:03:00 AM12:03:00 AM12:06:06 AM18616074INBOUND
7/31/2017 12:03:00 AM12:03:00 AM12:03:03 AM316074INBOUND
7/31/2017 12:03:00 AM12:03:00 AM12:04:30 AM9016074INBOUND
8/22/2017 12:03:00 AM12:03:00 AM12:03:32 AM323157INBOUND
8/24/2017 12:03:00 AM12:03:00 AM12:03:03 AM316074OUTBOUND
8/24/2017 12:03:00 AM12:03:00 AM12:04:57 AM1172149INBOUND
8/31/2017 12:03:00 AM12:03:00 AM12:05:27 AM14716074INBOUND
8/31/2017 12:03:00 AM12:03:00 AM12:11:22 AM50216074INBOUND
9/3/2017 12:03:17 AM12:03:17 AM12:06:13 AM1763157INBOUND
9/16/2017 12:03:31 AM12:03:31 AM12:08:10 AM27930214INBOUND
9/6/2017 12:03:51 AM12:03:51 AM12:04:42 AM5111836OUTBOUND

 

Here's the current output that shows aggregate count per second. It is calculated against a table called "Time1secIntervals" withe column "IntervalStart" holding the 1 second time intervals.

 

IntervalStart#Active Calls#Active Calls OUT#Active Calls IN
0:00:00312
0:00:01312
0:00:02312
<Skipping rows till next transition...> 
0:00:56312
0:00:57312
0:00:58312
0:00:59312
0:01:00927
0:01:01927
<Skipping rows till next transition...> 
0:01:08927
0:01:09927
0:01:101028
<Skipping rows till next transition...> 
0:01:151028
0:01:161129
<Skipping rows till next transition...> 
0:01:261129
0:01:2712210
<Skipping rows till next transition...> 
0:01:4012210
0:01:4113211
<Skipping rows till next transition...> 
0:01:4713211
0:01:4814212
0:01:4914212
0:01:5015213
0:01:5115213
0:01:5216214
0:01:5317215
<Skipping rows till next transition...> 
0:01:5917215
0:02:0022319
<Skipping rows till next transition...> 
0:02:0622319
0:02:0723320
0:02:0823320
0:02:0923320
0:02:1023320
0:02:1122319
<Skipping rows till next transition...> 
0:02:1922319
0:02:2021318

 

Lastly, here's how the Calculated Columns #Active Calls, #Active Calls OUT and #Active Calls IN were determined:

 

#Active Calls = CALCULATE(COUNTA('CDR'[Call Start]), FILTER(ALL('CDR'), and('CDR'[Time Call Start] <= Time1secIntervals[IntervalStart], 'CDR'[Time Call End] > Time1secIntervals[IntervalStart])))
 
#Active Calls OUT = CALCULATE(COUNTA('CDR'[Call Start]), FILTER(ALL('CDR'), and('CDR'[Time Call Start] <= Time1secIntervals[IntervalStart], AND('CDR'[Time Call End] > Time1secIntervals[IntervalStart], 'CDR'[Call Direction]="Outbound"))))
 
#Active Calls IN = Time1secIntervals[#Active Calls] - Time1secIntervals[#Active Calls OUT]

#Active Calls = CALCULATE(COUNTA('CDR'[Call Start]), FILTER(ALL('CDR'), and('CDR'[Time Call Start] <= Time1secIntervals[IntervalStart], 'CDR'[Time Call End] > Time1secIntervals[IntervalStart])))

 

#Active Calls OUT = CALCULATE(COUNTA('CDR'[Call Start]), FILTER(ALL('CDR'), and('CDR'[Time Call Start] <= Time1secIntervals[IntervalStart], AND('CDR'[Time Call End] > Time1secIntervals[IntervalStart], 'CDR'[Call Direction]="Outbound"))))

 

#Active Calls IN = Time1secIntervals[#Active Calls] - Time1secIntervals[#Active Calls OUT]

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: Count active/concurrent calls every second

@kennwort,

 

b), filtering while dynamically recalculating the number of concurrent calls every second

If you first requirement if achieved by creating calculate measure, then if you select items on slicer(store #, call direction, month), the measure will be recalculated.

 

Regards,

Charlie Liao

5 REPLIES 5
kennwort Frequent Visitor
Frequent Visitor

Re: Count active/concurrent calls every second

Any suggestions? Appreciate any help I can get. Thanks!

Moderator v-caliao-msft
Moderator

Re: Count active/concurrent calls every second

@kennwort,

 

b), filtering while dynamically recalculating the number of concurrent calls every second

If you first requirement if achieved by creating calculate measure, then if you select items on slicer(store #, call direction, month), the measure will be recalculated.

 

Regards,

Charlie Liao

kennwort Frequent Visitor
Frequent Visitor

Re: Count active/concurrent calls every second

@v-caliao-msft

 

Thank you for the response. I had been struggling to create a measure that performed the same function, but I finally got it working. Appreciate the suggestion.

amshukla Frequent Visitor
Frequent Visitor

Re: Count active/concurrent calls every second

@kennwort Could you please share the updated measure, I am struggling with the same issue.

 

Awaiting your response @kennwort,

 

Regards,

Amit Shukla

amshukla Frequent Visitor
Frequent Visitor

Re: Count active/concurrent calls every second

@v-caliao-msft could you please suggest the mesure for this issue, i have same problem statment.

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 1,752 guests
Please welcome our newest community members: