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...
Need Help
  • 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.

     

     

  • MSAYED26's avatar
    MSAYED26
    Helper II
    2 years ago

    But i  want make filter with  Departure station and filter with Arrival station then arrival time calculated

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      2 years ago

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

        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 .