cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

1 ACCEPTED SOLUTION
Helper I

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":

Distance =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR lat1 = MIN(Combined[latitude])*PI()/180
VAR lat2 = SELECTEDVALUE(Customers[latitude])*PI()/180
VAR lon1 = MIN(Combined[Longitude])*PI()/180
VAR lon2 = SELECTEDVALUE(Customers[longitude])*PI()/180
RETURN
If(
ISBLANK(Custo),
0,
ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
)

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":

Size =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR Combi = MIN(Combined[PROJECTNAME])
RETURN
IF(
Custo = Combi && Projecttype ="Projekt",
2,1)

The tables I use looks like this. I highlighted the measures I defined:

The measure "Measure Filter":

Measure Filter =
VAR MinValue = MIN(Entfernung[Entfernung (in km)])
VAR MaxValue = MAX(Entfernung[Entfernung (in km)])
VAR CurrentMeasureValue = Combined[Distance]
RETURN
IF(ISBLANK(SELECTEDVALUE(Customers[PROJECTNAME])),
1,
IF(
CurrentMeasureValue >= MinValue && CurrentMeasureValue <= MaxValue,
1,
0
))

and measure "Type":
Type =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR Size = Combined[Size]
RETURN
IF(
ISBLANK(Custo),
Projecttype,
IF(
Projecttype = "Projekt" && size = 2,
Projecttype,
IF(
Projecttype <> "Projekt",
Projecttype,
"")))

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.

20 REPLIES 20
Super User

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.

Super User

``````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 v = filter(u,[N]=n)
return switch(TRUE(),SELECTEDVALUE(Combined[Name])=SELECTEDVALUE(Customers[Name]),20,CONCATENATEX(v,[r]) in {"1","2","3"},15,1)``````
Helper I

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.

Super User

Here you go

Schönes Wochenende.

Helper I

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.

Helper I

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":

Distance =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR lat1 = MIN(Combined[latitude])*PI()/180
VAR lat2 = SELECTEDVALUE(Customers[latitude])*PI()/180
VAR lon1 = MIN(Combined[Longitude])*PI()/180
VAR lon2 = SELECTEDVALUE(Customers[longitude])*PI()/180
RETURN
If(
ISBLANK(Custo),
0,
ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
)

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":

Size =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR Combi = MIN(Combined[PROJECTNAME])
RETURN
IF(
Custo = Combi && Projecttype ="Projekt",
2,1)

The tables I use looks like this. I highlighted the measures I defined:

The measure "Measure Filter":

Measure Filter =
VAR MinValue = MIN(Entfernung[Entfernung (in km)])
VAR MaxValue = MAX(Entfernung[Entfernung (in km)])
VAR CurrentMeasureValue = Combined[Distance]
RETURN
IF(ISBLANK(SELECTEDVALUE(Customers[PROJECTNAME])),
1,
IF(
CurrentMeasureValue >= MinValue && CurrentMeasureValue <= MaxValue,
1,
0
))

and measure "Type":
Type =
VAR Custo = SELECTEDVALUE(Customers[PROJECTNAME])
VAR Size = Combined[Size]
RETURN
IF(
ISBLANK(Custo),
Projecttype,
IF(
Projecttype = "Projekt" && size = 2,
Projecttype,
IF(
Projecttype <> "Projekt",
Projecttype,
"")))

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.

Super User

As I mentioned they share the exact same lat/lon with suppliers. Change your data to be more realistic.

Super User

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.

Helper I

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

Distance2 = ACOS(SIN(Combined[Latitude])*SIN(SELECTEDVALUE(Customers[Latitude]))+COS(Combined[Latitude])*COS(SELECTEDVALUE(Customers[Latitude]))*COS(SELECTEDVALUE(Customers[Longitude])-Combined[Longitude]))*6378.388*PI()/180

Super User

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.

Community Support

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

Helper I

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

Super User

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.

Super User

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?

Helper I

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.

Super User

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.

Helper I

Hi,

thank you for your quick reply. The Problem with the right Location I solved before and I

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?

Super User

We need Latitude and Longitude to calculate the Great Circle distance.

File upload is unfortunately only possible for Super Users.

Helper I

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
Helper I

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.