Get max concurrent call count by the hour

[pstew12]

Hi Everyone,

I have call logs where I am trying to find the overlapping calls to figure out the max concurrent calls I have every hour. 

Data Example:

JoinTime	LeaveTime
3/8/18 8:01 AM	3/8/18 9:38 AM
3/8/18 8:04 AM	3/8/18 9:38 AM
3/8/18 9:03 AM	3/8/18 9:39 AM
3/8/18 9:09 AM	3/8/18 9:39 AM
3/8/18 9:23 AM	3/8/18 9:29 AM
3/8/18 9:23 AM	3/8/18 9:29 AM
3/8/18 9:30 AM	3/8/18 9:56 AM

What I need to come up with is the following

Hour of day (24hr)	Max Calls
0	1
1	2
2	2
3	8
4	2
5	3
6	7
7	0
8	4
9	4
10	3
11	3
12	4
13	3
14	5
15	3
16	3
17	3
18	4
19	5
20	4
21	3
22	2
23	3

Any ideas to point me in the right direction? I don't even know where to start.

[Syndicate_Admin]

Hello @pstew12!

I am currently encountering the same problem you had, I need to calculate the maximum number of simultaneous calls for each hour of the day. Did you manage to get it out?

Thanks in advance.

Greetings,

Bibi

[v-yuta-msft · Accepted solution]

@pstew12 ,

Firstly generate a 24h series table using dax:

Hours = GENERATESERIES(0, 23, 1)

Then you can create a table like below, you can add other conditions in the switch expression.

Result Table = 
SELECTCOLUMNS (
    Hours,
    "Hour of day(24hr)", Hours[Value],
    "Max Calls", SWITCH (
        Hours[Value],
        8, CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                NOT (
                    HOUR ( 'Table'[JoinTime] ) >= 9
                        || HOUR ( 'Table'[LeaveTime] ) < 8
                )
            )
        ),
        9, CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                'Table',
                NOT (
                    HOUR ( 'Table'[JoinTime] ) >= 10
                        || HOUR ( 'Table'[LeaveTime] ) < 9
                )
            )
        )
    )
)

Please also refer to the pbix file attached.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I dont want to count mutually exclusive count repeatedly. For eg. if there are mutually exclusive calls like

C1 from 8:00 to 8:05 ,

C2 from 8:06 to 8:10 

C3 from 8:11 to 8:15.

By the method explained above for 8, it will give count =3 but I want the count as 1 

i.e. excluding mutually exclusive calls because they will be sharing the resource or license

[pstew12]

Ok, If I want to filter by date how can I make this dynamic?

[v-yuta-msft]

@pstew12 ,

I'm afraid this couldn't be achieved because power bi doesn't support dynamic calculate table.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

You need something like Open Tickets:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[pstew12]

Thats helpful but how can I do it over a 24 hour period?