Hi,
I'm trying to plot locations from two different tables on one map, and then filter locations from one table only (i.e. all locations from second table remain on the map). The map shows preferred supplier locations and transactions with non-preferred suppliers. I want to be able to filter the transactions data only (by cost centre, or other factors) and the map show filtered transactions but continue to show all (i.e. unfiltered) preferred supplier locations.
I have:
Table 1 - Preferred Supplier Locations (i.e. a list of supplier sites)
| Supplier Name | Site Name | Latitude | Longitude |
| Supplier 1 | Alpha | data filled | data filled |
| Supplier 1 | Bravo | data filled | data filled |
| Supplier 1 | Charlie | data filled | data filled |
Table 2 - Transactions (Purchases made at non-preferred supplier locations)
| Supplier 1 | Preferred Site Name | Latitude | Longitude | Cost Centre | Transaction Number | Product | Quantity | Unit Price | Total Price |
| Supplier 2 | Name A | data | data | 123 | data filled | data | data | data | data |
Supplier 2 | Name B | data | data | 456 | data filled | data | data | data | data |
| Supplier 3 | Name C | data | data | 789 | data filled | data | data | data | data |
| Supplier 2 | Name B | data | data | 789 | data filled | data | data | data | data |
| Supplier 3 | Name C | data | data | 123 | data filled | data | data | data | data |
I have created a new table in Power Query that appends common columns from these two tables (meaning there are duplicate rows due to different transactions at same location)
Table 3 - MapData
| Supplier Name | Supplier Site Name | Preferred Site Name | Latitude | Longitude |
| Supplier 1 | | Alpha | data | data |
| Supplier 1 | | Bravo | data | data |
| Supplier 1 | | Charlie | data | data |
| Supplier 2 | Name A | | data | data |
Supplier 2 | Name B | | data | data |
| Supplier 3 | Name C | | data | data |
| Supplier 2 | Name B | | data | data |
| Supplier 3 | Name C | | data | data |
I have created a map (using Azure map visualization) based on Table 3 - MapData which successfully shows a bubble for each Preferred Supplier Locaiton and for each transaction, colour coded by Supplier Name.
Here's where i run into trouble.
I am trying to apply a slicer to Table 2 by Cost Centre to show only Table 2 transactions on the map along side all Supplier 1 locations from Table 1. But when I apply this slicer filter to Table 2, the data from Table 1 on the map disappears, too.
I have a relationship linking Table 2 and Table 3 using Supplier Location Number (many to many, although i have also tried removing duplicates from Table 3, but that still needs to be many to many as Supplier 1 locations are present).
How do I fix this?
Do i need to combine the data in some different manner, or apply the slicer or relationships differently?
Help!
Thanks in advance!
