Forum Discussion
USERELATIONSHIP Many to Many Help
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?
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]))
1 Reply
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]))