- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Find entry whose date time is less than but closest to fact table
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 07:55 AM - edited 01-26-2017 07:56 AM
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
Solved! Go to Solution.
Accepted Solutions
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 07:51 PM
This seems to have been deemed an M question, but here is one of those "dime a dozen" DAX solutions:
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
Re: Find entry whose date time is less than but closest to fact table
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-27-2017 10:52 AM - edited 01-27-2017 10:56 AM
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!
All Replies
Re: Find entry whose date time is less than but closest to fact table
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 11:32 AM - edited 01-26-2017 11:34 AM
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.
Re: Find entry whose date time is less than but closest to fact table
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 12:01 PM - edited 01-26-2017 12:32 PM
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 12:26 PM
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 12:33 PM
Re: Find entry whose date time is less than but closest to fact table
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 12:40 PM - edited 01-26-2017 12:40 PM
@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.
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 02:33 PM
Happy to hand-over :-)
Did I answer your question? Mark my post as a solution!
Proud to be a Datanaut!
Imke Feldmann
How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 03:06 PM
@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!
So don't hand over anything yet!
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 06:01 PM
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...
Re: Find entry whose date time is less than but closest to fact table
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-26-2017 07:51 PM
This seems to have been deemed an M question, but here is one of those "dime a dozen" DAX solutions:
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