Power BI - Finding records within time span of another record

Question

I have a Jobs table with a start and end datetime, and I have an Events table with the datetime that the event happened.

I'm trying to discover why some jobs take longer than others, so I'd like to filter the page to a specific job and see which events occurred after the start time and before the end time. I'm not sure how to set up this relationship... any advice?

I have both a date and a time dimension dataset if that's relevant.

Example (from a different domain than my problem set):

Job Data Set

Name             - Start              - End
Print Document 1 - 2018-06-19 1:20 PM - 2018-06-19 1:22 PM
Print Document 2 - 2018-06-19 2:20 PM - 2018-06-19 2:55 PM
Print Document 3 - 2018-06-19 3:20 PM - 2018-06-19 3:22 PM

Event Data Set

ID - Name          - DateTime
1  - Printer Jam   - 2018-06-15 1:20 PM
2  - Doorbell Rang - 2018-06-19 2:23 PM
3  - Printer Jam   - 2018-06-19 2:35 PM
4  - Out of toner  - 2018-06-20 1:20 PM

I want to associate Print Document 2 with Event IDs 2 and 3 so that I can use a filter to display only the relevant events, or so that I can create a measure to count the number of events per job.

I can't add the Job Name to the Event data source because events are not necessarily occurring during a job or in direct cause by a job. In my original domain, I need to see what other unrelated activities are happening elsewhere in the system to determine why the job is taking too long.

score 3 · Accepted Answer · answered Jun 19 '18 at 21:35

You can merge in the Events table to the Jobs table in the query editor.

Create a custom column that pulls in any rows from the Events table where the DateTime field is between the Start and End values.

= Table.AddColumn(#"[Previous Step Name Here]", "Custom",
      (J) => Table.SelectRows(Events, (E) => E[DateTime] >= J[Start] and E[DateTime] <= J[End]))

Once that custom column is created, expand that column (which contains a table for each row) to associate any events that occurred during the printing.

The result should look like this:

You can duplicate this by pasting the following into the Advanced Editor:

let
    JobsSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzCtRcMlPVjBU0lEyMjC00DUw0zW0VDC0MjJQCPDFEDQCCcbqIOs0QlVkhE2nkZWpKaZOY1RFxth0GsPsjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t]),
    Jobs = Table.TransformColumnTypes(JobsSource,{{"Name", type text}, {"Start", type datetime}, {"End", type datetime}}),
    EventsSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyswrSS1S8ErMBfKMDAwtdA3MdA1NFQytjAwUAnyVYnWilYyAUi75+UVJqTk5CkGJeenISi0VjKyMjGFKjXGbCVZoClNoApTyLy1RyE9TKMnPSy1CUgm0GG57LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t, DateTime = _t]),
    Events = Table.TransformColumnTypes(EventsSource,{{"ID", Int64.Type}, {"Description", type text}, {"DateTime", type datetime}}),
    #"Added Custom" = Table.AddColumn(Jobs, "Custom", (J) => Table.SelectRows(Events, (E) => E[DateTime] >= J[Start] and E[DateTime] <= J[End])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"ID", "Description", "DateTime"}, {"ID", "Description", "DateTime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"ID", Int64.Type}, {"Description", type text}, {"DateTime", type datetime}})
in
    #"Changed Type"

I just learned so much. This was my first foray into the advanced editor. Thank you! — Alex Eisenhart, Jun 20 '18 at 01:30

Power BI - Finding records within time span of another record

1 Answers1