Showing Different Segments of a Trip

[DerekC]

I have a dataset that shows different Railcars moving across different routes and is captured line by line by what segement it is on.  I am wanting to create a couple columns that show the Origin City that the move started, the interchange locations and the Destination City Regardless of the segment it is on. 

I want to show LAKOTA is the Begin City on all segments because it has the lowest segement no and BLISSFIEL the End City on all segments because it has the highest segment no.  I am also not sure if there is a way to show Chicago as Interchange 1, Lima as Interchange 2 and Riga as Interchange 3 on all lines as well.  Any help would be greatly appreciated! 

Example below:

[Greg_Deckler]

@DerekC I feel like I did this once, I think it is something called Transitive Closure: Transitive Closure - Microsoft Power BI Community

[DerekC]

@Greg_Deckler This is close, but "FROM" being the Begin City and "END" being the End City doesn't always give me the 1st Begin City from the minimun segment like I am looking for.  Is there a bit more context that can be applied? 

[Greg_Deckler · Accepted solution]

@DerekC Well, in your case you may not need something as fancy as Transitive Closure. You could do this in DAX columns or probably PQ columns as well. Let's see, in DAX it would be something like this:

Begin City Column =
  VAR __CarNo = [car_no]
  VAR __Table = FILTER('Table',[car_no] __CarNo)
  VAR __Segment = MINX(__Table,[segment_no)
RETURN
  MAXX(FILTER(__Table,[segment_no] = __Segment),[Begin City])

End City Column =
  VAR __CarNo = [car_no]
  VAR __Table = FILTER('Table',[car_no] __CarNo)
  VAR __Segment = MAXX(__Table,[segment_no)
RETURN
  MAXX(FILTER(__Table,[segment_no] = __Segment),[End City])