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
YouTube Channel! Microsoft Hates Greg
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
YouTube Channel! Microsoft Hates Greg
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
YouTube Channel! Microsoft Hates Greg
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!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.