Forum Discussion
MSAYED26
2 years agoHelper II
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...
- 2 years ago
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.
Greg_Deckler
2 years agoCommunity Champion
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.
MSAYED26
2 years agoHelper II
Thanks