Map to show legend from a different table with multiple entries

[JRadford]

I have three tables; Example (pop), Example (equip), Example (lat, long). Each has the column, port code which I've based the relationship on (I have to use this column). I have a table of every location Example (lat, long) which is what im using for latitude and longitude.  The bubble size is from the table Example (pop) 'Pop'.  

What I need is for the map's legend to show the table, Example (equip) "hierachy" and those locations that are not on the Example (equip) table to still show the locations from Example (lat, long) on the map but be in gray. Unfortunately if I add Example (equip) "hierachy" to the legend, all other locations from Example (lat, long) disappear even if I click "show items with no data". 

(Basically I need the first map and second map to merge)

Example (equip) Is a table that is modified often. 

Example (lat, long) is static with all locations and coordinates.

Example (pop) has population estimates. 

Any help would be great!!

[v-echaithra]

Hi @JRadford ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @JRadford ,

Thank you @nielsvdc  for the updates.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[nielsvdc · Accepted solution]

Hi @JRadford, if I understand correctly, your table Example (equip) does not contain all areas and the other 2 tables do. And you want to show all areas, even when you have no hierarchy in the Example (equip) table?

I think you can do it in two ways.

Option 1: merge all tables into one table

As all the tables have the combined column area code, which is the relation between all tables, you could merge all tables into one table with a left join using Power Query, using the table that has all areas as the lead table. Then some area will get a value of null for the hierarchy and you can replace null with the value 0 or -1. This way you don't have to use "Show items with no data".

Option 2: Merge only the Example (equip) with the (lat, long) or pop table

Assuming that the Example (lat, long) and Example (pop) contain all areas, you can merge one of these tables with the Example (equip) table using Power Query, so that the equip table also contains all areas, although the hierachy might be null. Then replace null with the value 0 or -1.

Hope this helps. If so, please give kudos ‌‌👍 and mark as Accepted Solution ‌‌✔️ to help others. If you resolved your question, let us know what worked for you.