How to get latest previous event row from another table

[vangelem]

Hi all,

First question on this forum for me 🙂

I'm facing a blocking situation, for which I've been struggling quite some time without managing to fix it.

I have two tables (bench_tab and instrument_tab) that have two fields in common (bench and user). They also have event and date (actually datetime) fields. The bench_tab has in addition a user field and the instrument_tab an instrument field, as shown here: 

instrument_tab

bench_tab

I need to connect each row in the instrument_tab table to the corresponding user field in the bench_tab table according to those rules:

• The two tables are related by the bench/pc pair
• Only the bench_tab rows with an "open" event are used for the correspondance
• The matching row (if it exists) in bench_tab should have its date immediatly preceeding the date in the instrument_tab row

Using the example tables shown above:

• For the first two row #0 (and row #1 as well) in instrument_tab, there is no ambiguity and the matching row in bench_tab is row #0, that is user is "user_1". 
• For row #2 (and #3) in instrument_tab, there are multiple rows in bench_tab with an "open" event. One (#13) has a datetime greater than the datetimes in instrument_tab, so it should be ignored. There are two other options: rows #5 and #9, with different datetimes. The one with the immediatly preceeding datetime and then matching user is row #5 with user "user_2"
• For row #4 (and #5) in instrument_tab, there are no rows matching the search conditions, so the user should be set to null

As a result, here is what I should get:

I tried -using this forum- to get that to work and managed to get the result using Power Query by merging/grouping the tables with a nested search function to find the right matching dates. The problem is that it works fine on this small example but, once I switch to the real production data (with probably 200000+ rows in each table, and increasing), the processing hangs forever (i.e. more than 1 hour, not practical at all!). I have been trying to do it differently, by creating relationships between the tables and writting DAX commands -thought it would make it faster-, but got blocked by the many-to-many relationship that prevents functions such as RELATED to work (and mostly because my DAX skills are terrible :-).

So what I would like to achieve, if possible, is a fast and efficient way to make that correspondance and find the matching user, wether it is done in DAX or Power Query.

And here is the code (locale should be set to "French (France)" to correctly load the datetimes), if that helps reproducing the problem (I couldn't find a way to attach the .pbix file btw):

bench_tab

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BCoMwEIXhq5SsBZOZSSO5ikhpJdBF0aD2/hW1MjZaZ/Xe5l98ZakeoamfN6MyFetp3n3optPG0IwDNtdFDnAx1oP22qgq+5f1w70bfjocO33atXGTXT1pb4uTrH61fdh0Lulg7uDbAdPRkmmX6PayVUfHuv1u1tGxbi9bdSTXIdOhXIdch3IdMh3Kdch1KNcR0zm5jrjOyXXEdE6uI65zia76AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [bench = _t, pc = _t, user = _t, event = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}})
in
    #"Changed Type"

instrument_tab

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc3RCkBAEIXhV9FeKzNnV8u8ijYhxQ1i3z+sWsUFV2ea+vqrSo3T5mtWqWr7qRvCtXRhkGdUZCAg4Vw0CeH4br5ZvXLpf2lI2AQ5LzdEhLjgOTARUiEMYftOfspSYIXsK6kj1Bc8B+WP5Kd8Jt0O", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [instrument = _t, bench = _t, pc = _t, date = _t, event = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"bench", type text}, {"pc", type text}, {"instrument", type text}, {"date", type datetime}})
in
    #"Changed Type"

find_latest_bench_rows

(tab as table, diff_date as text) =>
let
    max_diff_date = List.Min(Table.Column(tab, diff_date)),
    to_keep=Table.TransformRows(tab, each Record.Field(_, diff_date) = max_diff_date),
    result=Table.FromColumns(Table.ToColumns(tab)&{to_keep}, Table.ColumnNames(tab)&{Text.Combine({diff_date, "_to_keep"})})
in
    result

 instrument_user_tab

let
    Source = Table.NestedJoin(instrument_tab, {"bench", "pc"}, bench_tab, {"bench", "pc"}, "bench_join", JoinKind.LeftOuter),
    #"Expanded bench" = Table.ExpandTableColumn(Source, "bench_join", {"user", "event", "date"}, {"bench.user", "bench.event", "bench.date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded bench", each [bench.event] = null or [bench.event] = "open"),
    #"Added date no null" = Table.AddColumn(#"Filtered Rows", "bench.date_no_null", each if [bench.date] = null then #datetime(2000, 1, 1, 0, 0, 0) else [bench.date]),
    #"Added Custom" = Table.AddColumn(#"Added date no null", "diff_date", each [date]-[bench.date_no_null]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [diff_date] >= #duration(0, 0, 0, 0)),
    output = Table.Group(#"Filtered Rows1", {"instrument", "bench", "pc", "date","event"}, {{"all", each find_latest_bench_rows(_, "diff_date")}}),
    #"Expanded all" = Table.ExpandTableColumn(output, "all", {"bench.user", "bench.date_no_null", "diff_date", "diff_date_to_keep"}, {"user", "all.bench.date_no_null", "all.diff_date", "all.diff_date_to_keep"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded all", each ([all.diff_date_to_keep] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"all.bench.date_no_null", "all.diff_date", "all.diff_date_to_keep"})
in
    #"Removed Columns"

 Thanks!

Matt

[Anonymous · Accepted solution]

Hi  @vangelem ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _count=
COUNTX(FILTER(ALL('bench_tab'),
'bench_tab'[bench]=MAX('instrument_tab'[bench])&&'bench_tab'[pc]=MAX('instrument_tab'[pc])&&'bench_tab'[event]="open"),[date])
var _date1=
MAXX(FILTER(ALL(bench_tab),
'bench_tab'[bench]=MAX('instrument_tab'[bench])&&'bench_tab'[pc]=MAX('instrument_tab'[pc])&&'bench_tab'[event]="open"
),[date])
var _date2=
MAXX(FILTER(ALL(bench_tab),
'bench_tab'[bench]=MAX('instrument_tab'[bench])&&'bench_tab'[pc]=MAX('instrument_tab'[pc])&&'bench_tab'[event]="open"&&
'bench_tab'[date]<=MAXX(FILTER(ALL(instrument_tab),
'instrument_tab'[bench]=MAX('instrument_tab'[bench])&&'instrument_tab'[event]="stop"),[date])
),[date])
var _if=
IF(
    _count<=1,
CALCULATE(MAX('bench_tab'[user]),
FILTER(ALL(bench_tab),
'bench_tab'[bench]
=MAX('instrument_tab'[bench])&&'bench_tab'[pc]=MAX('instrument_tab'[pc])&&
'bench_tab'[date]=
_date1)),
CALCULATE(MAX('bench_tab'[user]),
FILTER(ALL(bench_tab),
'bench_tab'[bench]
=MAX('instrument_tab'[bench])&&'bench_tab'[pc]=MAX('instrument_tab'[pc])&&
'bench_tab'[date]=
_date2))
)
return
_if

2. Result:

If you need pbix, please click here.

How to get latest previous event row from another table.pbix

Best Regards,

Liu Yang

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