cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brett_walton
Helper II
Helper II

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

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

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

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

PBIX sample

 

 

Capture.PNG

 

 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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Sean
Community Champion
Community Champion

@brett_walton

I suspect you've modified @OwenAuger's formula to only look at your Time columns!

 

Note that he checks a Date/Time column.

 

So to address this create a Date-Time Column in both tables like this

Date-Time = [Date]&" "&[Time]

Both of these columns will default to Text - so you need to convert them to Data Type - Date/Time - again in both tables.

 

This should resolve it!

 

Good Luck! Smiley Happy

View solution in original post

15 REPLIES 15
Sean
Community Champion
Community Champion


@brett_walton wrote:




                     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


 

Sam is the only one logged in device 3560!

 

Maybe you meant to have Rob log in device 3560 - but as you've written it the second should be Sam

OwenAuger
Super User
Super User

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

PBIX sample

 

 

Capture.PNG

 

 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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Thank you Owen, that worked well.  I perferred the DAX solution over M as I actaully understand what you did. Smiley Very Happy  (work for a smaller company so I am not a full time data person) . 

 

One other huddle I need to cross 

 

If I have a transaction at 1/27/17 @ 00:10:00 AM AND the log in record am looking for is 1/26/17  22:40:00 (so they logged in the previous day) - is there another condition I can add to this to account for that ? 

 

Thank You Again 

Brett 

Sean
Community Champion
Community Champion

@brett_walton

I suspect you've modified @OwenAuger's formula to only look at your Time columns!

 

Note that he checks a Date/Time column.

 

So to address this create a Date-Time Column in both tables like this

Date-Time = [Date]&" "&[Time]

Both of these columns will default to Text - so you need to convert them to Data Type - Date/Time - again in both tables.

 

This should resolve it!

 

Good Luck! Smiley Happy

Thank you , I noticed the merge I just didnt do 2 things 1) change my data type 2) open my device log fact tbale for mutiple days 

Sean
Community Champion
Community Champion

The other thing this community will agree on @OwenAuger's solutions definitely do not fall in the "dime a dozen" category!

 

BTW Owen's sample file above includes Marcel's solution as well Smiley LOL @MarcelBeug

 

@brett_waltonyou can create the Device table in Owen's solution in the Query Editor

Duplicate the table that has ALL Devices (right-click the table)

Rename It

Remove All Other Columns

Remove Duplicates (right-click the column)

Close and Apply

 

Good Luck! Smiley Happy

MarcelBeug
Community Champion
Community Champion

Maybe DAX would be more appropriate, but in M (Edit Queries) it can be done with the following code.

I combined dates and times in each table to the field "DateTime".

Steps:

  • Merge table Logins with Transactions (Logins are nested tables in Transctions table)
  • Add a column with nested Logins with DateTime <= Transaction.DateTime
  • Remove original Logins column
  • Select latest Logins
  • Expand the nested table, returning only the name
  • Apply type text to the Name 

 

let
    Source = Table.NestedJoin(Transactions,{"Device"},Logins,{"Device"},"Logins",JoinKind.LeftOuter),
    AddedLoginsBeforeTransaction = Table.AddColumn(Source, "Logins before Transaction", (CT) => Table.SelectRows(CT[Logins], each [DateTime]<=CT[DateTime])),
    RemovedLogins = Table.RemoveColumns(AddedLoginsBeforeTransaction,{"Logins"}),
    LatestLogin = Table.TransformColumns(RemovedLogins, {{"Logins before Transaction", each Table.SelectRows(_, let latest = List.Max(_[DateTime]) in each _[DateTime] = latest)}}),
    NameFromLogin = Table.ExpandTableColumn(LatestLogin, "Logins before Transaction", {"Person"}, {"Person"}),
    Typed = Table.TransformColumnTypes(NameFromLogin,{{"Person", type text}})
in
   Typed

 By the way, the second is SAM, not ROB.

 

Specializing in Power Query Formula Language (M)

Thank you .. Will give it a try but for point of clarity of what I am looking for the second one is Rob . The transaction was at 10:13 and he logged into the device at 10:00 so it was he who posted it not SAM . 

Sean
Community Champion
Community Champion

Folks it seems @MarcelBeug maybe giving our undisputed M expert @ImkeF a run for her money! Smiley Happy

 

MarcelBeug
Community Champion
Community Champion

Thanks @Sean, @ImkeF is aware. Smiley Very Happy

Specializing in Power Query Formula Language (M)

Happy to hand-over 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Sean
Community Champion
Community Champion

@ImkeFif we can poll this community one thing will be certain!

When it comes to DAX there are many so called "experts"

But when it comes to M everyone will vote for you! Smiley Happy

So don't hand over anything yet!

MarcelBeug
Community Champion
Community Champion

When it comes to theoretical M knowledge, I think @ImkeF can more or less compete by now.

With regard to practical experience I'm definitely lagging behind.

But I have that screencasting software as a card in my sleeve.

And my userid is with a capital M... Smiley Tongue

Specializing in Power Query Formula Language (M)
Sean
Community Champion
Community Champion

MarcelBeug
Community Champion
Community Champion

@Sean Especially with Power Query I think that videos have enormous added value, as code is typically generated by clicking the right buttons on the menus.

 

A good example in https://community.powerbi.com/t5/Desktop/Parsing-Embedded-JSON/m-p/118765#M50133 in which the solution is basically simple and straightforward, but it requires a lot of clicks.

 

Even with a video it takes 3 minutes to explain.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors