Matching data from a date range and an ID across two tables

[Anonymous]

I am working on a report that has two tables like the ones shown

Table 1

User	LoginTime	LogoutTime	SessionKey
A	11/12/2019 12:00	11/12/2019 15:00	key1
B	11/12/2019 7:00	11/12/2019 10:00	key2
A	11/12/2019 17:00	11/12/2019 18:00	key3

Table 2

User	ActivityTime
A	11/12/2019 12:30
A	11/12/2019 14:00
B	11/12/2019 8:00
A	11/12/2019 17:30
B	11/12/2019 9:00

I need a way to match each row in Table 2 with a row in Table 1 by matching the User as well as making sure the ActivityTime is between the LoginTime and LogoutTime. Is there a way to do this?

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create column in table2 like DAX below.

SessionKey_matched =
MAXX (
    TOPN (
        1,
        FILTER (
            'Table 1',
            'Table 1'[User] = 'Table 2'[User]
                && 'Table 1'[LogoutTime] > 'Table 2'[ActivityTime]
                && 'Table 1'[LoginTime] <= 'Table 2'[ActivityTime]
        ),
        'Table 1'[LoginTime] && 'Table 1'[LogoutTime], DESC
    ),
    'Table 1'[SessionKey]
)

Best Regards,

Amy

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