Create Origin To Destination Map In BI Desktop

[rbowen]

I've spent the past several days trying to find a simple solution to this but, so far, no luck. I have a single, hand built table of historical sales data for six locations (origins) and 99 locations where product might be shipped or delivered (destinations). I need to create what amounts to a flow map that shows lines between each origin point and all the destinations product was delivered or shipped to. I have lat/long information for all origins and destinations as well as zip codes and full street addresses for both. Ultimately, I'm looking to see where paths cross or are duplicated to determine where we might gain efficiencies. 

I've investigated several different possible solutions that might work in BI Desktop but they either required Bing Maps (now gone) or an extensive background in R, Java or similar - I'm not opposed to learning those but I have limited time to deliver a solution. The Flow Map plugin by Weiwei Cui was often mentioned in numerous discussions for this task and looked promising but that plug has stopped working since Microsoft dumped Bing Maps back in June. I've tried using the native ArcGIS map in BI but it's complicated and would require me to rebuild a substantial part of my data table (I think) and fairly limited without a license. I'm willing to invest in a license if I can find a relatively 'low code' solution that will allow me to get this project completed soon. Granted, I'm new to the GIS/GeoCoding arena so everything seems complicated. Perhaps I'm over-simplifying this, but it seems that in 2025 there would be a relatively straighforward way of accomplishing this task without becoming a GIS developer.

So, is it possible to actually do this in BI Desktop, or do I need to investigate some other way of getting it done?

[v-agajavelly]

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.

[v-agajavelly]

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.

[v-agajavelly]

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.

[v-agajavelly · Accepted solution]

Hi @rbowen 

A big thank you to both of you for your detailed and insightful responses.

@Greg_Deckler   Your two-step approach using GENERATESERIES and a dynamic DAX pattern for building intermediate points is spot on. It’s a scalable and flexible solution, especially valuable when working with multiple origin-destination pairs.

@tharunkumarRTK   Thank you for pointing to the Azure Maps Path Layer. It’s a native, low-code solution that directly meets the requirement and works well for visualizing shipment paths using existing lat/long data.

Both of your solutions correct and offer complementary approaches. One with full control using DAX, and the other leveraging Azure Maps' built-in capabilities.

@rbowen  Is there any chance you could try these out and see if they meet your needs? These suggestions could help you get to a working solution without heavy external dependencies.

Thanks again for your support, experts.

Regards,
Akhil.

[rbowen]

Apologies for the delay in responding. We discovered that the lat longs of the destination locations were actually ship to addresses, not delivery addresses - the old adage about real world data being messy and inconsistent comes to mind here. As such, the stakeholders have decided to wave off on the line draw option and go with drive time/distances. For that, we're using the native ArcGIS map visual in BI Desktop. To my knowledge, the Azure map visual doesn't have this capability. However, thank you to both @Greg_Deckler and @tharunkumarRTK for your suggestions. If we ever get our DB house in order and need to investigate a line draw option, I'll examine both options. 

[tharunkumarRTK]

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

@tharunkumarRTK  - 

We were not able to use Azure maps because we needed to examine specific drive times - i.e., 30 minutes from the orgin, 45 minutes from the origin, etc. The only native visual in BI that would allow this is the ArcGIS map. 

[Greg_Deckler]

@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

Columbus, OH	39.9611111	-82.9988889
London, UK	51.5	-0.116667
Kansas City	39.099912	-94.581213
St. Louis	38.627089	-90.200203

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

[Greg_Deckler]

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