Introduction
It’s the final week of mapping month! This week we are plotting points on a map within a selected radius. It’s common to want to see locations, such as stores or restaurants that are nearby to a selected location. This week we are exploring one way to do that in Power BI. This involves a fun bit of DAX and some conditional formatting.
This week we are mapping Dutch Bros Coffee locations. This is a coffee chain based in Oregon, USA that has locations in about 13 states throughout the country. I enjoy their cold brew, but they have many hot, cold, and frozen drink options.
Requirements
- Retrieve the Dutch Bros location data and the list of cities from data.world.
- Create a map visual that plots the Dutch Bros locations using longitude and latitude.
- Use the “CityLatLong.xlsx” table to populate state and city slicers.
- Create a DAX calculation that uses the haversine formula to calculate distance from the selected city in the slicers to each Dutch Bros location. (I chose to use kilometers, but feel free to do miles if you prefer.)
- Create a slicer to allow users to filter on distance from store locations to the selected city.
- Add a visual (I used a table) to list the store addresses and the distance from the selected city.
- Configure the map and table so they only show locations within the selected distance radius. (Hint1, Hint2)
Dataset
This challenge uses data provided on data.world. You can use the data.world connector in Power BI (recommended) or download the Excel file to your local machine. You will need to log in to data.world to retrieve the data, but accounts are free.
How would you change the distance formula to use miles instead of kilometer?
Hi, John. You can change the radius to 3956. This makes the units miles instead of kilometers.
Hi, just wanted to check if I can find distance of coffee shops from a particular location, which I can keep changing. Say distance from a Metro or Distance from a particular house number/Lat-Long or particular shop
Hi. Yes, that is possible. The challenge is that you have to provide lat and long for the source and destination. I provided the lat and long of the cities in my source data. It would be much trickier if you needed to do that on the fly (since you wouldn’t have pre-populated lat and long for your source), but maybe it could be done using a Power App embedded in the report. If you just want to show distance from a metro area, you just have to add the metro area to your dataset and assign it a lat and long value.
Is it possible to make a search radius for PowerApps and filter locations? Would be nice to hear from you. Thnaks in advance.
Are you asking if you can embed a Power BI visual in a Power App and then use the location from a device as retrieved from the app to filter locations? You could probably do that. You would need to adjust how the location is input into the map.
Hi Megan,
What if I want to apply the filter on the distance between the same coffeeshops, that is, first select the coffeeshop that I´m interested in and then get the nearest coffeeshops
The formula for calculating distance is the same either way, just change the inputs for your measure.
Hu excellent video, easy to follow. However, try as I might I cannot get the distance calculation to work properly, I must be doing something wrong in the calculation (below) but I cannot see what, any help appreciated
Thanks Dave
sDistance (KM) =
var LatPl = min(Planners[latitude])
var LngPl = min(Planners[longitude])
var LatSite = min(Sites[latitude])
var LngSite = min(Sites[longitude])
var P = DIVIDE(PI(),180)
var A = 0.5 * COS((LatSite – LatPl) * P)/2 +
COS(LatPl * P) * COS(LatSite * P) * (1-COS((LngSite-LngPl) * P))/2
var final = 12742 * ASIN((SQRT(A)))
Return
final
Hello Megan,
Before creating the visualization do we have to create any relationship between the tables in the model?
Nope. There are no relationships in the model. Feel free to check the solution video for more details.
It looks correct to me after a quick glance. Can you explain the result you are getting vs what you expected?
Hi I might have missed this in the tutorrial but can i also include a point on the map that highlights the Lat/Long of the selected city(in a different colour to the coffee shops?
THank you for all your help
Hello, thank you for this awesome video. However, I am having trouble with my measure…
Distance =
var Lat1 = min(‘City'[Latitude Decimal])
var Lng1 = min(‘City'[Longitude Decimal])
var Lat2 = min(‘Location of Shop'[Latitude Decimal])
var Lng2 = min(‘Location of Shop'[Longitude Decimal])
var P = DIVIDE (PI(),180)
var A = 0.5 * COS((Lat2-Lat1) * P)/2 +
COS(Lat1 * P) * COS(Lat2 * P) * (1-COS((Lng2-Lng1) * P))/2
var Final = 12742 * ASIN((SQRT(A)))
Return Final
It comes up with an error saying the argument ‘ASIN’ has the wrong data type or the result is too large or too small. Am I missing something?
Note…when I write the measure, it does not show any errors. It is only when I put it in a visual that I get the error.
Thank you.
That’s not addressed in the tutorial. The lat/long of the city is an arbitrary point within the city, based upon the dataset used. So the city isn’t actually a data point that gets plotted on the map. You could probably add it and use conditional formatting to change the color.
Check that the data type of your Lat and long fields are decimal. You can also return the output of var A and make sure the value is what you expect.
Hello,
Permission helps that the formula you entered is wrong. It should be “0.5 – COS” not “0.5 * COS”.
Thank You
Hello,
Permission helps that the formula you entered is wrong. It should be “0.5 – COS” not “0.5 * COS”.
Thank You
Great tutorial. Thanks.
Was wondering if it is possible to add a circle with a red line and no fill – or 80% transparency or so – centered on the chosen city’s lat&long, please.
Thanks
There isn’t a built-in map visual that would support those types of marks. You could try it in Deneb or another custom visual. I haven’t done this myself, so I have no detailed advice.
Hi there –
For the “Is Within Threshold”, I keep getting an empty table with Location and Distance. I have selected a State and a City and have my Max Distance at 15 and nothing comes up. Can you please help? Thanks.
I would suggest going back through the calculations that are dependencies for [Is Within Threshold] and checking their values. In your table, clear the filter and just look at address and distance. Are those values correct? If the distance values aren’t correct then, the threshold check won’t be correct.
This is great! Just curious what I need to change in the Distance formula to get miles instead of KM.
Is it possible to create this relationship using only zip codes and not coordinates?
If you want to calculate the distance, you need the coordinates. If you want to calculate distance to a zip code, you would have to pick a point within the zip code to use in the distance calculation.
For miles, change the 12742 in the formula to 7918
The “Is Within Threshold” filter only seems to work situationally. I am unable to apply it to some visuals, to the whole page or all pages. Do you know why that might be?
I am plotting customers whose addresses are within a radius of store locations and have been asked to display these customers’ demographics.
This workout has been absolutely fantastic; thank you! I look forward to doing many more of these.
I figured it out! I wrote a new DAX measure that looks like this:
Customers within Distance =
CALCULATE(
COUNT(‘Customers'[ID]),
FILTER(
‘Customers’,
[Distance (km)] <= [Maximum Distance (km) Value]
)
)
I am able to use this measure to create the demographics visuals requested by my report consumers.
thank you!