Tokyo Olympics: Dashboard Alerts & Updates

Today's post is about Power BI Dashboards - what they are, why we need them and how I used it to automate my Twitter posts (more on that in my next blog post). 

Power BI Dashboards

Dashboard is a term that is widely used, but in the world of Power BI it has a specific function. A Power BI Dashboard: 

• displays key metrics that you need to know RIGHT NOW
• can combine data from multiple reports and datasets, in one place
• enables you to set alerts on key measures

I really like this visual, borrowed from the Microsoft Docs on Dashboards to explain how dashboards differ from reports: 

Manage Alerts

It's not possible to embed a dashboard on a public website, so I haven't created my dashboard for aesthetics. Instead, I have focused on the alert functionality and used my dashboard to trigger a Power Automate flow. 

I have chosen to put only card visuals into my dashboard. Card visuals provide a single value that Power BI can monitor. This means we're able to set alerts on any of these card tiles in the dashboard. 

How to set up alerts

To set up alerts on a dashboard: 

1. Pin a card or KPI visual to dashboard (or create one using question and answer). You can pin tiles from your report by hovering over the visualization and clicking the 'pin' icon. 
2. From the dashboard, click the three dots at the top of your card visual and select 'Manage alerts'. NOTE: for alerts to work, this visual must display a single value that Power BI can monitor. You will NOT see the 'Manage alerts' option for a column chart for example.
   
   
3. Set the alert threshold. Note that Power BI will send you an alert ANY time the data changes if it's over/under the threshold you selected. For my scenario, I want to know any time the US wins a medal, so I have set my threshold to 0. Any time the data changes, I'll get an alert that the US has earned another medal. If the number doesn't change, the alert won't trigger (even if the value is greater than 0). Pretty powerful!
   WARNING: If you republish the dataset this will trigger the alert, even if the value hasn't changed, so if you're using it for a similar purpose to me, you may want to update your alerts threshold to the current number, then republish the dataset.
   

Update Dashboard Tiles

Dashboard tiles will update automatically when the dataset is refreshed - they don't need the report anymore to update. That means that if you change something in the report, the dashboard won't change. 

How I broke my dashboard

However, if you rename, remove or drastically alter the measure that is used in the dashboard tile, Power BI won't have any way to calculate that tile anymore. In the image below you can see the first two tiles on my dashboard display the PowerBI icon and no data value. 

This is because they are referencing the [New Zealand Total Tokyo2020] and [United States Total Tokyo2020] measures. After pinning these dashboard tiles, I went back to my report and decided to rename the measures to [NZ Tokyo2020 Medals] and [US Tokyo2020 Medals] in an effort to shorten them a bit. Unfortunately, this change was not picked up by the dashboard. All the dashboard could see was that the original measures no longer existed. 

Therefore the dashboard tiles could not display a value. There is NO alert or warning that this has happened. 

Always check your dashboard after a dataset change/republish

In order to fix this, I simply removed the old broken tiles and repinned the new tiles. It's an easy fix, but a little bit of a gotcha if you aren't aware.

As a rule of thumb, if you make any change to the report or data model (aka you do a re-publish overwriting an existing dataset), CHECK YOUR DASHBOARD and make sure all the tiles still have references and are pulling through the correct data.

Tokyo Olympics: Small Multiples

 It's officially out of preview - small multiples are here to stay and all the kinks have been ironed out. 

Small multiples are a great way to display data when you have too many things for one visual. I wanted to depict the history of each country in the Olympics, and obviously we want to see the Gold/Silver/Bronze split for each year. However, the Summer/Winter difference was also important to me. Particularly as some countries have won almost no Winter medals but do really well in summer. With a simple column chart, you can only split the data by legend once, but with small multiples I could add the Season into the Small Multiples field to show exactly what I wanted.

The great thing about small multiples are that it ensures you have the same axis on all visuals, which is vital for enabling users to make eyeball comparisons of the data. 

How to use Small Multiples

If you have the latest version of Power BI desktop, you should see a Small multiples option on your column, bar and line charts:

Simply drag a dimension field into the Small multiples and viola! Power BI has created multiple small visualizations for you, that all behave with the same formatting. 

Now to iron out the details. Swap to the format view for this visual and you'll notice two new options: 

Small multiple titles

By default this is on, and I think that's pretty helpful. You don't get a legend for small multiples, so it's important to know what we're actually looking at. 

Grid layout

This lets you define the default for how many multiples you'll see without scrolling. Users can always scroll to see more. 

Tokyo Olympics: Mobile Friendly View

 I believe Microsoft still have some work to do in terms of improving the ease of designing mobile friendly reports in Power BI desktop, but overall it's remarkable how much information you can get at your fingertips from your mobile device - on the go!

Here's a quick demo of what the Power BI mobile app looks like on Android and how to navigate your way around: 

Power BI Mobile Layout

You can set the mobile layout in Power BI for both reports and dashboards. The interface is an easy drag and drop. All visuals must snap to the grid, so you don't need to worry about getting pixel perfect sizing - Power BI takes care of that for you. 

In the screenshot above, you will notice that I have dragged some visuals onto the mobile canvas, but there are still 4 visuals left in the 'Page visuals' pane that I have decided not to put on the mobile view of the report. I consider the mobile view to be more of a quick look at the most important info, so have left off most slicers. I also prefer vertical layout for mobile view - try not to make your end user scroll in two directions. Since the mobile view canvas will scroll up and down, use visuals that also scroll up and down. 

Power BI make it really easy to leave visuals off the mobile view, but unfortunately it's not so easy to leave them off the desktop view. If you want the visual displayed in the mobile view, it must be displayed in the desktop view. If you hide a visual in desktop view, it will also be hidden in mobile view. 

Vote for this in Power BI Ideas.

In the meantime, in order to work around this limitation, I find myself creating mobile only visuals and then layering them behind other visuals in the desktop. That way they are still 'visible' in the desktop view, but users don't see them. In the mobile layout, I choose the vertical orientation (bar chart) and leave the landscape orientation (column chart) for the desktop. 

Mobile Layout Design Considerations

When you're designing mobile friendly reports, keep these things in mind:

Test Font Size:

Power BI will auto-scale the text in your visuals for the mobile device, so check what it looks like on your own device before publishing to a wider audience. Ensure you've tested the mobile view with the longest text values in your report so you know how they will appear to your users.

Replace Right Click with Buttons: 

Make sure your users can access all the pages and information they need easily. If there is a lot of right clicking or Ctrl key needed in the desktop version, ensure you give mobile users buttons or instructions on how to get the information they need.

Orient Visuals Vertically: 

Try to keep the mobile view to a single directional (up/down) scroll. This might mean replacing your column charts with bar charts and leaving off some of the more detailed tables (or reshaping them).

Make Space for Scrolling: 

If a visual will have a scroll bar, consider leaving an extra gap on the report so users can scroll the report page or the visual (the native gap width in Power BI mobile isn't quite wide enough for the average finger). 

Fit Visuals to Screen: 

Power BI mobile layouts are designed to scroll indefinitely, so make sure your user knows there's more to see. Ensure you can always see the top of the next visual when the current visual is aligned with the top of the screen. Users shouldn't have to scroll to see the entire visual. NOTE: Not all mobile devices are the same size, so test on the devices your end users will be using. The mobile layout view in desktop isn't necessarily what your users will see.

Tokyo Olympics: Data Category in Power BI

 Today's post is a nice short one on the importance and helpfulness of 'Data category' in Power BI. 

Data Category Power BI

Power BI allows us to categorize any of your columns to help the engine determine how to handle these values. 

Data categorization is primarily useful for: 

• Mapping correct locations
• Web URLs
• Images

If you set the data categorization properly in Power BI, you can get accurate and interactive visuals. 

Example 1: Georgia (the Country)

In our Olympics report, Georgia have won a few medals. Obviously this is Georgia the country, not Georgia the city. It would be nice if Power BI could read our mind, or look in the column at the other values and decipher that most of them are also countries, and pick Georgia the country. Unfortunately, it seems to have a large bias for North America. 

In order to demonstrate this, I have changed the color for Georgia to red in the visuals below, to make it easier to spot. 

Without data categorization

Georgia is plotted in the United States, as the state of Georgia.

With data categorization

When we set the Data Category for the 'Team' column to 'Country' (careful not to choose County!!! look for the 'R'), it's plotted correctly.

Example 2: Web URL in Matrix header

Web URLs in Power BI are a bit tricky. They must start with https in order for Power BI to allow and recognize them. Tables give you the ability to conditionally format any value as a 'Web URL'. This is pretty cool, as it enables us to display one value but link to the underlying URL. You can see this in the images below with the number - each one is underlined because they are a hyperlink to that Olympic Games web page. Conditional formatting with web URLs will work when the column is categorized or not, as long as it is text and has the https. 

In the matrix row header however, this is a different story. 

To display hyperlinks in matrix row headers, you can use the URL icon functionality. Simply turn 'On' the URL icon property in the 'Row headers' section of the matrix formatting:

Without data categorization

Here you'll see that the URL column displays the URL text of the column, even though I have turned the URL icon to 'On' in the matrix formatting.

With data categorization

When we set the Data category for the URL column to 'Web URL', the matrix row headers automatically update to the URL icon, thanks to the formatting we set earlier:

How to set Data Category

In conclusion, setting the Data category for your columns can be a very important step in getting the right results in your Power BI report. To set the Data category, simply select the column in Power BI Fields pane, then in the ribbon select the Data category you want. 

Tokyo Olympics: Cross Filter Direction and DAX Measures

 

In my Olympics History YouTube video I stated that using a Cross-filter direction of 'both' can introduce ambiguity into your data model and give you incorrect results. 

In this post I will give a concrete example of why Cross-filter direction of 'both' can cause errors.

Cross-filter direction

Let's start by defining Cross-filter direction. This is the direction that filters propagate through your data model. In plain English, if the Cross-filter direction is set to single from DimTable to FactTable (which it should be), then we get the following behavior: 

• putting a filter on the DimTable will filter the FactTable 
• putting a filter on the FactTable will NOT filter the DimTable 

As we saw in the video above, this behavior can be annoying at times - we want to filter our DimTable to remove records with no entry in the FactTable. However, the problem arises when we have many DimTables. Let's look at the example below.

In the above image, let's focus on the three tables at right: 

• OlympicMedals (DimTable1)
• OlympicTeams (DimTable2)
• Tokyo 2020 Medal Standings (FactTable1)

You can see that the Cross-filter direction between DimTable1 and FactTable1 is set to both. This means that if we filter DimTable2, it will filter both the FactTable1 AND the DimTable1. 

In this example, if I choose a specific team, for example the  United States of America, that will filter the Tokyo 2020 Medal Standings table. Since the Team USA haven't yet won any medals in Tokyo 2020 at the time of writing, the Tokyo 2020 Medal Standing table is now empty. 

With a Cross-filter direction of both between Tokyo 2020 Medal Standing table and OlympicMedals table, the Tokyo 2020 Medal Standing table filters the OlympicMedals table, so it is also empty. This causes my slicer for Medal Type to be empty: 

 Let's look at another country; Japan was the first team in Tokyo 2020 to win two medals. At the time of writing this post, they have earned a Gold and a Silver medal. In the image below when Cross-filter direction is set to both, you can see that my slicer for MedalType[Medal Type] now shows only two medals; Gold and Silver. This table has again been filtered by the Tokyo 2020 Medal Standings table. 

Single Cross-Filter Direction

To fix the slicer, we need to set the Cross-filter direction to Single. This should be your default setting for a one to many relationship. 

Now our data model looks like this: 

When we return to the report, our United States Olympic Medal History page is fixed - the Medal Type slicer has restored Gold, Silver and Bronze as options. 

Tokyo Olympics: Schedule Refresh in PowerBI.com

 Since the Olympics is in full swing, we want to keep up to date on the latest medal counts and information, automatically. 

In order to do this, we should be able to schedule a refresh on our report since it uses public web data as the source. 

Schedule Refresh in PowerBI.com

There are a few things we need to overcome in order to succeed with the scheduled refresh. You may encounter two error messages commonly when trying to refresh your data in PowerBI.com. These messages relate to:

• Dynamic Data Source
• Gateway

Dynamic Data Source

In order to schedule the refresh on this dataset in PowerBI.com, we need to tweak the source code slightly. If you have a look in Power Query at the SampleResultsTable query we authored using the custom parameter you will see that Power Query generated the following code for Source step: 

= Web.BrowserContents("https://olympics.com/en/olympic-games/" & URLpart)

This code is what is know as a 'Dynamic Data Source', which cannot be refreshed in PowerBI.com

A dynamic data source in Power BI is one which cannot be determined without running the Power Query code. Since this URL requires Power Query to run another query, it is a dynamic data source. In order to fix it, we need to provide a static base URL which can be accessed independently, and then use the RelativePath property to direct Power BI to each page based on our parameter. 

We will update the source step to read: 

= Web.Contents("https://olympics.com/en/olympic-games/", [RelativePath = URLpart])

Data Gateway 

If we try publishing this to PowerBI.com, it now asks us for a Gateway connection. 

A data Gateway allows you to manage access to secure data sources that aren't available otherwise. If you are using Excel files stored on SharePoint or OneDrive and get a Gateway error message on refresh, this post is for you too!

Why the error?

If our data is available on the web with no login required, or in the cloud in SharePoint with the same login credentials we're using for PowerBI.com, why does PowerBI.com insist on a gateway?

Data Gateway & Web.BrowserContents

Web.BrowserContents as a function requires the secure gateway connection to our datset. In order to refresh the dataset we have two options:

• add the Web.BrowserContents data sources to a gateway
• convert all queries to use Web.Contents instead

Data Gateway and OneDrive / SharePoint files

If you connected to a SharePoint or OneDrive Excel file using the Get Data > Excel option, Power BI will use the local path specific to the current computer you're using. In order to access this same filepath, you'll need to build a gateway. Again, you have two options here:

• add the Excel.Workbook data sources to a gateway
• convert all queries to use Web.Contents instead

Add data to Gateway

If you are lucky enough to have admin access to your company's gateway, this is a simple process that you can do from PowerBI.com. If you don't yet have admin access to a gateway, install one!

Your gateway must be installed on a computer that is always running. If the computer stops running or goes to sleep, so does your gateway, and so too will your scheduled refreshes. 

Once you've installed your gateway, using it is straightforward. Simply add each data source to the gateway using the blue links seen in the screenshot above. 

Convert Source: No Gateway Needed

Alternatively, we can convert the source steps to use Web.Contents, and avoid the need for the Gateway. Do I hear a sigh of relief here? 

Web Data

In our Olympics example, this process is easy - simply edit the formula bar for the Source step wherever you see Web.BrowserContents and change to Web.Contents.

NOTE: If you have already published to PowerBI.com, you will need to delete the data source before trying to republish. If you simply overwrite the data source your changes will not pull through.

One Drive / SharePoint Data

For a OneDrive / SharePoint file, I recommend that you use the Get Data > Web option, then paste the Filepath of your desired document. This filepath can be found in SharePoint by clicking the three dots next to the file you want, then select Details (you may need to click More, then Details). Scroll down on the Details pane and click the Icon to copy the filepath.

Once you've loaded this data into Power BI, you can see what the Source step needs changed to in order to avoid the need for a gateway.

Update Credentials

Even though we don't need a Gateway for this method, we still need to update our credentials in PowerBI.com in order to schedule the refresh. When you're in the dataset settings page on PowerBI.com you'll see a prompt to 'Edit Credentials'. You must do this for each data source in your file. Once you've done this once, it will allow you to enable scheduled refresh on the dataset.

In order to Update credentials for Web or SharePoint based data sources; 

Step 0: If you're getting data from SharePoint, ensure you've used the correct link. See more info in my Get Data From SharePoint blog. 

Step 1: Navigate to your workspace in PowerBI.com

Step 2: Click the 'Schedule Refresh' icon next to the dataset you wish to refresh.

Step 3: Scroll to the 'Data Source Credentials' area. Click 'Edit credentials' for each data source and sign in with Oauth (or the correct credentials for your chosen data source).

Tokyo Olympics: Power Query Custom Functions

The Tokyo 2020 Olympics opening ceremony is only days away, and some of the qualifying matches are already underway. I say 'already'; I guess they're only a year late!

This is the first blog in a short series designed to:

• demonstrate common Power BI principles using a non-sales related dataset
• address common pain points when working with non-fact table measures
• keep up to date with the latest stats in the Tokyo 2020 Olympic games
• cheer for the little guy - we're going to throw population into the mix and see how the big players stack up now

Over the coming weeks I'll document how I created the reports using Olympic medals data readily available on the web.

Power Query Parameters

I've blogged about parameters in Power Query before. The 'Get Data from Folder' packages up data for us nicely and does all the custom function work, but what if you have something tricky or different you need to handle in each file? What if all the sheets in Excel are named the same as the filename - the Get Data from Folder won't allow you to pick a different sheet name each time. Parameters can make this possible, but we need to get familiar with using parameters and be able to understand how the data fits together. 

This is an easy demonstration on using parameters, and it uses data that's readily available on the web. As a bonus, you can use this technique to crawl a website to get the lowest prices or read all the reviews on a product you're looking to buy. Parameters are all about finding the patterns in your data source, which in our case today is the URL of the website. 

In this sample, we connect to Olympics.com using two main URLs: 

• Parameter Table: https://olympics.com/en/olympic-games/olympic-results

• Sample URL: https://olympics.com/en/olympic-games/pyeongchang-2018/medals

The https://olympics.com/en/olympic-games/olympic-results site gives a simple table of all the Olympic games throughout history, and thankfully they are formatted in a way that is very similar to the Sample URL for each medals table. 

When pulled into Power Query, the above table looks like this: 

With just a bit of Power Query Magic, we can format it to match the URL for the Sample URL file: 

In the first half of the video below, I walk through how I turned this table into a complete list of all Teams who have won medals for each of the Olympic games. 

Custom Visual Review: Sankey

Auckland: Living City

Today's post is inspired the the Auckland Museum Living City exhibit that projects larger than life data visualizations onto the floors and walls of Auckland Museum. Check it out if you're in New Zealand, or sneak a peak at the Auckland Museum Living City Instagram post for my overseas readers. 

Sankey Custom Visual

Scores

Data Clarity: 6/10

Versatility: 3/10

Fun Factor: 6/10

PBI Certified: Yes

Description

The Sankey Chart is a specialized type of visual designed to display the flow of data from source to destination. For that reason, it can be difficult to find the right data to use with this visual or you may need to perform many transformations on your data model to get it into a format ready to use with a Sankey visual. 

I have used it to help display the breakdown of complex hierarchies - turning it into a flowchart of the parent - child relationships in the dataset. It's commonly used for the energy industry, but as Auckland Museum have shown it works well with anything that moves in which you want to show the relationships from start to finish. 

How To

Format the visual

The Sankey visual enables the end user to manipulate the data by dragging and dropping the nodes to move the links around and change the overall flow of the visual. 

The Sankey visual has a few basic formatting options: 

Data labels: This will turn on the labels for the nodes. Of course you have the typical font size, color, etc. There is also an option here to turn on Force display, which will ensure that all nodes are labeled without the user needing to hover over the visual. This is great for small datasets, but can make larger datasets look very cluttered. Unfortunately, there is no option to specify which nodes to force display for, it's either: default, all or nothing.

Data link labels: This will turn on the labels for ALL the data links. You'll see in the video below that this can be time consuming and clutter!

Links: This simply lets you choose a color for all the links. You must individually set the color for each unique link combination.

Nodes: This lets you choose a color for each of the nodes, again this must be done individually for each node. 

Scale settings: This is a useful option that lets you change the scale. The default is set to 'Provide min optimal height of node' which can make it difficult to see which node has a larger population/weight. Turning this off can make some nodes too small to see, but also displays the nodes accurately to scale. There is also an option for logarithmic scales here.

Cycles displaying: Not applicable for this dataset, but useful for displaying the flow of items that can return back to their source. 

All the other formatting options are as standard visuals, formatting for title, size, background etc. 

This is definitely a time consuming visual to set up, but the rewards can be great if you put in the effort and have the right data to display. 

Model the Data

The most challenging part of using the Sankey visual might just be getting your data formatted properly. If you're wanting to use the Sankey visual for a dataset that isn't curated for Source and Destination (such as showing multi-level hierarchical parent child relationships), you may need to transform the data to make it all visible in the Sankey. 

In the video below I show one method for turning a flat tabular table into a Source > Destination table for Sankey visualization. Here you'll see how to create a series of queries that reference and group your raw data, and then finally append all those queries into one long table for the Sankey visual.