Forum Discussion

MSAYED26's avatar
MSAYED26
Helper II
2 years ago
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...
  • Greg_Deckler's avatar
    Greg_Deckler
    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
        __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.