Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Solved! Go to 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])
@DerekC I feel like I did this once, I think it is something called Transitive Closure: Transitive Closure - Microsoft Power BI Community
@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])
User | Count |
---|---|
119 | |
66 | |
66 | |
56 | |
50 |
User | Count |
---|---|
176 | |
83 | |
68 | |
65 | |
54 |