cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Finding airline connections

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&DOriginDestination
DEN-ATLDENATL
DEN-DTWDENDTW
DEN-ORDDENORD
DTW-DENDTWDEN
DTW-ORDDTWORD
ATL-ORDATLORD
1 ACCEPTED SOLUTION
Community Support
Community Support

Hello @MrNoob ,

Try this:

1. Create two tables.

Origin = VALUES('Table'[Origin])
Destination = VALUES('Table'[Destination])

re.JPG

2. Create slicers.

slicer.gif

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 ) & " " )

od.gif

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.

View solution in original post

3 REPLIES 3
Frequent Visitor

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

 

O&DOriginDestinationDeparture TimeArrival Time
DEN-ATLDENATL12:0015:00
DEN-DTWDENDTW  
DEN-ORDDENORD  
DTW-DENDTWDEN08:0010:00
DTW-ORDDTWORD  
ATL-ORDATLORD  

 

 

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

 

Can it be?

 

Thank you.

Community Support
Community Support

Hello @MrNoob ,

Try this:

1. Create two tables.

Origin = VALUES('Table'[Origin])
Destination = VALUES('Table'[Destination])

re.JPG

2. Create slicers.

slicer.gif

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 ) & " " )

od.gif

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.

View solution in original post

Super User III
Super User III

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

Screenshot 2020-10-01 021418.png

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors