Show adress from one customer and the supplier adresses around this adresses from supplier.

[kletterbilder]

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

[lbendlin]

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.

[lbendlin]

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)

[kletterbilder]

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.

[lbendlin]

Here you go

Schönes Wochenende.

[kletterbilder]

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.

[kletterbilder · Accepted 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":

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])

VAR Projecttype = MIN(Combined[LINEOFBUSINESSID])

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]

VAR Projecttype = MIN(Combined[LINEOFBUSINESSID])

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.

[lbendlin]

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

[lbendlin]

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.

[kletterbilder]

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

[lbendlin]

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.

[v-kkf-msft]

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

[kletterbilder]

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

[lbendlin]

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.

[lbendlin]

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?

[kletterbilder]

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.

[lbendlin]

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.

[kletterbilder]

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? 

[lbendlin]

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

File upload is unfortunately only possible for Super Users.

[kletterbilder]

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

[kletterbilder]

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.