Forum Discussion
Maps: visualise and filter data from different tables
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
FairIdAnd dimension tables
StoreTable
---------
StoreId
StoreName
Longitude
Latitude
CompetitorTable
---------
StoreId
StoreName
Longitude
Latitude
EventTable
---------
EventId
EventName
FairTable
--------
FairId
FairName
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.
1 Reply
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.