Displaying customers within a given distance radius on a Power BI map was one of my recent challenges. Report consumers would need to be able to make a location selection and see which other points were in the same vicinity. Here’s a sample report that aligns closely with the requirements:
My first inclination was to use the ArcGIS Map for Power BI because of its Drive Time feature. There were three primary challenges with that visual that did not allow me to meet requirements. The first is that while the ArcGIS Map has an option for distance radius, it only allows a maximum of 100 miles / 161 kilometers. The second is that the ArcGIS Map will only display 1,000 points without upgrading to Plus. The third was the largest limitation—users cannot dynamically select new points and are instead limited to points that the content creator chooses.
I found my solution in the unlikeliest of places: the vanilla Power BI “Map” visual. If you don’t have latitude and longitude coordinates for your points available, you’d need to geocode those first. This solution does not work with text locations–latitude and longitude only.
“But wait David, the Power BI Map won’t draw a circle of a given radius!” Nope. Fortunately, having a visible circle is not part of the requirement. Differentiating between points that are in versus out is. You could also approach this with other map visuals, but the core Map has one advantage over custom visuals since it’s native to Power BI and should have organizational support almost everywhere. Some users cannot deploy custom visuals in their organization.
Calculating Great-Circle Distance
Finding distance along the edge of a sphere calls for the haversine formula. Calculating distance on the earth “as the crow flies” relies on some math that’s outside the scope of my ability. Fortunately, this problem has been solved before and adapted to various code languages. There were a few options on Stack Overflow. While the haversine formula does not account for the fact that Earth is not a true sphere, the margin of error is small enough that it won’t matter much when looking at localized distances. Even measuring at the global level, you might end up at maximum a few dozen kilometers off depending on the location of your point of reference and another point across the globe.
Taking the highest voted Stack Overflow answer and adapting it to DAX, I came up with the following measure:
Distance (km) =
// Haversine/great-circle distance calculation adapted from Stack Overflow: https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula
VAR __latSelected = SELECTEDVALUE('US Cities'[Latitude])
VAR __lonSelected = SELECTEDVALUE('US Cities'[Longitude])
VAR __radius = 6371
VAR __multiplier = PI()/180
VAR __latDiff = (MIN('Selector'[Latitude])-__latSelected) * __multiplier
VAR __lonDiff = (MIN('Selector'[Longitude])-__lonSelected) * __multiplier
VAR __formula1 =
SIN(__latDiff/2) * SIN(__latDiff/2) +
COS(MIN('Selector'[Latitude]) * __multiplier) * COS(__latSelected * __multiplier) *
SIN(__lonDiff/2) * SIN(__lonDiff/2)
VAR __formula2 = 2 * ATAN(DIVIDE(SQRT(__formula1),SQRT(1-__formula1)))
VAR __distance = __radius * __formula2
RETURN __distanceThe “Selector” in this case can be constructed in two ways that I’ve tried:
- Use a copy of your main data table and narrow the columns to Latitude, Longitude, and supporting data like City to make filters/slicers more relevant to consumers who know the data. Here’s a sample calculated table that selects City, State, Latitude, and Longitude from the primary table named ‘US Cities’:
Selector =
SELECTCOLUMNS('US Cities',
"State",'US Cities'[State],
"City",'US Cities'[City],
"Latitude",'US Cities'[Latitude],
"Longitude",'US Cities'[Longitude]
)- Create parameters for Latitude and Longitude and add them as filters/slicers. This allows you to select any coordinate on the globe as your reference point, not simply what’s included in your data.
This has been done before…
I should have done more research on the topic specific to Power BI. It turns out that Phil Seamark already had posted a DAX distance measure about a year and a half ago. I double-checked my calculation versus his, and fortunately, distances match. Doing even more research, there is another post from David Hager with a third alternate formula in DAX. Take your pick. They’re all based on variations of the haversine formula so it shouldn’t matter much whether you opt for the measure I have included, Phil’s, or David’s version. As with Phil, I can’t explain the math in detail, but I can adapt the formula to DAX.
Differentiate Points on a Power BI Map
Once the distance measure is available, it can be used in any visual, not simply a map. For my requirement though, I needed a way to display it on a map. Conditional formatting / Advanced Controls under Format – Data Colors to the rescue. The solution that I arrived at was to create a separate measure that had different hex color values based on the distance. I then used that measure in Advanced Controls with the Map visual.
Color
I broke my values out into a separate “Color Thresholds” table so that the thresholds for the SWITCH function as well as the colors could be data-driven rather than hardcoded. This adds another layer of complexity though, and in its simpler form, you could get by hardcoding the various SWITCH values. Limit is a number in kilometers and Color is a hex color value. By using SWITCH in this measure, you are able to set as many threshold limits as you desire. I used five main limits that display different colors as points radiate out from the selected location.
Map Point Color =
// Hardcode limits and colors below or use data-driven threshold and colors from "Color Thresholds" calculated table
IF(HASONEFILTER('Selector'[City]),
SWITCH(TRUE(),
// selection color
[Distance (km)] = 0, MIN('Color Thresholds'[Selection Color]),
// color scale as distance from selection increases
[Distance (km)] <= MIN('Color Thresholds'[A Limit]), MIN('Color Thresholds'[A Color]),
[Distance (km)] <= MIN('Color Thresholds'[B Limit]), MIN('Color Thresholds'[B Color]),
[Distance (km)] <= MIN('Color Thresholds'[C Limit]), MIN('Color Thresholds'[C Color]),
[Distance (km)] <= MIN('Color Thresholds'[D Limit]), MIN('Color Thresholds'[D Color]),
// color beyond last threshold (max color)
MIN('Color Thresholds'[Max Color])
)
)In this sample, I pulled my color palette from Color Brewer, and I marked the selected point in red (#ff0000) where distance = 0.
Size and Display
As another convenience, I added a parameter for a variable distance threshold. With this parameter factoring into a size measure, I show only points that fall within the user-selected maximum distance. All points the don’t meet the IF condition do not display on the map.
Map Point Size =
IF([Distance (km)] <= [Maximum Distance (km) Value],
IF(HASONEFILTER('Selector'[City]),
SWITCH(TRUE(),
[Distance (km)] = 0,1,
0.25
),
1
),0)
The PBIX file for this sample is available on the Power BI Data Stories Gallery: https://community.powerbi.com/t5/Data-Stories-Gallery/Display-Points-within-a-Distance-Radius-on-a-Power-BI-Map/m-p/649975#M2558
You’ll need the March 2019 or later version of Power BI Desktop to open it.
Hi David,
I had a near similar requirement recently from customer but I had to show Walking Distance and Driving Distance on the map. I used Google Maps api with PowerQuery and Databricks with R to populate the table with walking and driving distance. I had a requirement to show how many vendors are within 1000, 2000 and 2000 ft Plus Walking distance KPI and color coding them whether the particular stop meets the requirement.
I used Arcgis Online ($700/Year) to create Public Transport Stops as it is static and does not change and used walking distance of vendor from particular stop.
I think we need more of these capabilities with Microsoft Native Map Visual (Multiple Layers etc.). it has been supported with Tableau as well. Native Integration with Azure Maps would also be helpful to take mapping capabilities to the next level.
Great post! Is it possible to create in Dax or Power Query a grid (for example 1 point each 1 km) around the globe and apply some krigging or estimative for each point?
I don’t know how, but it would be possible to generate that data. Just need the math as a starting point.
A great post and I was able to use this as a template to plot thousands of points as I asked on Twitter. I have a new requirement. Rather than post the points coloured (yes, I’m Canadian and that’s how I spell colour 🙂 ) by distance I want to colour them according to a category, all within a radius. I am hoping to use this as an example.
This post was exactly what I needed to see. Thank you so much for doing this!
Great to hear it was helpful. Thanks for the feedback!
Hi David,
Can you clarify how the Maximum Distance slider is functioning? I have my BI setup just like yours and everything works but that slicer. When I change the distance it doesn’t filter out the points above the set point. I looked through your file and can’t find an actual relationship between the table used for the slicer and the calculated distance values.
Thanks!
Maximum Distance was setup as a What If Parameter, so there is no relationship. The DAX measures reference the Max Distance measure that’s included in that parameter.
I have one problem. I set my file up exactly this way with my Canadian data. The what if parameter is setup properly and the distance slider does it’s job…..for everything but the map! If I slide to a larger KM number the map does not reveal those new points that should be within? And vice verse… Help! 🙂 Otherwise, this is excellent stuff.
i should say….every time i add in the ‘map point size’ measure to the map, the points on the map disappear immediately. i have no clue why. Tried everything in my power to find the issue.
correction. i had to delete the map a couple of times and re-enter the lat/longs and then the map size measure and it is finally working. i guess it was just buggy. no clue. and chance you can walk us through this measure? I dont fully get it:
Map Point Size =
IF (
[Distance (km)] <= [Maximum Distance (km) Value],
IF (
HASONEFILTER ( ‘Selector'[Agency] ),
SWITCH ( TRUE (), [Distance (km)] = 0, 1, 0.25 ),
1
),
0
)
David-
Thanks for this great example! It is working great for me, except I have lost the ability to color code the dots by distance.
I have split the starting points into a new table as those are set by which user is viewing the file. I am still using the selector table as well. My three tables (starting addresses, selector, and US Cities) are all linked by the person’s Name. In the Map Point Color measure I have tried using the name field from each those tables, but it doesn’t seem to color code.
Any suggestions of what else I can try to get this functionality back?
Thanks!
Hello,
Is it possible to key off an address entered by the user instead of a pre-populated city or ZIP?
The address would have to be in your data. As long as you have a list of possible addresses, this should work. There’s no way to get the location through an ad hoc request to
a geocoding service to find the coordinates for any address a user might want.
Hi David,really nice post
Can we have distance in meters instead of km
Did you find a solution?
Hey David,
Thanks for the great article. I was wondeirng if you knew if it was possible for the Power BI maps to display two layers of lat and long data? and that by clicking one a variable from one set, triggered the other set to appear. At the moment, our work around for this is to display the second set of lat long data in a table, which is filtered by a distance radius slicer. But for users of the dashboard, it would be much nicer to see this secondary set in relation to the first on a map
Thanks for commenting Gil. In the core visuals, there’s no direct way to handle multiple layers on a map. You might be able in some cases to split it out and filter by category to make it appear as multiple layers. For custom visuals depending on your situation, you might try the Icon Map or Mapbox.
Thanks for the reply David. I will have a play aroud with Icon Map & Mapbox. Will drop another comment on here if I’m sucessful, seems like a handy thing for the community to know.
I’m really sorry if this is really obvious but I’ve been struggling to display the card showing points in proximity. How did you do you this?
Thank you!!
Hi,
I have replicated your tutorial with my data and it’s working fine. Is it possible to use Mapbox instead of Map visual? Mapbox doesn’t respond to the Map Point Size as in Map.
Thanks,
Mike
It should work with any map that plots lat/long including Mapbox. Each map visual treats size, etc. differently, so no guarantee that experience would be the same.
Hi,
How about the Table? Is it possible to filter it by the Max Distance, similarly to the Map?
I did the table filtering by another Comparing the Distance vs Max Distance.
Thank you for your post 🙂
Hi David, thanks for your tutorial. Really cool feature.
Is it possible to select the coordinates by clicking on map? The system would capture those coordinates and inject them into calculations.
The distance has to be based on coordinates in the data source. We can’t base it on current click location.
Hey David,
Thank you so much for this absolutely brilliant post! I’ve got it working on my end for the most part but when I select a City, it doesn’t display the red dot, only the dots around it. Any idea where I might be going wrong?
Cheers!
Check the PBIX available using the link in the post, but if you don’t see a dot at all, the first thing I would check is the conditional statement in the size measure. If you see a dot but it’s simply not red, it would be related to the conditional statement in the color measure.
Hey David, I can’t seem to figure out how you actually created these parameters for Lat and Long. Did you need to write a query from scratch first? Seems like the quick query function doesn’t let you set a parameter with increments of 0.1. How do you do this?
I take it back, You CAN do this with What if parameters. I didn’t notice I had to set the data type to decimal.
BUT – now I can’t figure out how to use these parameters with the dashboard. How do you use these parmaters to set the focal point (red dot) instead of the fields within your ‘Selector’ table?