Forum Discussion
Solved
Sum
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 Lin...
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 __ResultAlso, 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.
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
__ResultAnd 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 __ResultAll 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 __ResultAlso, 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.