Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kennwort
Frequent Visitor

Count active/concurrent calls every second

‎10-17-2017 11:30 PM

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]

Labels:
Message 1 of 6
4,514 Views
1 ACCEPTED SOLUTION
v-caliao-msft
Microsoft Employee
Microsoft Employee

‎10-19-2017 12:47 AM

@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

View solution in original post

Message 3 of 6
4,995 Views
0
5 REPLIES 5
v-caliao-msft
Microsoft Employee
Microsoft Employee

‎10-19-2017 12:47 AM

@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

Message 3 of 6
4,996 Views
0
kennwort
Frequent Visitor
In response to v-caliao-msft

‎10-19-2017 07:55 AM

@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.

Message 4 of 6
4,479 Views
0
amshukla
Frequent Visitor
In response to kennwort

‎01-17-2018 10:38 PM

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

 

Awaiting your response @kennwort,

 

Regards,

Amit Shukla

Message 5 of 6
4,429 Views
0
amshukla
Frequent Visitor
In response to amshukla

‎01-17-2018 11:26 PM

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

 

 

Message 6 of 6
4,425 Views
0
kennwort
Frequent Visitor

‎10-18-2017 08:12 AM

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

Message 2 of 6
4,497 Views
0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

View All