Sum

[MSAYED26]

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

[Ashish_Mathur]

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.

[Greg_Deckler]

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

[MSAYED26]

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

[Greg_Deckler]

@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]

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 .

[Greg_Deckler]

@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
    __Result

[MSAYED26]

All travel time give me blank

[Greg_Deckler · Accepted solution]

@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]

Thanks