Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors