Create Origin To Destination Map In BI Desktop

rbowen Here's a more complete solution that takes a table of cities and turns it into a table of latitude and longitude points that can create a "line" between all the different cities using Azure map visual:

CityLatitudeLongitude

From and To = 
    VAR __Steps = SELECTCOLUMNS( GENERATESERIES( 1, 10, 1 ), "Step", [Value] )
    VAR __NumSteps = 10
    VAR __BaseTable =
        FILTER( 
            GENERATE( 
                SELECTCOLUMNS( 'Cities', "From City", [City], "From Long", [Longitude], "From Lat", [Latitude] ),
                SELECTCOLUMNS( 'Cities', "To City", [City], "To Long", [Longitude], "To Lat", [Latitude] )
            ),
            [From City] <> [To City]
        )
    VAR __String = CONCATENATEX( __BaseTable, [From City] & "^" & [From Long] & "^" & [From Lat] & "^" & [To City] & "^" & [To Long] & "^" & [To Lat], "|" )
    VAR __Count = COUNTROWS( __BaseTable )
    VAR __Table = 
        ADDCOLUMNS(
            GENERATESERIES( 1, __Count ),
            "__Data", SUBSTITUTE( PATHITEM( __String, [Value] ), "^", "|" )
        )
    VAR __Result = 
        SELECTCOLUMNS(
            ADDCOLUMNS(
                GENERATE(
                    __Table,
                    __Steps
                ),
                "Latitude", PATHITEM( [__Data], 3 ) +  ( [Step] / __NumSteps ) * ( PATHITEM( [__Data], 6 ) - PATHITEM( [__Data], 3 ) ),
                "Longitude", PATHITEM( [__Data], 2 ) + ( [Step] / __NumSteps) * ( PATHITEM( [__Data], 5 ) - PATHITEM( [__Data], 2 ) )
            ),
            "Latitude", [Latitude],
            "Longitude", [Longitude]
        )
RETURN
    __Result

rbowen 

I am wondering whether you have explored path layer in Azure maps which will allow you to draw lines between origin and destination, if not then please check it out and see if that solves the purpose

https://learn.microsoft.com/en-us/azure/azure-maps/power-bi-visual-add-path-layer

Connect on LinkedIn

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

rbowen OK, so one way to do this would be to create a table of all some of the intermediate points between each origin and destination. Then you could simply plot them on an Azure map visual like so:

IntermediatePoints =
VAR StartLat = 40.7128
VAR StartLon = -74.0060
VAR EndLat = 34.0522
VAR EndLon = -118.2437
VAR Steps = 10

RETURN
ADDCOLUMNS(
    GENERATESERIES(0, Steps),
    "Latitude", StartLat + ([Value] / Steps) * (EndLat - StartLat),
    "Longitude", StartLon + ([Value] / Steps) * (EndLon - StartLon)
)

Hi rbowen ,

Really appreciate the update, and no worries at all on the delay. Totally agree  real-world data has a way of humbling us all. Thanks for the context on the shift to drive time/distance. The ArcGIS map visual is a solid choice for that, especially when line-drawing isn't viable due to messy or misaligned geo fields like ship-to vs delivery addresses. Sounds like you're on a good path forward with what’s realistically available right now. If things evolve and you revisit the line draw option later, happy to help dig into either of the suggested approaches. Until then, best of luck ironing out the data.

Regards,
Akhil.

Hi rbowen ,

Glad to hear you’ve got a solid path forward. Just checking in did the ArcGIS approach give you what you needed?

Regards,
Akhil.

Hi rbowen ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Regards,
Akhil.