I want to calculate total travel time based on line and station filterExample : Departure station (A1) Arrival, station(A3) and line red so travel time =24 min How make this in power bi LineDeparture stationArrival stationTravel Time (min)RedA1A212RedA2A312RedA3A412RedA4A512RedA5A612RedA6A712RedA7A812RedA8A912RedA9A1012RedA10S112RedS1S212GreenA1A28GreenA2A38GreenA3A48GreenA4A58GreenA5A68GreenA6A78GreenA7A88GreenA8A98GreenA9A108GreenA10F18GreenF1F28BlueA1A27BlueA2A37BlueA3A47BlueA4A57BlueA5A67BlueA6A77BlueA7A87BlueA8A97BlueA9A107PurpleA1A27PurpleA2A37PurpleA3A47PurpleA4A57PurpleA5A67PurpleA6A77PurpleA7A87PurpleA8A97PurpleA9A107

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

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

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

Need Help

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.

TravelTimes.pbix42 KB

9 Replies

Greg_Deckler

Community Champion

2 years ago

MSAYED26 Looks like a Transitive Closure problem: Transitive Closure - Microsoft Fabric Community

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

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

TravelTimes.pbix40 KB

Ashish_Mathur
Super User
2 years ago
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.

Forum Discussion

Sum

9 Replies

Related content

Running sum

Filtering a Sum

SUM

Improper Sum with slicer

Summing by Client