Directional flow using Route Map visual for Power BI

Problem

There are limited choice in Power BI to display information in a map with directional flow – meaning show the direction of movement in a sequence of movement data. e.g. tour route, flight route, etc.

In this blog lets explore how this can be achieved in Power BI using the Route Map visual.

 

Route Map visual

Route map visual is from Weiwei Cui and can be downloaded from Power BI store.

As per the description in the site:

Route map is designed to visualize trajectories of objects, such as taxis, vessels, airplanes, and hurricanes. Specifically, a trajectory dataset often contains three aspects of information: time, geo-coordinate, and attribute. In this visual, you can draw the spatial-temporal information on a map with polylines, then use line styles (e.g., colors and widths) to encode desired attributes.

 

Sample Data

The sample “travel packages” dataset I used is as below:

From To Type Route ID Route Order
Canada United States Cruise PK1 1
United States United Kingdom Flight PK1 2
United Kingdom Singapore Flight PK1 3
Australia Singapore Cruise PK2 1
China Singapore Cruise PK3 2
Singapore China Flight PK4 1
China India Cruise PK4 2

 

Data Transformation

The above dataset cannot be directly used for Route Map visual.
1. From and To locations needs to be on separate lines.

2. Need to have geo-location code for the Country names.

Step 1 – From and To locations needs to be on separate lines

To transform the dataset so that we have separate rows for From and To data, we need to “Unpivot” the dataset.

Routes1

Step 2 – Need to have geolocation code for the Country names

To get the geocodes for the countries I used the Get Data from Web.

You can find the geocodes from Google datasets here. I simple created a CSV file with data and loaded it to Power BI. Then I merged the “travel package” dataset and “country geocodes” datasets together.

Capture

Then expand the Tables in the resulting column.

Untitled

For ease of copy and paste the final dataset is below:

Type Route ID Route Order Attribute Value Latitude Longitude
Cruise PK1 1 From Canada 56.130366 -106.346771
Cruise PK1 1 To United States 37.09024 -95.712891
Flight PK1 2 From United States 37.09024 -95.712891
Flight PK1 2 To United Kingdom 55.378051 -3.435973
Flight PK1 3 From United Kingdom 55.378051 -3.435973
Flight PK1 3 To Singapore 1.352083 103.819836
Cruise PK2 1 From Australia -25.274398 133.775136
Cruise PK2 1 To Singapore 1.352083 103.819836
Cruise PK3 2 From China 35.86166 104.195397
Cruise PK3 2 To Singapore 1.352083 103.819836
Flight PK4 1 From Singapore 1.352083 103.819836
Flight PK4 1 To China 35.86166 104.195397
Cruise PK4 2 From China 35.86166 104.195397
Cruise PK4 2 To India 20.593684 78.96288

 

Map Route Visual

When you plot the above data in the Route Map visual, you get the below.

Capture

If you see the directional arrows in the above diagram it is not correct. There is no route from Australia to Canada but the map shows an arrow from Australia to Canada. My understanding is this is because the Route Map is ordering the rows differently similar to what is shown in Values table visual.

Similarly, if I select the Route ID PK1 from the Slicer, the route is displayed as Canada -> Singapore -> United Kingdom -> United States. This is in alphabetical order.

The actual route for Route ID PK1 is Canada -> United States -> United Kingdom -> Singapore.

Capture

Note: There is a bug with Route Map visual as it cannot display the arrows for the UK to USA and sometimes blanks out.

 

Additional Data Transformation To Fix the Problem

To fix the sort order problem I added an Index column in Query editor to the final dataset and added a new column in DAX as below:

Column = Routes[Index] & Routes[Value]

This will add anew column as:

Capture

Now in the Route Map visual I used the ‘Column’ as the value for the ‘Timestamp’.

Capture

Now the direction flow order problem is fixed (though the Route Map bug that it cannot display all the routes remain).

When I select Route ID PK1 now the route is displayed correctly with correct directional flow.

Capture

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: