Forum Discussion
Solved
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 comb...
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]))
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]))
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]))