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.
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 Start | Time Call Start | Time Call End | Call Duration | Store# | Call Direction |
7/15/2017 12:00:00 AM | 12:00:00 AM | 12:04:57 AM | 297 | 20369 | INBOUND |
7/19/2017 12:00:00 AM | 12:00:00 AM | 12:02:59 AM | 179 | 30214 | INBOUND |
8/3/2017 12:00:00 AM | 12:00:00 AM | 12:03:49 AM | 229 | 16074 | OUTBOUND |
7/19/2017 12:01:00 AM | 12:01:00 AM | 12:02:20 AM | 80 | 16074 | INBOUND |
7/19/2017 12:01:00 AM | 12:01:00 AM | 12:05:52 AM | 292 | 2149 | OUTBOUND |
7/19/2017 12:01:00 AM | 12:01:00 AM | 12:06:27 AM | 327 | 30214 | INBOUND |
8/18/2017 12:01:00 AM | 12:01:00 AM | 12:02:11 AM | 71 | 16074 | INBOUND |
8/22/2017 12:01:00 AM | 12:01:00 AM | 12:02:42 AM | 102 | 3157 | INBOUND |
8/24/2017 12:01:00 AM | 12:01:00 AM | 12:02:50 AM | 110 | 2149 | INBOUND |
9/18/2017 12:01:10 AM | 12:01:10 AM | 12:06:28 AM | 318 | 16074 | INBOUND |
9/5/2017 12:01:16 AM | 12:01:16 AM | 12:03:53 AM | 157 | 30214 | INBOUND |
9/7/2017 12:01:27 AM | 12:01:27 AM | 12:03:18 AM | 111 | 16074 | INBOUND |
9/11/2017 12:01:41 AM | 12:01:41 AM | 12:02:43 AM | 62 | 20369 | INBOUND |
9/14/2017 12:01:48 AM | 12:01:48 AM | 12:03:18 AM | 90 | 11836 | INBOUND |
9/14/2017 12:01:50 AM | 12:01:50 AM | 12:04:49 AM | 179 | 30214 | INBOUND |
9/11/2017 12:01:52 AM | 12:01:52 AM | 12:04:35 AM | 163 | 30214 | INBOUND |
9/19/2017 12:01:53 AM | 12:01:53 AM | 12:03:20 AM | 87 | 7282 | INBOUND |
7/15/2017 12:02:00 AM | 12:02:00 AM | 12:05:39 AM | 219 | 11836 | INBOUND |
7/19/2017 12:02:00 AM | 12:02:00 AM | 12:02:27 AM | 27 | 11836 | INBOUND |
7/25/2017 12:02:00 AM | 12:02:00 AM | 12:03:41 AM | 101 | 11836 | OUTBOUND |
8/22/2017 12:02:00 AM | 12:02:00 AM | 12:03:48 AM | 108 | 4320 | INBOUND |
8/24/2017 12:02:00 AM | 12:02:00 AM | 12:03:16 AM | 76 | 16074 | INBOUND |
9/14/2017 12:02:07 AM | 12:02:07 AM | 12:06:07 AM | 240 | 16074 | INBOUND |
9/3/2017 12:02:24 AM | 12:02:24 AM | 12:04:40 AM | 136 | 4320 | INBOUND |
7/23/2017 12:03:00 AM | 12:03:00 AM | 12:04:34 AM | 94 | 16074 | INBOUND |
7/25/2017 12:03:00 AM | 12:03:00 AM | 12:04:41 AM | 101 | 3157 | INBOUND |
7/25/2017 12:03:00 AM | 12:03:00 AM | 12:06:06 AM | 186 | 16074 | INBOUND |
7/31/2017 12:03:00 AM | 12:03:00 AM | 12:03:03 AM | 3 | 16074 | INBOUND |
7/31/2017 12:03:00 AM | 12:03:00 AM | 12:04:30 AM | 90 | 16074 | INBOUND |
8/22/2017 12:03:00 AM | 12:03:00 AM | 12:03:32 AM | 32 | 3157 | INBOUND |
8/24/2017 12:03:00 AM | 12:03:00 AM | 12:03:03 AM | 3 | 16074 | OUTBOUND |
8/24/2017 12:03:00 AM | 12:03:00 AM | 12:04:57 AM | 117 | 2149 | INBOUND |
8/31/2017 12:03:00 AM | 12:03:00 AM | 12:05:27 AM | 147 | 16074 | INBOUND |
8/31/2017 12:03:00 AM | 12:03:00 AM | 12:11:22 AM | 502 | 16074 | INBOUND |
9/3/2017 12:03:17 AM | 12:03:17 AM | 12:06:13 AM | 176 | 3157 | INBOUND |
9/16/2017 12:03:31 AM | 12:03:31 AM | 12:08:10 AM | 279 | 30214 | INBOUND |
9/6/2017 12:03:51 AM | 12:03:51 AM | 12:04:42 AM | 51 | 11836 | OUTBOUND |
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:00 | 3 | 1 | 2 |
0:00:01 | 3 | 1 | 2 |
0:00:02 | 3 | 1 | 2 |
<Skipping rows till next transition...> | |||
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 |
<Skipping rows till next transition...> | |||
0:01:08 | 9 | 2 | 7 |
0:01:09 | 9 | 2 | 7 |
0:01:10 | 10 | 2 | 8 |
<Skipping rows till next transition...> | |||
0:01:15 | 10 | 2 | 8 |
0:01:16 | 11 | 2 | 9 |
<Skipping rows till next transition...> | |||
0:01:26 | 11 | 2 | 9 |
0:01:27 | 12 | 2 | 10 |
<Skipping rows till next transition...> | |||
0:01:40 | 12 | 2 | 10 |
0:01:41 | 13 | 2 | 11 |
<Skipping rows till next transition...> | |||
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 |
<Skipping rows till next transition...> | |||
0:01:59 | 17 | 2 | 15 |
0:02:00 | 22 | 3 | 19 |
<Skipping rows till next transition...> | |||
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 |
<Skipping rows till next transition...> | |||
0:02:19 | 22 | 3 | 19 |
0:02:20 | 21 | 3 | 18 |
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]
Solved! Go to Solution.
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
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
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.
Any suggestions? Appreciate any help I can get. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |