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: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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,026 Views
1 ACCEPTED SOLUTION
v-caliao-msft
Employee
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,507 Views
0
5 REPLIES 5
v-caliao-msft
Employee
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,508 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
3,991 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
3,941 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
3,937 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,009 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All