Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
kletterbilder
Helper I
Helper I

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

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:

 

kletterbilder_0-1628168351974.png

 

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

Now I found finally the solutions to solve all my problems. The final report looks like this

 

kletterbilder_0-1631734881652.png

 

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.

 

kletterbilder_2-1631735399552.png

 

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:

 

kletterbilder_3-1631735515969.png

 

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!

 

kletterbilder_4-1631735673633.png

 

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:

 

kletterbilder_0-1631737931575.png

 

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.

 

 

 

 

View solution in original post

20 REPLIES 20
lbendlin
Super User
Super User

Making some progress

lbendlin_0-1630631944991.png

 

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_1-1630634221581.png

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.

 

kletterbilder_0-1630648402549.png

Thanks and I wish you a nice and hopefull sunny weekend.

Here you go

lbendlin_0-1630694107502.png

Schönes Wochenende.

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

 

kletterbilder_0-1631734881652.png

 

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.

 

kletterbilder_2-1631735399552.png

 

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:

 

kletterbilder_3-1631735515969.png

 

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!

 

kletterbilder_4-1631735673633.png

 

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:

 

kletterbilder_0-1631737931575.png

 

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.

lbendlin
Super User
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.

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:

 

kletterbilder_0-1630594038145.png

 

 

I also tried it with DAX

 

kletterbilder_1-1630598088758.png

 

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

 

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
Community Support
Community Support

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!

 

CustomerLocationKindOfAdressStreetZIPCODECITYCOUNTRY
Customer1Germany, 80939ProjectGundelkoferstraße 580939MünchenGermany
Customer2Germany, 80995ProjectPenckstr. 480995MünchenGermany
Customer3Germany, 80995ProjectLeuchsstr. 11 a80995MünchenGermany
Customer4Germany, 81737ProjectUnterbiberger Straße 3181737MünchenGermany
Customer6Germany, 81809ProjectHeinrich-Böll-Straße 481809MünchenGermany
Customer7Germany, 81825ProjectKönigsseestraße 2081825MünchenGermany

 

SupplierLocationKindOfAdressStreetZIPCODECITYCOUNTRY
Materiallieferant1Germany, 81379MateriallieferantAidenbachstraße 5281379MünchenGermany
Architekt1Germany, 80995ArchitektPaul-Preuß-Straße 280995MünchenGermany
Materiallieferant2Germany, 81829MateriallieferantHanns-Schwindt-Str. 281829MünchenGermany
Baustelleneinrichtung1Germany, 81547BaustelleneinrichtungPeißenbergstraße 1781547MünchenGermany
Materiallieferant3Germany, 80809MateriallieferantBirnauer Straße 480809MünchenGermany
Architekt2Germany, 81829ArchitektWilly-Brandt-Allee 1081829MünchenGermany
Materiallieferant4Germany, 80637MateriallieferantWaisenhausstr. 480637MünchenGermany
Baustelleneinrichtung2Germany, 81925BaustelleneinrichtungArabellastraße 481925MünchenGermany
Materiallieferant5Germany, 81249MateriallieferantJakob Baumann Strasse 281249MünchenGermany

 

 

lbendlin
Super User
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.

nah, the UNION really just creates a static table. Not impacted by filter choices. 

lbendlin_0-1628266653818.png

 

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?

 

kletterbilder_0-1630337594877.png

 

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.

 

zipcodecitylongitudelatitude
80331München11,572199448,1378593
80333München11,568218448,1468565
80335München11,552018648,1471265
80336München11,55398848,134305
80337München11,558981248,1283121
80339München11,538065348,1379361
80469München11,572236748,1299013
80538München11,590835348,1464634
80539München11,581743748,146632
80634München11,529439848,1511242
80636München11,542819848,1542354
80637München11,537090548,1659187
80638München11,505888248,1633227
80639München11,508792848,1527916
80686München11,511953748,1342723
80687München11,505909348,1432007
80689München11,485333748,1331355
80796München11,569470748,1646491
80797München11,556760348,1637607
80798München11,565641848,157168
80799München11,574364148,1535962
80801München11,578659248,1595621
80802München11,592261348,1607759
80803München11,580311848,1659026
80804München11,575879148,17393
80805München11,606642848,1757667
80807München11,584582348,1859744
80809München11,552174748,1808528
80933München11,557141748,2179463
80935München11,552949448,2002579
80937München11,575012248,2113313
80939München11,616642548,2080563
80992München11,517267748,1763994
80993München11,519447948,188144
80995München11,51624148,2191299
80997München11,482648748,1949517
80999München11,452312848,1926955
81241München11,4636648,1430311
81243München11,436725348,1475698
81245München11,442117348,1625235
81247München11,467532148,1688216
81249München11,404449448,1675378
81369München11,530240648,1121952
81371München11,547245148,1172998
81373München11,529938648,1246112
81375München11,485210148,1213817
81377München11,492954648,1131369
81379München11,53192848,1021508
81475München11,480452548,0928777
81476München11,495329348,0912289
81477München11,507593248,0852591
81479München11,523443848,0798162
81539München11,588696448,112694
81541München11,58650948,1223744
81543München11,563428548,1119033
81545München11,557034348,089909
81547München11,575061548,1027772
81549München11,600492448,0994684
81667München11,599194448,1324385
81669München11,600759748,1215281
81671München11,617963148,1235767
81673München11,630188848,1301745
81675München11,60237248,1409528
81677München11,631827448,1402794
81679München11,608160848,1495544
81735München11,639859348,1118932
81737München11,632818948,1005994
81739München11,659746648,0902065
81825München11,660382348,120573
81827München11,689155448,1088248
81829München11,687664648,1354537
81925München11,622493948,1637603
81927München11,637324148,1600676
81929München11,663951248,1624688

But it will not work. I will see all or even one by one. But not one customer and one group of supplier?

 

kletterbilder_2-1630422605844.png

 

 

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.

 

kletterbilder_0-1630422425203.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.