Forum Discussion
Distance-Based Location Highlighting
Hello,
I’m trying to calculate the distance between the assigned location and the employee’s home location using the latitude and longitude available in this file.
The objective is to identify locations that are within the selected distance range (in kilometres) using a kilometre slicer. I would like the locations that fall within the selected range to be highlighted in green, while all other locations remain grey.
Additionally, I would like the selected location to be highlighted based on the selected City, Employee Location, or StoresDummy[City] value.
I’ve also referred to the following YouTube video for guidance: youtube
Could you please help me implement this logic in Power BI/DAX?
Sample data
Hi Swami_om,
If you already have the distance calculation working, then I would separate the remaining requirement into a colour measure.
The important part is to give the selected location the highest priority, then check the distance, and finally use grey for everything else.
For example, if StoresDummy[City] is your disconnected slicer table:
Location Color =
VAR SelectedCity =
SELECTEDVALUE ( StoresDummy[City] )
VAR CurrentCity =
SELECTEDVALUE ( Locations[City] )VAR DistanceKm =
[Distance KM]VAR SelectedRadiusKm =
[Selected Distance KM]
RETURN
SWITCH (
TRUE (),
NOT ISBLANK ( SelectedCity )
&& CurrentCity = SelectedCity, "#D32F2F",
NOT ISBLANK ( DistanceKm )
&& DistanceKm <= SelectedRadiusKm, "#2E7D32",
"#BDBDBD"
)You would replace Locations[City], [Distance KM], and [Selected Distance KM] with the corresponding names from your model.
The order of the conditions is important. The selected city is tested first, so even though its distance would normally be 0 km and therefore qualify as green, it is returned as red instead.
Microsoft recommends SELECTEDVALUE for retrieving a single selected slicer value, and Power BI supports using a DAX measure that returns hex colours for field-value conditional formatting.
I would also keep StoresDummy disconnected from the location table if it is being used only to select the centre location. If you use the same City field that drives the map as the slicer, selecting a city can filter the other map locations away, which prevents you from showing the surrounding locations in green/grey.
Then apply Location Color to the map's marker/fill colour using conditional formatting (fx) with Format style = Field value, if the map layer you are using exposes that option.
The resulting logic is:
- selected location = red
- location within selected radius = green
- location outside selected radius = grey
If you are using Azure Maps specifically, the exact colour setting depends on whether you are using the Marker, Filled map, or another layer, but the DAX logic above can remain the same.AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
4 Replies
- MFelix
Super User
Hi Swami_om ,
For this you need to use a specific formula that is used to calculate the distance between two points on earth.
Check this post below with the calculation and also an example:
https://dax.tips/2017/05/13/dynamic-distances-in-power-bi/ (This is from a master in DAX Phil Seamark) Includes a copy of the PBIX.
Distance Calculation from Latitude & Longitude Data | Microsoft Fabric Community
Calculation distance between latitudes & Longitudes | Microsoft Fabric Community
I have already added or used the calculation into shared file, just want highlight the selected location in red color
v-saisrao-msftCommunity Support
Hi Swami_om,
Have you had a chance to review the solution shared by ShivekMaharaj MFelix ? If the issue persists, feel free to reply so we can help further.
Thank you.
- ShivekMaharaj
Community Champion
Hi Swami_om,
If you already have the distance calculation working, then I would separate the remaining requirement into a colour measure.
The important part is to give the selected location the highest priority, then check the distance, and finally use grey for everything else.
For example, if StoresDummy[City] is your disconnected slicer table:
Location Color =
VAR SelectedCity =
SELECTEDVALUE ( StoresDummy[City] )
VAR CurrentCity =
SELECTEDVALUE ( Locations[City] )VAR DistanceKm =
[Distance KM]VAR SelectedRadiusKm =
[Selected Distance KM]
RETURN
SWITCH (
TRUE (),
NOT ISBLANK ( SelectedCity )
&& CurrentCity = SelectedCity, "#D32F2F",
NOT ISBLANK ( DistanceKm )
&& DistanceKm <= SelectedRadiusKm, "#2E7D32",
"#BDBDBD"
)You would replace Locations[City], [Distance KM], and [Selected Distance KM] with the corresponding names from your model.
The order of the conditions is important. The selected city is tested first, so even though its distance would normally be 0 km and therefore qualify as green, it is returned as red instead.
Microsoft recommends SELECTEDVALUE for retrieving a single selected slicer value, and Power BI supports using a DAX measure that returns hex colours for field-value conditional formatting.
I would also keep StoresDummy disconnected from the location table if it is being used only to select the centre location. If you use the same City field that drives the map as the slicer, selecting a city can filter the other map locations away, which prevents you from showing the surrounding locations in green/grey.
Then apply Location Color to the map's marker/fill colour using conditional formatting (fx) with Format style = Field value, if the map layer you are using exposes that option.
The resulting logic is:
- selected location = red
- location within selected radius = green
- location outside selected radius = grey
If you are using Azure Maps specifically, the exact colour setting depends on whether you are using the Marker, Filled map, or another layer, but the DAX logic above can remain the same.AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
