Distance Calculation On Map | Power BI Geospatial Analysis

Distance Calculation On Map | Power BI Geospatial Analysis

No comments

In this tutorial, I’ll discuss distance calculation on map in Power BI based on forward stock location (FSL) and delivery point. This is just one of the various ways on how you can efficiently use distance calculation for geospatial analysis.

In this example, I’ll display and analyze customers within a radius from an existing forward stock location at a depot or a hub.

distance calculation on map

The Dataset For Distance Calculation On Map

Let’s now discuss the dataset that I used for this instance of distance calculation. Here’s the data that contains the Customers data table.

It also contains the Forward Stock Locations table.

The Depots table has been appended to the Customers data table. This is to allow simultaneous display.

As you can see, there’s no connection between the two tables.

Furthermore, the DAX measures that I used are in three separate tables. 

When modeling, I keep my measures at hand. Once my report is completed, the measure tables will be hidden. 

I used three sets of measures to calculate and display the distance for this demonstration.

Dax Measures For Distance Calculation On Map

I transformed the distance calculation (Haversine formula) into a DAX measure (Distance measure). I’ve discussed the Haversine formula in an article about Distance And Bearing Calculation in Power BI (I’ll link this once it’s published).

This formula is slightly different from the Power Query function because I just made this as a workaround.

I used the Distance measure to calculate the distance based on a selection from the Select Distance slicer.

I used the Total Distance in selection measure to calculate the total distance from the selection. Then, I displayed it on the table.

distance calculation on map

Moreover, I used the Distance in selection measure as a supporting measure for the Total Distance in selection measure.

distance calculation on map

DAX Measures For Map Visual

The next set of measures contains the measures for the color, size, and title.

The first measure for this set is the FSL Point Color (Forward Stock Location Point Color).

This measure creates the green point in this smaller map.

It is used to zoom in on the selected location from the slicers.

distance calculation on map

Next is the Map Point Color measure.

The Map Point Color measure displays the green or blue color on the bigger map.

distance calculation on map

Additionally, I used the Size measure to display the customer’s bubbles and selected depot in different sizes on the map.

distance calculation on map

Last is the Map measure. I used this to create a dynamic title.

This dynamic title changes based on the selection from the Select Distance slicer.

distance calculation on map

DAX Measures For Demand And Revenue Calculations

The third measure table contains some standard calculations for Revenue and Demands. It also has measures that show the results of these calculations associated with the selected distance.

distance calculation on map

I created a Select Radius table with the distances from 0 to 250 km. I used a what-if parameter for this one.

That table is then used to create this special slicer with a slider.

distance calculation on map

After getting all the measures ready, I can now start building the report.

The Output

As a result, here’s the map with the points from the distance selection.

distance calculation on map

In this report, I can select a depot.

distance calculation on map

I can also change the distance on the Select Distance slicer. Then, I’ll be able to see the results in the table like the Revenue in selection, Demand in selection, and the Total Distance in Selection.

distance calculation on map

The smaller map zooms in on the selected location. This will move based on the selected depot.

These cards also show the results based on the selection from the Select Distance slicer.

Conclusion

In this tutorial, I didn’t focus on building the most attractive dashboard. It is merely for demonstrating one of the many ways to do distance calculation on map in Power BI.

Hopefully, you were able to understand how to dynamically create visual reports in geospatial analysis where you need to analyze and display data based on a selected distance. Keep in mind that properly doing the measures and creating the dataset is essential for this to work.

Check out the links below for more examples and related content.

Cheers!

Paul

Membership Banne

***** Related Links *****
Power BI Data Visualization Technique: Creating A Dynamic Heading Or Title
Geospatial Analysis – New Course on Enterprise DNA
Data Visualizations Power BI – Dynamic Maps In Tooltips

***** Related Course Modules *****
Geospatial Analysis in Power BI
DAX Formula Deep Dives
Data Visualization Tips

***** Related Support Forum Posts *****
Improve Query With Conditional Merge For Distance Calculation
How To Calculate Distance Travelled Using Just Lat/Long In Power BI?
Calculate Distance From A Fuel Purchase Summary With Odometer Readings
For more distance calculation support queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.