Count of Agents Login

[ashaikh]

Hello All,

I have a table which says when we get a call and a table with Agent Login Time and Logout Time

CallTable :

    CallId | CallTime            |
    1      | 2019-04-01 09:00 AM |
    2      | 2019-04-01 09:30 AM |

AgentLoginLogout :

    Date       | AgentId | LoginTime          | LogoutTime          | AgentTeam |
    2019-04-01 | 1       | 2019-04-03 3:00 AM | 2019-04-03 10:00 AM | TeamA     |
    2019-04-01 | 2       | 2019-04-03 9:15 AM | 2019-04-03 5:15 PM  | TeamA     |
    2019-04-01 | 3       | 2019-04-03 9:15 AM | 2019-04-03 5:15 PM  | TeamB     |

So what I want to calculate is how many agents are logged in when each call came in. Final result will look like below

FinalResult :

    CallId | CallTime            | AgentsOnline
    1      | 2019-04-01 09:00 AM | 1
    2      | 2019-04-01 09:30 AM | 2

Thanks in Advance

[Zubair_Muhammad · Accepted solution]

@ashaikh 

Try this calculated column

AgentsOnline =
COUNTROWS (
    FILTER (
        AgentLoginLogout,
        CallTable[CallTime] >= AgentLoginLogout[LoginTime]
            && CallTable[CallTime] <= AgentLoginLogout[LogoutTime]
    )
)

[ashaikh]

@Zubair_Muhammad I am trying  to use this query and I am just getting working on it...

And its taking long time to execute I guess. Is there any other way to do it.