Join tables on "greater than" condition

[Anonymous]

Hello,

I have two tables that look like this:

I need "Time2" from "Table 2" in "Table 1", on these conditions:

a. Table 1 [ID] = Table 2[ID]

b. Table 1 [Date] = Table 2[Date]

c. Table 2[Time2] is the smallest value that fulfils Table 2[Time2] > Table 1[Time1]

For the example of the two tables above, this is the expected result:

Can you please help me to do this? 

[sturlaws · Accepted solution]

hi @Anonymous 

how about creating a calculated column like this in table 1:

Time 2 =
CALCULATE (
    MIN ( Table2[Time2] );
    FILTER (
        ALL ( Table2 );
        Table2[Date ] = Table1[Date]
            && Table2[ID] = Table1[ID]
            && Table2[Time2] > Table1[Time 1]
    )
)

[Anonymous]

Thanks a lot, that is exactly what I needed!

For those that want to use it in the future, just a comment on two small syntax errors: the ";" should be ",". Otherwise the query works directly.