Finding airline connections

[MrNoob]

I am lost here.  I am trying to find the connections for the origin and destination pairs.  In this example DEN-ORD,  I would like to return DEN-ATL-ORD  and so on?

Is this even possible?

Thanks for the help:)

O&D	Origin	Destination
DEN-ATL	DEN	ATL
DEN-DTW	DEN	DTW
DEN-ORD	DEN	ORD
DTW-DEN	DTW	DEN
DTW-ORD	DTW	ORD
ATL-ORD	ATL	ORD

[akay23]

Hi, I used your solution for my report. It works good. But I want to calculate total flight time. 
For Example,

O&D	Origin	Destination	Departure Time	Arrival Time
DEN-ATL	DEN	ATL	12:00	15:00
DEN-DTW	DEN	DTW
DEN-ORD	DEN	ORD
DTW-DEN	DTW	DEN	08:00	10:00
DTW-ORD	DTW	ORD
ATL-ORD	ATL	ORD

I want to total flight time DTW - DEN - ATL = 7:00

Can it be?

Thank you.

[Icey · Accepted solution]

Hello @MrNoob ,

Try this:

1. Create two tables.

Origin = VALUES('Table'[Origin])

Destination = VALUES('Table'[Destination])

2. Create slicers.

3. Create a measure.

Measure =
VAR Origin_ =
    SELECTEDVALUE ( Origin[Origin] )
VAR Destination_ =
    SELECTEDVALUE ( Destination[Destination] )
VAR t1 =
    SELECTCOLUMNS (
        FILTER ( 'Table', 'Table'[Origin] = Origin_ ),
        "Origin1", [Origin],
        "Destination1", [Destination]
    )
VAR t2 =
    SELECTCOLUMNS (
        FILTER ( 'Table', 'Table'[Destination] = Destination_ ),
        "Origin2", [Origin],
        "Destination2", [Destination]
    )
VAR t3 =
    FILTER (
        CROSSJOIN ( t1, t2 ),
        [Destination1] = [Origin2]
            || [Destination1] = Destination_
    )
VAR t4 =
    SUMMARIZE (
        t3,
        [Origin1],
        [Destination1],
        [Destination2],
        "O&D_",
            IF (
                [Destination1] = Destination_,
                [Origin1] & "-" & [Destination1],
                [Origin1] & "-" & [Destination1] & "-" & [Destination2]
            )
    )
RETURN
    CONCATENATEX ( t4, [O&D_], "," & UNICHAR ( 10 ) & " " )

BTW file, attached .pbix file.

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[CNENFRNL]

Hi, @MrNoob , it's totally possible, pls try the following code,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnH103UM8VHSAbGAJIgdqwMRdwkJh4uD2DBx/yAXuDiIDRYPCddFqITIwsSh6sHiMPVAe6DiENvB4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"O&D" = _t, Origin = _t, Destination = _t]),
    OD = Source[#"O&D"],
    #"Removed Columns" = Table.RemoveColumns(Source,{"O&D"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Destination"}, #"Removed Columns", {"Origin"}, "Source", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Destination", "Exchange"}}),
    #"Expanded Source" = Table.ExpandTableColumn(#"Renamed Columns", "Source", {"Destination"}, {"Destination"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Source", each ([Destination] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if List.Contains(OD, [Origin] & "-" & [Destination]) then [Origin] & "-" & [Destination] else false),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> false))
in
    #"Filtered Rows1"

the final result