Count of Possible Calls

[jcawley]

Good morning,

I'm trying to find the count of phone calls a user could've answered. I've had this work successfully using calculated columns. But I'd like to avoid that if a measure is possible.

Let's say we are looking at user 'Jessica' and want to know how many possible calls they could've taken. This should be all calls they either took (user = Jessica, call type = inbound) or passed up that were inbound, the call was to the same answer location, and they were not busy on the phone during that time.

So using the following table:

Call Date	User	Call Time	Call End Time	Duration	Call Type	Answer Location
12/5/2023	Jessica	8:57:52 AM	8:58:49 AM	0:00:57	Inbound	1
12/5/2023	Sheena	8:58:11 AM	8:58:25 AM	0:00:14	Inbound	2
12/5/2023	Whitney	8:58:18 AM	9:00:46 AM	0:02:28	Internal	2
12/5/2023		8:58:31 AM	8:59:32 AM	0:01:01	Outbound	2
12/5/2023		8:58:37 AM	9:02:25 AM	0:03:48	Outbound	1
12/5/2023		8:58:47 AM	9:00:15 AM	0:01:28	Outbound	1
12/5/2023		8:59:26 AM	9:00:49 AM	0:01:23	Inbound	2
12/5/2023		8:59:34 AM	9:00:59 AM	0:01:25	Inbound	1
12/5/2023		8:59:36 AM	9:02:31 AM	0:02:55	Inbound	1
12/5/2023		8:59:37 AM	9:00:37 AM	0:01:00	Outbound	2
12/5/2023		8:59:51 AM	9:01:38 AM	0:01:47	Outbound	2
12/5/2023	Jessica	9:00:11 AM	9:02:55 AM	0:02:44	Inbound	1
12/5/2023	Tatyana	9:00:12 AM	9:05:43 AM	0:05:31	Inbound	2
12/5/2023		9:00:26 AM	9:03:29 AM	0:03:03	Outbound	2
12/5/2023		9:00:41 AM	9:37:45 AM	0:37:04	Outbound	2
12/5/2023		9:00:57 AM	9:03:58 AM	0:03:01	Outbound	2
12/5/2023	Angelica	9:00:57 AM	9:04:03 AM	0:03:06	Inbound	1
12/5/2023		9:01:21 AM	9:03:13 AM	0:01:52	Internal	1

I would see that Jessica could've answered the phone 4 times. She answered twice (green text), and had two calls that went unanswered (red text) where they were not on another phone call.

Is this possible? Any help would be greatly appreciated!

[v-shex-msft]

HI @jcawley,

You can try to use the following measure formula to calculate the call counts based on groups:

formula = 
VAR currUser =
    SELECTEDVALUE ( 'Table'[User] )
VAR currStart =
    MAX ( 'Table'[Call Time] )
VAR currEnd =
    MAX ( 'Table'[Call End Time] )
VAR answered =
    CALCULATE (
        COUNT ( 'Table'[Call Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Call Time] <= currStart
                && [User] = currUser
        ),
        VALUES ( 'Table'[Call Date] ),
        VALUES ( 'Table'[Call Type] ),
        VALUES ( 'Table'[Answer Location] )
    )
VAR notanswered =
    CALCULATE (
        COUNT ( 'Table'[Call Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Call Time] <= currStart
                && OR ( [Call Time] <= currEnd, [Call End Time] >= currStart )
                && [User] = ""
        ),
        VALUES ( 'Table'[Call Date] ),
        VALUES ( 'Table'[Call Type] ),
        VALUES ( 'Table'[Answer Location] )
    )
RETURN
    answered + notanswered

Regards,

Xiaoxin Sheng

[jcawley]

Thank you for the attempt! This does not quite work though.

The first variable works like a charm! The problem lies in the second part which I suppose is most of the problem. 

I've added more users to this table:

https://docs.google.com/spreadsheets/d/1Wh24yBd7a1ZqrRMphKQgPa8io4dZe_tTerLfe9wwdlo/edit#gid=0

The code sort of works, but it needs to be dependent on location and whether or not the call is inbound.

Location must equal location (in this instance 1) and a call can't be missed if it was outbound or internal, so only a missed inbound would count!

Any additional help would be greatly appreciated!

[jcawley]

I think one big thing I left out is user location. For this Example "Jessica" is associated with location 1 only, not 2.

[jcawley]

@v-shex-msft We really appreciate your help and if there is anyway you could give this a second look we would forever gratefull!