Filtering by Dynamic Distance in PowerBI

TL:DR , the blog show some tricks using DAX and Icon Map, pattern like dynamic filtering by distance and showing a Buffer when selecting a point, The Publish to web report and pbix are here

before you get too excited Currently to make it works you have to block the Zoom Functionality.

The Formula for calculating distance in PowerBI is very well known I think it was first mentioned here , I had used it already for building this report a couple of years ago.

what changed is Icon Map, with the 3.0 release basically nearly everything can be customized which make some new scenario Possible.

you need only 1 table with coordinates

Draw a Line Between Two Points and show Distance

1- You Build first an Icon map just using point

2-Create the second Layer by creating a new Map Visual

This Map will show the line based on the Point selected

we need first a dummy table with one row

Build a measure that capture the selection

linestring = var Geo="LINESTRING("&CONCATENATEX(reference,[longitude]&" "&[latitude],",")&")"
return
if(ISFILTERED(reference[city]),Geo,BLANK())

add the Line String Measure here

and Voila now you have two Map, The Point Selected in the first Map will be shown as a line in the second Map, CONCATENATEX does not respect the Order of Selection, so currently it is useful only for two Points ( M parameter respect the selection but that will be another Blog)

3- Merge the Two Map together

I guess this is the trick of the Blog, if you want this behavior

Stack the two layers on top of each other; Make the Point layer background transparent, Remove Background, Lock the Map, keep both layer having the same Dimension , Layer Layout and zoom Value

You have to remove the Zoom otherwise the two layers will not be synchronized, Technically speaking we need only 1 Map, but unfortunately DAX table can not filter itself, feel free to vote on this idea and complain on Linkedin and Twitter 🙂

Select a Buffer Selection

When you Select a Point, the Map will draw a Circle and filter only the Point inside the Circle, it is a very Powerful GIS pattern.

The Same approach, but now we create three Layers

The Selection Layer we use this option ( the point not selected will be hidden, I love Icon map )

The Circle Layer

we use this Option

to get the Radius of the Circle we use this Measure

diam_M = if(ISFILTERED(reference[city]),SELECTEDVALUE(Dis_Parameter[Dis])*1000)

The Parameter is used to control the Size and the measure Works only when a point is selected

The Calculation Layer is where we show the Point filtered , we will use a bit of DAX, we create a new disconnected Table , make sure there is no relationship between the two Tables

to reduce data duplication we need only ID from the first table, I am using a measure to get the coordinates ( Probably LookupValue is no the most performant Option)

Target_Lat = LOOKUPVALUE(reference[latitude],reference[city],min(Target[Target City]))
Target_Lng = LOOKUPVALUE(reference[longitude],reference[city],min(Target[Target City]))

The Measure to calculate the Distance is

Target Distance = 
-- see https://radacad.com/dynamic-distances-in-power-bi
var Lat1 = min(reference[latitude])
var Lng1 = min(reference[longitude])

var Lat2 = [Target_Lat]
var Lng2 = [Target_Lng]
---- Algorithm here -----
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 if(ISFILTERED(reference[city]),final)

And then another Measure to filter the Result

Filter_by_Distance = if(ISFILTERED(reference[city]),if([Target Distance]<=SELECTEDVALUE(Dis_Parameter[Dis]),1,BLANK()),BLANK()) 

Then add the measure filter to the Viz

Icon Map is a fantastic Tool, and there are plenty of possibilities, hopefully the author keep adding more options and customisation