Maps: visualise and filter data from different tables

[MilczacaOwca]

Hi,

I want to visualise on map Stores and it's competitors.

In fact table  (dispalyed as visual table) we store information about Store and it's competitor during an event.

When I select in slicer Fair I want to see localisation of Stores and Competitors in map (with different color on map)

I've already did additional table "Map" that combines (UNION) Stores and Competitors with additional column "Type", but I struggle how to make relations. Dimention tables are connced with relation 1:N with fact table.

Any idea how to solve this?

I have factTable

StoreId
CompetitorId
EventId
FairId

And dimension tables

StoreTable
---------
StoreId
StoreName
Longitude
Latitude

CompetitorTable
---------
StoreId
StoreName
Longitude
Latitude

EventTable
---------
EventId
EventName

FairTable
--------
FairId
FairName

[AllisonKennedy · Accepted solution]

@MilczacaOwca  I think you need to Unpivot StoreID and CompetitorID in your Fact table. 

Then you'll have:

Fact Table: 

FairID | EventID | Type | StoreCompID

Your UNION (in Power Query we call it 'append') of Competitor and Store tables is correct - are the IDs unique in these? If not, you'll need to merge columns Type and StoreCompID to get a unique ID in both tables: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html

Your new UNION table should have:

StoreCompID | Store Name | Lat | Long | Type

where Type is Competitor or Store

Then create a new measure:

[FactTransactions] = COUNTROWS(FactTable)

Put that in the Size of a map visual and put the UNIONdim[Type] in the legend and the UNIONdim Lat and Long in the lat long fields of the map.