Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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.
Thanks.
This works for me. The only problem with the data set is, that Customer 2 and 3 are not shown in the map. Parallel I also try the concept with a bigger dataset. Here I have the same problem. But i can not figure it out why. The long and lat data are in the dataset of the customer 2 and 3.
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.
As I mentioned they share the exact same lat/lon with suppliers. Change your data to be more realistic.
Thank you for updating the sample data. I'll look at it later. In the meantime you can look up the (DAX) formula to calculate the Great Circle distance.
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
The next step will be to identify the selected customer, then calculate the distance to all partners, and then pick the top three partners via RANKX.
It's an interesting topic, unfortunately i can't carve out much time at the moment. Will let you know when I had time.
Hi @kletterbilder ,
Has your problem been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Sample Data!
Customer | Location | KindOfAdress | Street | ZIPCODE | CITY | COUNTRY |
Customer1 | Germany, 80939 | Project | Gundelkoferstraße 5 | 80939 | München | Germany |
Customer2 | Germany, 80995 | Project | Penckstr. 4 | 80995 | München | Germany |
Customer3 | Germany, 80995 | Project | Leuchsstr. 11 a | 80995 | München | Germany |
Customer4 | Germany, 81737 | Project | Unterbiberger Straße 31 | 81737 | München | Germany |
Customer6 | Germany, 81809 | Project | Heinrich-Böll-Straße 4 | 81809 | München | Germany |
Customer7 | Germany, 81825 | Project | Königsseestraße 20 | 81825 | München | Germany |
Supplier | Location | KindOfAdress | Street | ZIPCODE | CITY | COUNTRY |
Materiallieferant1 | Germany, 81379 | Materiallieferant | Aidenbachstraße 52 | 81379 | München | Germany |
Architekt1 | Germany, 80995 | Architekt | Paul-Preuß-Straße 2 | 80995 | München | Germany |
Materiallieferant2 | Germany, 81829 | Materiallieferant | Hanns-Schwindt-Str. 2 | 81829 | München | Germany |
Baustelleneinrichtung1 | Germany, 81547 | Baustelleneinrichtung | Peißenbergstraße 17 | 81547 | München | Germany |
Materiallieferant3 | Germany, 80809 | Materiallieferant | Birnauer Straße 4 | 80809 | München | Germany |
Architekt2 | Germany, 81829 | Architekt | Willy-Brandt-Allee 10 | 81829 | München | Germany |
Materiallieferant4 | Germany, 80637 | Materiallieferant | Waisenhausstr. 4 | 80637 | München | Germany |
Baustelleneinrichtung2 | Germany, 81925 | Baustelleneinrichtung | Arabellastraße 4 | 81925 | München | Germany |
Materiallieferant5 | Germany, 81249 | Materiallieferant | Jakob Baumann Strasse 2 | 81249 | München | Germany |
Here's how I would try to do it
- pick the columns from the customer and supplier tables that are needed for the map visual. Include the account type column so you can have different pin colors
- create a new calculated table as a UNION between these sets of columns.
- feed the map visual from the unioned table, not from the original tables.
- keep the slicers tied to the original tables.
- select one customer
No idea if it will work. Please provide sample data in usable format (not as a picture) if desired.
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,
thanks for your tries. I have no preference of the visual. I tried with different ones. I hope it will work with Free of charge Visuals. I would prefere one of these.
Thank you.
Here's a new approach. Have a combined table with customers and suppliers, and also two separate disconnected tables for the slicers.
The plan is to create a measure that sees if a single customer is selected, and then to filter the combined table to that customer and the closest partners.
Issue: Your addresses are ambiguous and cannot be used in most maps. In order to avoid the ambiguities and to calculate distances we also need Latitude/Longitude.
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?
We need Latitude and Longitude to calculate the Great Circle distance.
File upload is unfortunately only possible for Super Users.
Hi,
herewith I send you the center point of the zip code of Munich with latitude and latitude. I have this list also for whole Germany and can map it in the future if your solution will work. I am curious about your solution. I could not see any difference in my solution.
zipcode | city | longitude | latitude |
80331 | München | 11,5721994 | 48,1378593 |
80333 | München | 11,5682184 | 48,1468565 |
80335 | München | 11,5520186 | 48,1471265 |
80336 | München | 11,553988 | 48,134305 |
80337 | München | 11,5589812 | 48,1283121 |
80339 | München | 11,5380653 | 48,1379361 |
80469 | München | 11,5722367 | 48,1299013 |
80538 | München | 11,5908353 | 48,1464634 |
80539 | München | 11,5817437 | 48,146632 |
80634 | München | 11,5294398 | 48,1511242 |
80636 | München | 11,5428198 | 48,1542354 |
80637 | München | 11,5370905 | 48,1659187 |
80638 | München | 11,5058882 | 48,1633227 |
80639 | München | 11,5087928 | 48,1527916 |
80686 | München | 11,5119537 | 48,1342723 |
80687 | München | 11,5059093 | 48,1432007 |
80689 | München | 11,4853337 | 48,1331355 |
80796 | München | 11,5694707 | 48,1646491 |
80797 | München | 11,5567603 | 48,1637607 |
80798 | München | 11,5656418 | 48,157168 |
80799 | München | 11,5743641 | 48,1535962 |
80801 | München | 11,5786592 | 48,1595621 |
80802 | München | 11,5922613 | 48,1607759 |
80803 | München | 11,5803118 | 48,1659026 |
80804 | München | 11,5758791 | 48,17393 |
80805 | München | 11,6066428 | 48,1757667 |
80807 | München | 11,5845823 | 48,1859744 |
80809 | München | 11,5521747 | 48,1808528 |
80933 | München | 11,5571417 | 48,2179463 |
80935 | München | 11,5529494 | 48,2002579 |
80937 | München | 11,5750122 | 48,2113313 |
80939 | München | 11,6166425 | 48,2080563 |
80992 | München | 11,5172677 | 48,1763994 |
80993 | München | 11,5194479 | 48,188144 |
80995 | München | 11,516241 | 48,2191299 |
80997 | München | 11,4826487 | 48,1949517 |
80999 | München | 11,4523128 | 48,1926955 |
81241 | München | 11,46366 | 48,1430311 |
81243 | München | 11,4367253 | 48,1475698 |
81245 | München | 11,4421173 | 48,1625235 |
81247 | München | 11,4675321 | 48,1688216 |
81249 | München | 11,4044494 | 48,1675378 |
81369 | München | 11,5302406 | 48,1121952 |
81371 | München | 11,5472451 | 48,1172998 |
81373 | München | 11,5299386 | 48,1246112 |
81375 | München | 11,4852101 | 48,1213817 |
81377 | München | 11,4929546 | 48,1131369 |
81379 | München | 11,531928 | 48,1021508 |
81475 | München | 11,4804525 | 48,0928777 |
81476 | München | 11,4953293 | 48,0912289 |
81477 | München | 11,5075932 | 48,0852591 |
81479 | München | 11,5234438 | 48,0798162 |
81539 | München | 11,5886964 | 48,112694 |
81541 | München | 11,586509 | 48,1223744 |
81543 | München | 11,5634285 | 48,1119033 |
81545 | München | 11,5570343 | 48,089909 |
81547 | München | 11,5750615 | 48,1027772 |
81549 | München | 11,6004924 | 48,0994684 |
81667 | München | 11,5991944 | 48,1324385 |
81669 | München | 11,6007597 | 48,1215281 |
81671 | München | 11,6179631 | 48,1235767 |
81673 | München | 11,6301888 | 48,1301745 |
81675 | München | 11,602372 | 48,1409528 |
81677 | München | 11,6318274 | 48,1402794 |
81679 | München | 11,6081608 | 48,1495544 |
81735 | München | 11,6398593 | 48,1118932 |
81737 | München | 11,6328189 | 48,1005994 |
81739 | München | 11,6597466 | 48,0902065 |
81825 | München | 11,6603823 | 48,120573 |
81827 | München | 11,6891554 | 48,1088248 |
81829 | München | 11,6876646 | 48,1354537 |
81925 | München | 11,6224939 | 48,1637603 |
81927 | München | 11,6373241 | 48,1600676 |
81929 | München | 11,6639512 | 48,1624688 |
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
90 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |