Forum Discussion
Map visualisation with multiple datasets?
I've got 2 datasets to present. The first is a list of offices and their addresses. The second is a list of postcodes covered by each office.
Ideally I'll show a map with a pin/bubble indicating the location of each office and the postcodes coloured by office to show coverage.
To get across the line, I used a "Filled Map" with a legend to generate the coverage and a "Map" to generate the pins/bubbles. I then used an image editing tool to overlay them to create the final image for distribution. This is going to be tedious to perform on an ongoing basis.
Are there any visualisatons that can do what I'm after?
HI Ormesome,
Based on test, current power bi not support auto determine the relationship mapping on Multi-records to Multi-records.
On my opinion, I'd like to suggest merge and append these columns to one table, then use it as the source of map visual.
Steps:
1. Duplicate 'Catchments' table.
2. Add custom column postcode to lookup value from 'CatchmentPostcodes' table.=Function.Invoke((id as any) => Table.SelectRows(CatchmentPostcodes,each [CatchmentID]= id),{[CatchmentID]})[Postcode]3. Add custom column Address to lookup values from 'CatchmentOffices' table.
=Function.Invoke((id as any) => Table.SelectRows(CatchmentOffices,each [CatchmentID]= id),{[CatchmentID]})[Address]4. Expand above columns to new row.
Result:
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLMy0stUiguTSotSipWitWJVjICivrlF5VkpBbloUgYAyWC80sxJUyAEq6JxSXo4qZA8fBUNPFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CatchmentID = _t, Description = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CatchmentID", Int64.Type}, {"Description", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Postcode", each Function.Invoke((id as any) => Table.SelectRows(CatchmentPostcodes,each [CatchmentID]= id),{[CatchmentID]})[Postcode]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Address", each Function.Invoke((id as any) => Table.SelectRows(CatchmentOffices,each [CatchmentID]= id),{[CatchmentID]})[Address]), #"Expanded Postcode" = Table.ExpandListColumn(#"Added Custom1", "Postcode"), #"Expanded Address" = Table.ExpandListColumn(#"Expanded Postcode", "Address") in #"Expanded Address"Regards,
Xiaoxin Sheng
4 Replies
Hi Ormesome,
I think you should create a mapping table to link these tables with 'both' cross filter direction. Then use address as the location and use postcode as legend.
In addition, it will be help if you share some sample data to test.
Notice: if your data contains some privacy data, you can remove these part or make some fake data with similar table structure.
Regards,
Xiaoxin Sheng
This is the schema (the spec changed from one office per catchment to many offices):
CatchmentPostcodes - Catchments - CatchmentOffices
[sorry, the forum won't let me insert the screenshot today]
And some anonymised sample data (using McDonalds restaurants) is:Catchments CatchmentID Description 1 Inner suburbs 2 Northern suburbs 3 Southern suburbs 4 Eastern suburbs 5 Western suburbs CatchmentPostcodes CatchmentID Postcode 1 4006 1 4000 1 4001 1 4101 2 4010 2 4035 2 4017 2 4034 3 4110 3 4115 3 4103 3 4108 4 4171 4 4153 4 4152 4 4170 5 4070 5 4060 5 4066 5 4065 CatchmentOffices CatchmentID Address 1 Eagle St Pier, Brisbane Queensland 4000 2 179 Sandgate Road, Albion Queensland 4010 3 Elizabeth Street, Acacia Ridge, Brisbane 3 333 Ipswich Road, Annerley Queensland 4103 4 1006 Wynnum Road, Cannon Hill Queensland 4170 As you can see, the model allows a given catchment to cover many postcodes and to have several offices per catchment.
I want to take a map and colour the postcodes of catchment "Inner Suburbs" blue with dots to show the location of offices in the catchment.
I want to take the same map and colour the postcodes of catchment "Northern Suburbs" red with dots to show the location of offices in that catchment.
etc.HI Ormesome,
Based on test, current power bi not support auto determine the relationship mapping on Multi-records to Multi-records.
On my opinion, I'd like to suggest merge and append these columns to one table, then use it as the source of map visual.
Steps:
1. Duplicate 'Catchments' table.
2. Add custom column postcode to lookup value from 'CatchmentPostcodes' table.=Function.Invoke((id as any) => Table.SelectRows(CatchmentPostcodes,each [CatchmentID]= id),{[CatchmentID]})[Postcode]3. Add custom column Address to lookup values from 'CatchmentOffices' table.
=Function.Invoke((id as any) => Table.SelectRows(CatchmentOffices,each [CatchmentID]= id),{[CatchmentID]})[Address]4. Expand above columns to new row.
Result:
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLMy0stUiguTSotSipWitWJVjICivrlF5VkpBbloUgYAyWC80sxJUyAEq6JxSXo4qZA8fBUNPFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CatchmentID = _t, Description = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CatchmentID", Int64.Type}, {"Description", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Postcode", each Function.Invoke((id as any) => Table.SelectRows(CatchmentPostcodes,each [CatchmentID]= id),{[CatchmentID]})[Postcode]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Address", each Function.Invoke((id as any) => Table.SelectRows(CatchmentOffices,each [CatchmentID]= id),{[CatchmentID]})[Address]), #"Expanded Postcode" = Table.ExpandListColumn(#"Added Custom1", "Postcode"), #"Expanded Address" = Table.ExpandListColumn(#"Expanded Postcode", "Address") in #"Expanded Address"Regards,
Xiaoxin Sheng
Hi guys, Anonymous Ormesome
I use the map vizualization to accomplish something similar - I have a map displaying where my colleagues live (location) and how many colleagues live in that town (size of bubble)
I would like to add circles with a defined size (cluster size, in km, see image) with the center as a fixed town (cluster center, see image).
The sizes of the bubbles are 0-5km; 6-10, 11-15, 16-20 etc. always +5
How can i accomplish this without not being able to zoom in anymore and have the dynamic beauty of the map visual?
Thanks heeps in advance.
Jonas
