Since my post last week on using the Google Image Charts API to create sparklines and small multiples in Power BI has proved very popular, I thought I would do a follow-up showing how to use the Azure Maps API to create map small multiples. Here’s an example of what’s possible, a table from a sample report I built that displays crimes committed in London (sourced from here) in June 2018 with one row for each crime and a map column displaying the location of the crime:
You can find out how to sign up for an Azure Maps account here; it isn’t free to use but you do get 250,000 free map renders per month (which should be more than enough for Power BI use) and any use over that is extremely cheap. Full details on pricing can be found here.
Here’s what the source data in my dataset looks like:
The only important column is the Center column, which contains the longitude of the crime location followed by a comma followed by the latitude of the crime location in a single text value.
With the data in this format you can call the Get Map Image API relatively easily in DAX using a measure something like this:
[sourcecode language=”text” padlinenumbers=”true” highlight=”7″]
Map =
var BaseURL =
"https://atlas.microsoft.com/map/static/png"
var SubscriptionKey =
"?subscription-key="
&
"insert your key here"
var ApiVersion =
"&api-version=1.0"
var Layer =
"&layer=hybrid"
var Center =
"¢er=" & SELECTEDVALUE(‘London Crime'[Center])
var ZoomLevel =
"&zoom=16"
var HeightWidth =
"&height=150&width=150"
return
IF(
HASONEVALUE(‘London Crime'[Center]),
BaseURL & SubscriptionKey & ApiVersion &
Layer & Center & ZoomLevel & HeightWidth
)
[/sourcecode]
You’ll need to paste your Azure Maps API key in on the line highlighted above and set the Data Category for the measure to Image URL. The maximum possible height of an image in a table or matrix in Power BI is, as far as I can see, 150 pixels so that’s why the code above requests an image that is 150×150. You may want to experiment with different zoom levels and layer types to see what looks best on your report.
You can view the sample report here and download a copy of the report (without my API key in) here.
Hi Chris,
Just wanted to point out that there is a security issue in this. On hover of map user can see the subscription key used.
Prateek Raina
Also, even if you turn the tool tips off, right click on map image and open in new tab gives the subscription key in the browser URL.
Thanks for letting me know, that is a good point. I guess that within an organisation you would have to trust your users…
Exactly.
Also, risky for Power BI Embedded reports in ISVs or Publish to Web.