Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to calculate total travel time based on line and station filter
Example : Departure station (A1) Arrival, station(A3) and line red so travel time =24 min
How make this in power bi
Line | Departure station | Arrival station | Travel Time (min) |
Red | A1 | A2 | 12 |
Red | A2 | A3 | 12 |
Red | A3 | A4 | 12 |
Red | A4 | A5 | 12 |
Red | A5 | A6 | 12 |
Red | A6 | A7 | 12 |
Red | A7 | A8 | 12 |
Red | A8 | A9 | 12 |
Red | A9 | A10 | 12 |
Red | A10 | S1 | 12 |
Red | S1 | S2 | 12 |
Green | A1 | A2 | 8 |
Green | A2 | A3 | 8 |
Green | A3 | A4 | 8 |
Green | A4 | A5 | 8 |
Green | A5 | A6 | 8 |
Green | A6 | A7 | 8 |
Green | A7 | A8 | 8 |
Green | A8 | A9 | 8 |
Green | A9 | A10 | 8 |
Green | A10 | F1 | 8 |
Green | F1 | F2 | 8 |
Blue | A1 | A2 | 7 |
Blue | A2 | A3 | 7 |
Blue | A3 | A4 | 7 |
Blue | A4 | A5 | 7 |
Blue | A5 | A6 | 7 |
Blue | A6 | A7 | 7 |
Blue | A7 | A8 | 7 |
Blue | A8 | A9 | 7 |
Blue | A9 | A10 | 7 |
Purple | A1 | A2 | 7 |
Purple | A2 | A3 | 7 |
Purple | A3 | A4 | 7 |
Purple | A4 | A5 | 7 |
Purple | A5 | A6 | 7 |
Purple | A6 | A7 | 7 |
Purple | A7 | A8 | 7 |
Purple | A8 | A9 | 7 |
Purple | A9 | A10 | 7 |
Solved! Go to Solution.
@MSAYED26 OK, try this one. Updated PBIX attached.
Travel Time Measure =
VAR __Departure = MAX([Departure station])
VAR __Arrival = MAX([Arrival station])
VAR __Line = MAX([Line])
VAR __DepartIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Departure ), [Index] )
VAR __ArrivalIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Arrival station] = __Arrival ), [Index] )
VAR __ArrivalIndexReverse = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Arrival ), [Index] )
VAR __Result =
SWITCH( __Line,
"Blue", SUMX( FILTER( 'Table', [Index] >= __DepartIndex && [Index] <= __ArrivalIndex ), [Travel Time (min)] ),
SUMX( FILTER( 'Table', [Index] < __DepartIndex && [Index] >= __ArrivalIndexReverse ), [Travel Time (min)] )
)
RETURN
__Result
Also, I don't think that your data posted is correct or something is weird. Red S1 goes to S2 to A4, to A3, A2, A1, A10 so I don't see how S1 to A10 is 12 minutes.
Hi,
Cannot make any sense of the filter conditions itself. For departure station A1, there is no Arrival station A3 at all. Please clarify/review carefully.
@MSAYED26 Looks like a Transitive Closure problem: Transitive Closure - Microsoft Fabric Community
But i want make filter with Departure station and filter with Arrival station then arrival time calculated
@MSAYED26 See what you think of this. PBIX is attached below signature. First, I added an Index in Power Query. Then I think after that you just need the calculated column "Travel Time" that you can easily turn into a measure like so:
Travel Time =
VAR __Departure = MAX([Departure station])
VAR __Arrival = MAX([Arrival station])
VAR __Line = MAX([Line])
VAR __DepartIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Departure ), [Index] )
VAR __ArrivalIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Arrival station] = __Arrival ), [Index] )
VAR __Result = SUMX( FILTER( 'Table', [Index] >= __DepartIndex && [Index] <= __ArrivalIndex ), [Travel Time (min)] )
RETURN
__Result
In my case, I created a calculated table like this:
Pairs =
VAR __Red = FILTER( 'Table', [Line] = "Red" )
VAR __RedPairs =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS( __Red, "Line", [Line], "Departure station", [Departure station], "Index", [Index]),
SELECTCOLUMNS( __Red, "Arrival station", [Departure station], "Index1", [Index] )
),
"__Keep", IF( [Index] > [Index1] || ( [Departure station] = [Arrival station] && [Departure station] <> "A1"), 0, 1 )
)
VAR __Blue = FILTER( 'Table', [Line] = "Blue" )
VAR __BluePairs =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS( __Blue, "Line", [Line], "Departure station", [Departure station], "Index", [Index]),
SELECTCOLUMNS( __Blue, "Arrival station", [Departure station], "Index1", [Index] )
),
"__Keep", IF( [Index] > [Index1] || ( [Departure station] = [Arrival station] && [Departure station] <> "A1"), 0, 1 )
)
VAR __Green = FILTER( 'Table', [Line] = "Blue" )
VAR __GreenPairs =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS( __Green, "Line", [Line], "Departure station", [Departure station], "Index", [Index]),
SELECTCOLUMNS( __Green, "Arrival station", [Departure station], "Index1", [Index] )
),
"__Keep", IF( [Index] > [Index1] || ( [Departure station] = [Arrival station] && [Departure station] <> "A1"), 0, 1 )
)
VAR __Purple = FILTER( 'Table', [Line] = "Blue" )
VAR __PurplePairs =
ADDCOLUMNS(
GENERATE(
SELECTCOLUMNS( __Purple, "Line", [Line], "Departure station", [Departure station], "Index", [Index]),
SELECTCOLUMNS( __Purple, "Arrival station", [Departure station], "Index1", [Index] )
),
"__Keep", IF( [Index] > [Index1] || ( [Departure station] = [Arrival station] && [Departure station] <> "A1"), 0, 1 )
)
VAR __Table = UNION( __RedPairs, __BluePairs, __GreenPairs, __PurplePairs )
VAR __Result = SELECTCOLUMNS( FILTER( __Table, [__Keep] = 1 ), "Line", [Line], "Departure station", [Departure station], "Arrival station", [Arrival station] )
RETURN
__Result
And then a calculated column in that table like this:
Travel Time =
VAR __Departure = [Departure station]
VAR __Arrival = [Arrival station]
VAR __Line = [Line]
VAR __DepartIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Departure ), [Index] )
VAR __ArrivalIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Arrival station] = __Arrival ), [Index] )
VAR __Result = SUMX( FILTER( 'Table', [Index] >= __DepartIndex && [Index] <= __ArrivalIndex ), [Travel Time (min)] )
RETURN
__Result
Thanks alot it work but need something more , In the photo below the direction of blue from A1 to A2 so the calculation right, but other line the direction is The opposite, Can you help me .
@MSAYED26 I haven't tested this but I *think* you just need to reverse your comparison depending on the Line:
Travel Time =
VAR __Departure = MAX([Departure station])
VAR __Arrival = MAX([Arrival station])
VAR __Line = MAX([Line])
VAR __DepartIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Departure ), [Index] )
VAR __ArrivalIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Arrival station] = __Arrival ), [Index] )
VAR __Result =
SWITCH( __Line,
"Blue", SUMX( FILTER( 'Table', [Index] >= __DepartIndex && [Index] <= __ArrivalIndex ), [Travel Time (min)] ),
SUMX( FILTER( 'Table', [Index] <= __DepartIndex && [Index] >= __ArrivalIndex ), [Travel Time (min)] )
)
RETURN
__Result
All travel time give me blank
@MSAYED26 OK, try this one. Updated PBIX attached.
Travel Time Measure =
VAR __Departure = MAX([Departure station])
VAR __Arrival = MAX([Arrival station])
VAR __Line = MAX([Line])
VAR __DepartIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Departure ), [Index] )
VAR __ArrivalIndex = MAXX( FILTER( 'Table', [Line] = __Line && [Arrival station] = __Arrival ), [Index] )
VAR __ArrivalIndexReverse = MAXX( FILTER( 'Table', [Line] = __Line && [Departure station] = __Arrival ), [Index] )
VAR __Result =
SWITCH( __Line,
"Blue", SUMX( FILTER( 'Table', [Index] >= __DepartIndex && [Index] <= __ArrivalIndex ), [Travel Time (min)] ),
SUMX( FILTER( 'Table', [Index] < __DepartIndex && [Index] >= __ArrivalIndexReverse ), [Travel Time (min)] )
)
RETURN
__Result
Also, I don't think that your data posted is correct or something is weird. Red S1 goes to S2 to A4, to A3, A2, A1, A10 so I don't see how S1 to A10 is 12 minutes.
Thanks
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |