Find entry whose date time is less than but closest to fact table

[brett_walton]

I am looking for logic to determine how to pull the following data 

I have a fact table that has transaction date and time and computer per transaction the second table is the device table showing who was logged into the device (there is no log out). My intended logic will be to link date, and device ID and then find the time that is the first time found less the log in time from the fact table . So the first entry I want to determine BOB was logged and second ROB

                     Date      Time        Device

Example :      1/1/17   08:10:53   RFGN3550            

                     1/1/17   10:13:40   RFGN3560

I have a device Log table that says who was logged into that device 

                 Log in Date    Time       Device          Person

Example:           1/1/17   08:00:00   RFGN3550     BOB 

                         1/1/17  09:00:00    RFGN3550     MARY  

                         1/1/17  09:00:00    RFGN3560     SAM

                         1/1/17  10:00:00    RFGN3550    ROB

[OwenAuger · Accepted solution]

This seems to have been deemed an M question, but here is one of those "dime a dozen" DAX solutions:

PBIX sample

 Calculated column in Transactions:

Person = 
VAR TransactionDateTime = Transactions[DateTime]
RETURN
    CALCULATE (
        //Arbitrary tie-breaker
        FIRSTNONBLANK ( Logins[Person], 0 ),
        CALCULATETABLE (
            LASTNONBLANK ( Logins[DateTime], 0 ),
            Logins[DateTime] <= TransactionDateTime
        )
    )

Owen 🙂