Display multiple entities on Power BI map control

Photo by Susannah Burleson on Unsplash

Recently I had to display location of multiple entities on a CRM dashboard. The requirement was to display all Workorders, Projects, Resources and Bookings in a map control so the project scheduler / field service dispatcher could see where is the location of each Workorders, Projects, Resources and Bookings on map. The bing map control works fine on individual entities which are enabled for geolocation however, in this scenario I had to plot all different entities on a single map.

My thoughts were that I could choose from one of the following methods:

1. Use bing map control on a dashboard. Use a webresource to retrieve all entities in Workorders, Projects, Resources and Bookings. And then use a draw function to place each entity location on the bing map.
    
2. The second approach was to use Power BI and its Visual Map control to plot all entities on a map. Then host the Power BI control on my dashboard. I decided to use this approach to display entities on a map control.

Power BI Map control to show multiple entities

The map control in Power BI uses one source table with longitude and latitude information to display table rows on map. The challenge with this approach is that the visual map control supports only one entity's longitude and latitude and therefore we can only use one entity as source of the map data. In my scenario I had multiple entity types i.e. Workorders, Projects, Resources and Bookings. Each of these entities have its own longitude and latitude and we cannot use all these entities together as  a source for our Power BI Map.

The way I overcome to this challenge was to use a temporary table to union data from all Workorders, Projects, Resources and Bookings in this table and use this temporary table as the source of Power BI Map control. This is how I did it: 

1. Connect to the CRM Bookings table. This will bring all columns of the table to the Power BI.
2. Remove unwanted columns in the Query Editor (optional).
   = Table.SelectColumns(Source,{"name", "msdyn_longitude", "msdyn_worklocation", "bookableresourcebookingid", "msdyn_latitude"})
    
3. Reorder remaining columns in a way that you like to see your data (optional).
   = Table.ReorderColumns(#"Removed Other Columns",{"name", "msdyn_longitude", "msdyn_worklocation", "msdyn_latitude", "bookableresourcebookingid"})
    
4. Rename column headings (optional).
   = Table.RenameColumns(#"Reordered Columns1",{{"bookableresourcebookingid", "id"}})
    
5. Filter rows that you want to exclude from map (optional).
   = Table.SelectRows(#"Renamed Columns", each [latitude] <> null)
    
6. Add a custom column to the query as TABLE Identifier/Category so you can identify workorder rows in the union table.
   = Table.AddColumn(#"Filtered Rows", "category", each Text.Upper("Bookable Resource Booking"))
 
7. Change the column types (optional).
   = Table.TransformColumnTypes(#"Reordered Columns",{{"category", type text}, {"longitude", type number}, {"latitude", type number}})

If you have more than one entity, repeat the above steps for each table in your query editor.

The next step is to create a temporary table and union all the above tables data using DAX query into this temporary table.

1. Go to Modeling Table.
   
2. Click on New Table. Use the below query to fill the table (Alter table names based on your scenario),
   TempTable = UNION('Bookable Resource Booking','Bookable Resources','Work Orders','Project Sites')
3. Drag a Map Visualisation control to the Power BI.
4. Select "Category" or Entity Name from the TempTable as Legend. This will ensure to show your entities in different colors.
   Drag longitude and Latitude fields to the X and Y axis.
5. Note: By default when you form tables, Power BI adds SUM function to summarize longitude and latitude. These columns with summarize functionality don't work in maps. You must remove summarize attribute from them by choosing "Don't summarize".