Reading Time: 5 minutes
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 __distance
The “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.
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.
The post Display Points within a Distance Radius on a Power BI Map appeared first on DataVeld.