cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DerekC
Frequent Visitor

Showing Different Segments of a Trip

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:

DerekC_1-1630427584041.png

 

1 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])


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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? 

@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])


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!