DAX - Multiple Filters with Multiple Tables and Columns

Question

I've been struggling a bit with this and hopefully you can help me.

So I have a table with employee Login sessions with the following relevant columns: [Employee ID], [LoginTime], [LogoutTime], [Status]

And another table with Incident IDs and its respective [Acknowledgement DateTime] and which [Employee ID] is handling it.

Now I'm looking to verify which Login[Status] the employee was in when he accepted the Incident.

So, I was thinking about something along these lines:

Calculate(Login[Status];
    Filter(Login;Login[Employee ID]=Incident[Employee ID];
    Filter(Login;
       and(Login[LoginTime<=Incident[AckDateTime];Login[LogoutTime>=Incident[AckDateTime])

So far I don't believe I'm getting the desired results, do you have any tips on how I should go about this?

Thanks in advance, Cheers!

Edit: Thanks for the reply and apologies, it was my first question here.

The Login table should look like this:

Employee ID LoginTime LogoutTime Status Colossus 26/09/2018 13:05 26/09/2018 13:25 Available Nightcrawler 26/09/2018 11:30 26/09/2018 12:10 Research

And the Incident one, like this:

Incident ID Employee ID AckDateTime 12456324 Colossus 26/09/2018 13:24 45754121 Cyclops 26/09/2018 12:00 78452135 Nightcrawler 26/09/2018 11:15

I've tried with other similar formulas which seem to make sense (to me at least) but PowerBI just breaks down with lack of sufficient memory issues. The same happened with your suggested formula.

I'm working with a 200k+ row table with several columns so I'm looking to lighten this formula up, would this be possible with filters?

I mean, do they apply sequentially (narrowing down with each step) or does calculate apply all filters at once?

Answer 1

Please try to always provide a MCVE as this will help others help you!

If you add a New Calculated Column to your Incident Table you should be able to determine your Employee Status with something like this:

Employee Status =
    CALCULATE (
        MAX ( 'Login'[Status] ),
        FILTER (
            'Login',
            AND('Incident'[AckDateTime] > 'Login'[LoginTime],
                'Incident'[AckDateTime] <= 'Login'[LogoutTime]
            )
        )
    )