POWER POINT MEASURE DISTANCE CODE

For my requirement though, I needed a way to display it on a map. Once the distance measure is available, it can be used in any visual, not simply a map. As with Phil, I can’t explain the math in detail, but I can adapt the formula to DAX. 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. Doing even more research, there is another post from David Hager with a third alternate formula in DAX. I double-checked my calculation versus his, and fortunately, distances match. It turns out that Phil Seamark already had posted a DAX distance measure about a year and a half ago. I should have done more research on the topic specific to Power BI. This allows you to select any coordinate on the globe as your reference point, not simply what’s included in your data.

Create parameters for Latitude and Longitude and add them as filters/slicers.

Here’s a sample calculated table that selects City, State, Latitude, and Longitude from the primary table named ‘US Cities’:

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.

The “Selector” in this case can be constructed in two ways that I’ve tried: VAR _formula2 = 2 * ATAN(DIVIDE(SQRT(_formula1),SQRT(1-_formula1))) VAR _lonDiff = (MIN('Selector')-_lonSelected) * _multiplierĬOS(MIN('Selector') * _multiplier) * COS(_latSelected * _multiplier) * VAR _latDiff = (MIN('Selector')-_latSelected) * _multiplier VAR _lonSelected = SELECTEDVALUE('US Cities') VAR _latSelected = SELECTEDVALUE('US Cities') Haversine/great-circle distance calculation adapted from Stack Overflow: Taking the highest voted Stack Overflow answer and adapting it to DAX, I came up with the following measure: 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. 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. There were a few options on Stack Overflow.

POWER POINT MEASURE DISTANCE CODE

Fortunately, this problem has been solved before and adapted to various code languages. Calculating distance on the earth “as the crow flies” relies on some math that’s outside the scope of my ability.

Some users cannot deploy custom visuals in their organization.įinding distance along the edge of a sphere calls for the haversine formula. 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. Differentiating between points that are in versus out is.

Fortunately, having a visible circle is not part of the requirement. “But wait David, the Power BI Map won’t draw a circle of a given radius!” Nope. This solution does not work with text locations–latitude and longitude only. 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. The third was the largest limitation-users cannot dynamically select new points and are instead limited to points that the content creator chooses. The second is that the ArcGIS Map will only display 1,000 points without upgrading to Plus. The first is that while the ArcGIS Map has an option for distance radius, it only allows a maximum of 100 miles / 161 kilometers.

There were three primary challenges with that visual that did not allow me to meet requirements. My first inclination was to use the ArcGIS Map for Power BI because of its Drive Time feature. Here’s a sample report that aligns closely with the requirements: Report consumers would need to be able to make a location selection and see which other points were in the same vicinity. Displaying customers within a given distance radius on a Power BI map was one of my recent challenges.

Power point measure distance