Hi,
We are a construction company. Right now I can not solve following problem:
I have two sets of data one for Customers and one for Suppliers. My folks want to choose one customer in a table and view the adress of this customer in the center. The rest of the customers should not be seen at this time. Afterwards they want to look for different kind of suppliers and where they are located. For examples they want to see all different roof suppliers around this customer. Is this possible and can you send me an tutorial for this? I also draw below an sample for my problem:
The following 2 discussions did not help me!
Solved: Can I visualize two location table on one map? and... - Microsoft Power BI Community
Solved: Re: Map multiple data sets on the same map - Microsoft Power BI Community
Sample Data you will find below!
Yours Christoph
Now I found finally the solutions to solve all my problems. The final report looks like this
I can choose a customer (German "Kunde") on the right side and change the Slicer and adjust the distance between 0 and 2000 km.
If I select a customer. All other customers will disappear from the map and the table below and the distance to all our suppliers will be calculated with a measure in PowerBI. That works with the Great Circle Method. Please be careful with difference between Radian and Degree. I have all the latitude and longitude in Degree. So far I have to multiply the Degree * PI() /180. What you can see below. If not the distances between the the customers and all the suppliers will be all wrong.
Measure "Distance":
The result looks like this. If I increase the circle from 50 km to more. I will see more suppliers around this one customer.
The project you can see in the middle with an bigger circle (I show it with an green arrow in the picture on top). This I also solved with my measure "Size":
The tables I use looks like this. I highlighted the measures I defined:
The measure "Measure Filter":
Both tables "Customers" and "Suppliers" put together in the table "Combined" with the function in PowerQuery - "Append Queries".
The table "Entfernung" (it means Distances in German) is pretty easy in DAX. This is used to make the slider possible for the Distance adjustment, because you can not connect a slider to an measure!
Because I do not have all longitude and latitude Data for all addresses I found an API description to https://www.bingmapsportal.com/.
The function looks like this and works perfectly. Here you can find also an YouTube Video with more details: Power BI Latitude and Longitude Function | Bing Map API - YouTube
You just have to change YOURAPIKEY with your API key from BingMaps. For getting started you have to subscribe there.
let
FindLatLong = (location) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/" & location & "?o=xml&key=YOURAPIKEY")),
ResourceSets = Source{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
Resources = ResourceSet{0}[Resources],
Location = Resources{0}[Location],
Point = Location{0}[Point]
in
Point
in
FindLatLong
I use the Azure Map as the Map Visual. I can see this map perfect in the PowerBI Desktop. The last problem I have is that if I upload the report to PowerBI Online I can not make it appear in Teams. Here I get following Error message in German:
I guess this is an issue with our firewall. I am still trying to fix this with our IT department.
The problem is maybe described here:
Getting started with the Azure Maps Power BI visual | Microsoft Docs
Thanks to Ibendlin. You helped me a lot.
Making some progress
While you cannot hide/show the locations via measures you can influence the size of their bubbles. So we can use the size control to show the selected customer on the map, and the nearest three suppliers.
For the latter we need to do the following steps
- create a table variable with all locations
- flag locations as excluded if they are customer locations and do not match the slicer customer (set size to 1)
- flag the customer location as included if it matches the slicer (set size to 20)
- create a RANKX of all remaining locations and their distance to the selected customer
- if the current location is in the top three then set its size to 15 else set it to 1
Notes: 81809 is not a valid ZIP code
A couple of companies have the same location/zip code. It will require better Lat/Lon data to tell them apart.
size =
var n = SELECTEDVALUE(Combined[Name])
var s = SELECTCOLUMNS(FILTER(All(Combined),Combined[Type]="Supplier"),"N",Combined[Name],"lat",Combined[latitude],"lon",Combined[longitude])
var t = ADDCOLUMNS(s,"dist",abs([lat]-SELECTEDVALUE(Customers[latitude]))+abs([lon]-SELECTEDVALUE(Customers[longitude])))
var u = ADDCOLUMNS(t,"r",RANKX(t,[dist],,ASC))
var v = filter(u,[N]=n)
return switch(TRUE(),SELECTEDVALUE(Combined[Name])=SELECTEDVALUE(Customers[Name]),20,CONCATENATEX(v,[r]) in {"1","2","3"},15,1)Hi,
thank you. This comes very close. We are on the right track.
I just have one more question.
Is it possible to make the following part in the code adjustable? A slicer would be perfect. Right now this part ist static. For sure I can add more but I want to make this adjustable for the user.
Thanks and I wish you a nice and hopefull sunny weekend.
Now I found finally the solutions to solve all my problems. The final report looks like this
I can choose a customer (German "Kunde") on the right side and change the Slicer and adjust the distance between 0 and 2000 km.
If I select a customer. All other customers will disappear from the map and the table below and the distance to all our suppliers will be calculated with a measure in PowerBI. That works with the Great Circle Method. Please be careful with difference between Radian and Degree. I have all the latitude and longitude in Degree. So far I have to multiply the Degree * PI() /180. What you can see below. If not the distances between the the customers and all the suppliers will be all wrong.
Measure "Distance":
The result looks like this. If I increase the circle from 50 km to more. I will see more suppliers around this one customer.
The project you can see in the middle with an bigger circle (I show it with an green arrow in the picture on top). This I also solved with my measure "Size":
The tables I use looks like this. I highlighted the measures I defined:
The measure "Measure Filter":
Both tables "Customers" and "Suppliers" put together in the table "Combined" with the function in PowerQuery - "Append Queries".
The table "Entfernung" (it means Distances in German) is pretty easy in DAX. This is used to make the slider possible for the Distance adjustment, because you can not connect a slider to an measure!
Because I do not have all longitude and latitude Data for all addresses I found an API description to https://www.bingmapsportal.com/.
The function looks like this and works perfectly. Here you can find also an YouTube Video with more details: Power BI Latitude and Longitude Function | Bing Map API - YouTube
You just have to change YOURAPIKEY with your API key from BingMaps. For getting started you have to subscribe there.
let
FindLatLong = (location) =>
let
Source = Xml.Tables(Web.Contents("http://dev.virtualearth.net/REST/v1/Locations/" & location & "?o=xml&key=YOURAPIKEY")),
ResourceSets = Source{0}[ResourceSets],
ResourceSet = ResourceSets{0}[ResourceSet],
Resources = ResourceSet{0}[Resources],
Location = Resources{0}[Location],
Point = Location{0}[Point]
in
Point
in
FindLatLong
I use the Azure Map as the Map Visual. I can see this map perfect in the PowerBI Desktop. The last problem I have is that if I upload the report to PowerBI Online I can not make it appear in Teams. Here I get following Error message in German:
I guess this is an issue with our firewall. I am still trying to fix this with our IT department.
The problem is maybe described here:
Getting started with the Azure Maps Power BI visual | Microsoft Docs
Thanks to Ibendlin. You helped me a lot.
Hi
figured out the formular for the distance between 2 points and tried more.
But I am not sure were you want to head:
= Table.AddColumn(#"Changed Type", "Entfernung", each Number.Acos(Number.Sin([Latitude])*Number.Sin(48.1659)+Number.Cos([Latitude])*Number.Cos(48.1659)*Number.Cos(11.5371-[Longitude]))*6378.388*Number.PI/180)
The result is in km if the system calculates with degree instead of radian.
My current result looks like this:
I also tried it with DAX
nah, the UNION really just creates a static table. Not impacted by filter choices.
You might be able to trick it by setting the customer location to BLANK() for customers that are not selected in the original table. Let me try that.
Edit: Does't work either for the filled map - it only accepts columns as values.
Which map visual are you planning to use?
Hi,
thank you for your quick reply. The Problem with the right Location I solved before and I
changed it in your file. Here you find the video link
Incorrect MAP LOCATIONS in Power BI and how to fix it - YouTube
But I still can not see your solution, that I can choose one customer and the suplier arround?
PS: I wanted to upload the pbix file again - like you. But it does not work?
But it will not work. I will see all or even one by one. But not one customer and one group of supplier?
I also tried to change the interactions? This helpes little because the filter do not change on the left side if I change something. But if I choose in both tables anything the dots will disapear.
Helpful resources
Power BI Monthly Update - September 2024
Check out the September 2024 Power BI update to learn about new features.
Microsoft Fabric & AI Learning Hackathon
Learn from experts, get hands-on experience, and win awesome prizes.
Fabric Community Update - September 2024
Find out what's new and trending in the Fabric Community.
