USERELATIONSHIP Many to Many Help

[mcumis]

Hi

I've got two tables

A =  a list of trains journeys, with departure/arrival time, date, location etc Each train has a non-unique ID (set) and a unique one (Headcode). There is a unique combination of time/date/set, but each set is used for multiple journeys each day.

B = a list of coffee machines cleaning 'events', with multiple timestamps for each set on each day. 

What I am trying to do is find out which journey the 'event' occurred on, and get the unique ID (headcode), based on the set being the same in tables A and B (many to many), and also the time in table B being between the departure and arrival time recorded in table A. 

Is that possible?

[mcumis · Accepted solution]

If anyone comes across this in future, I think I've solved this myself with:

Column = CALCULATE(
LASTNONBLANK('Table A'[head code],'Table A'[head code]),
USERELATIONSHIP('Table A'[set],'Table B'[set]),
FILTER('Table A', 'Table B'[set] = 'Table A'[set]
&&('Table B'[Time] >= 'Table A'[Departure Time]-TIME(0,20,0))
&& 'Table B'[Time] <= ('Table A'[Arrival Time]+TIME(0,20,0))
&& 'Table B'[Date] = 'Table A'[date]))