Passenger distribution on a route, range intersection

[sebastiangaga]

Hi all,

I have a table with train tickets like this:

ticketId	departureName	departureDistance	arrivalName	arrivalDistance
556822	Paris	0	Rome	1400
558874	Paris	0	Lion	700
855462	Lion	500	Milan	900
547798	Milan	900	Rome	1400
900015	Lion	500	Milan	900
125569	Paris	0	Milan	900

The route is one directional (for simplicity) and the station distances are related to route's start (here Paris).

I need to compute a table that shows how many passenger are on each segment:

segmentStart	segmentEnd	passengers
Paris	Lion	3
Lion	Milan	4
Milan	Rome	2

With SQL Server I generated all the possible segments for a route and then I counted tickets besed on ticket and segment range intersection.

Can I do this with DAX or just import the View (which might get quite large for Dual or Import mode)?

[Greg_Deckler]

Off the cuff, this seems like it has an element of Transitive Closure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388