Tooltips in Power BI

The Power BI 'tooltip' is available in almost all Power BI visuals. What is a tooltip?

Tooltip: a message which appears when a cursor is positioned over an icon, image, hyperlink, or other element in a graphical user interface

View the Live Power BI report here

Power BI Tooltips

Almost every visual, both built-in and custom, has a tooltip section of the fields that can be added to the visual. By default, any fields that are included in the 'Values' will automatically show up in the tooltip, but you have the ability to add additional fields to the tooltips. 

Default Tooltip

With the default tooltip, you have very little control over the order and format of the fields:

• Fields used in the visual as 'Values' will be listed first, in the same order they are listed in the 'Values'
• Fields used in the visual as 'Tooltips' will be listed under the 'Values' fields, in the same order they are listed in the 'Tooltips'
• Fields used in the visual as 'Values' cannot be removed from the tooltip
• Fields used in the visual as 'Values' must have the same format in both the visual and the tooltip

Now I have a few complaints with these settings for my current visual. I am only plotting the Auckland alert level, but would like the tooltip to display the alert levels for all of NZ, in a natural language friendly manner rather than the label and numerical value. 

With the tooltips feature I could add the natural language statement (see visual above for 'Lockdown March 2020 Key Event Value: Level 4 Nationwide') but I cannot remove the value used to plot the levels in my graph (see visual above for 'Lockdown March 2020 AKL Level: 4.00')

I would also like to reorder the fields so that the March 2020 stats are grouped (including the friendly natural language tooltip) and the current stats are grouped.

Report Page Tooltip

If we move from the 'Fields' to the 'Format' section of our chosen visual, we find a 'Tooltip' formatting option that can be expanded. This allows us to change the 'Type' to 'Report page'.

Once you select the 'Report page' as the tooltip Type, a new dropdown will appear for 'Page'. All the other options below this Page dropdown apply only to the Default tooltips, so ignore them on Report page tooltips.

How to Create Report Page Tooltip

Step 1: Create Tooltip Page Format

Add a new page to your report. Click the Format paint roller to format the page: 

• Page information: turn 'Tooltip' to 'On'
• Page size: set 'Type' to 'Tooltip'

Step 2: Add Tooltip page Field

Add the same field used in the axis of your visual to the 'Tooltip' section of the Visualizations pane. This 'Tooltip' section only appears if you have done Step 1.

Select a single value in this filter so you can visualize and test the tooltip design.

Step 3: Design Tooltip page

Add any visuals or information to your page. Keep in mind this is a tooltip, so it will be small and users cannot click or interact with it - so keep it simple! However, you can use conditional formatting, color, maps, charts or any visual tricks and features you want just like a normal Power BI report page. 

I've kept mine simple and just grouped the information I want in an easier to read format.

Step 4: Update Visuals Tooltip settings on other pages

By default, any visuals that use the field you added to the 'Tooltip' field will update to use the Report page tooltip instead of the default tooltip. In my example, any visual that uses the Days[Days In Lockdown] field will automatically start using my tooltip page instead of the default tooltip. 

If I create another tooltip page using that same field, Power BI won't know which to pick so to be safe, go find the visual where you want the tooltip.

Select the visual and click the Format to format the Report page tooltip options we discussed above: 

• Change 'Type' to 'Report page'
• Change 'Report page' to 'Tooltip Page Name' (where 'Tooltip Page Name' is the actual name of your tooltip page you created in Step 1)

Tooltips are Easy and Effective

That's all there is to it! Tooltips are an easy and effective way to make your report more meaningful and accessible to all audiences. 

Visual Review: Area Chart

I spend a lot of time using line and column charts, but in trying to overlay different types of information for different time periods, I have discovered that the Area Chart is really effective and underutilized (at least by me).

Today I'm using it to overlay two lockdown time periods and visualize the total number of COVID 19 cases in each period. 

View the live Power BI Report

Area Chart

Scores

Data Clarity: 8/10

Versatility: 8/10

Fun Factor: 7/10

PBI Certified: Yes (Built-in default visual)

Description

The Basic Area Chart is one of the original built-in visuals. It is useful for showing trends over time. Specifically it highlights the volume trending over time, because it shades the area under the line. If you remember back to high school calculus that area under the line/curve is the volume. Area charts do a great job of focusing the eye on the full volume, making it easier to see the big picture and compare values. I've found it's a good compromise to using cumulative values - just the simple act of shading the area under the line causes the human eye to include all the historical values with the current value and aggregate them. 

As you hover over the area chart, you also get a line showing where you are, which again makes it easy to compare the two regions to left and right of your cursor and see the difference in VOLUME for the two areas. 

How to

Fields

The Area Chart accepts data into the following field categories: 

• Axis
• Legend
• Values
• Secondary values
• Small multiples
• Tooltips

Axis

This should ideally be a date, time, duration or continuous (usually time based) field. In my example I have used 'Days in Lockdown' as my continuous time measure.

Legend

This can be used for categorizing your data. Either use Legend OR put multiple fields in the Values category. If you have something in legend, it will determine the different colors and lines (we call these 'series' in the data viz world). In my example I have opted to use multiple values, so must leave legend empty.

Values

Almost every Power BI visual has a values pane - put your explicit measures here. If your 'Legend' category is empty, you have the option to put multiple values in the Values category. In my example I have two measures in here to show the Alert Level in Auckland on any given day:

• Alert Level AKL March 2020 
• Alert Level AKL August 2021

Secondary values

This enables you to visualize data with different scales on the same chart. I have used it to overlay the Alert Levels behind the COVID Cases Reported. The highest alert level is 4, while the highest number of NZ COVID Cases Reported on a single day is over 80. If these were both plotted on the same scale we wouldn't be able to see the change in alert levels very easily. In my example, I have put the COVID Cases Reported in the secondary values:

• Lockdown March 2020 Cases
• Lockdown August 2021 Cases

Small multiples

This lets you create multiple charts without the need to copy paste. It ensures the scale and axes are consistent across all the visuals, ensuring that your report reader can safely compare and pick up on patterns across the visualizations. It's a relatively new feature that has been added to all the core Power BI visuals and I have blogged before about Small Multiples. 

Tooltips

Tooltips are also available on most Power BI visuals - I will be doing a separate blog on Tooltips where I compare the built in tooltips to Report Page Tooltips.

Formatting Options

Once you've got the data into the visual, it's time to format it nicely. Select the visual, then click on the format paint roller to view the formatting options. 

General

For most visuals this has the responsiveness, size, position, layer order and alt text. The Area Chart General formatting (and line chart too) have an extra option in here for High Density Sampling. 

> High Density Sampling

High Density Sampling is a feature that ensures Power BI visuals capture all the outliers in your data and provide a true representative sample even when working with many data points. By default this is set to 'On' and you should leave it that way.

In the visual below, you can see that with high density sampling turned on, there's a break in the pink negative areas. With high density sampling turned off, that break is missed because the visual is only plotting regular samples at specific time periods, and misses the maximum value in that range. The high density sampling algorithm ensures that the min and max values are always plotted, and therefore we see the more accurate representation of the data which indicates a break in the negative pink area:

Legend

The 'Legend' section will only appear under formatting options if you have either:

• A field in the 'Legend' category, OR
• Multiple fields in the 'Values' category, OR
• Multiple fields in the 'Secondary values' category, OR
• A field or fields in both the 'Values' and 'Secondary values' categories

I particularly like to play around with the 'Style' section of the Legend. Depending on what options you select further down (in Shapes section) you may want to change the default Style.

X axis

By default, this will be set to 'Continuous' which will ensure you can see the entire time range on one screen, but may also result in some sampling, so make sure you've selected the right 'High Density Sampling' option from the General section when Continuous is set to on. 

This dataset has been curated so I decided how many days I wanted to visualize, therefore the 'End' of x axis set to auto will plot all 100 days I created in my GENERATESERIES column for the x axis. You may wish to use the Conditional formatting option here to find the last point in time with data and ensure the x axis stops there. Wonderful power and flexibility! You can even apply conditional formatting to the axis labels (for example on a categorical axis you could change the color for weekends vs weekdays - how cool is that?)!

Y axis

The Y axis settings are mostly a mirror of the X axis. If you're using multiple fields in the 'Values' you may want to turn on and change the Title of the Y axis so that people know which numbers relate to which areas in the chart.

Secondary Y Axis

These settings only appear if you're using the 'Secondary Values' and are the same as the Y axis settings.

Zoom sliders

This is a relatively new feature that I'll leave for another post on another day, but basically it lets the user change the axes start and end values dynamically to focus on what they want to see.

Data colors

Unfortunately, there is no conditional formatting for data colors in the Area Chart, which is one of the things keeping it from getting a higher score in versatility. It's possible to set the data colors specific to each category or series if you're using multiple values or legend.

Data labels

Data labels can be customized by series - just scroll to the bottom and turn customize series to 'on' (will only work if Legend is a formatting option). 

An important thing to note - the data labels don't enable you to show the series label for multiple values fields, but you can change the color so that it's easier to tell which numbers relate to which series. Which of the above images do you prefer? I don't like using color as the only distinguishing feature, so this is another reason this visual doesn't get higher scores for data clarity.

Shapes 

This might be where the fun factor comes in, but it's also a really useful way to differentiate between your series. 

I'm using the area chart to compare two distinct time periods - and to forecast based on previous values. Therefore I want the shapes for the previous 'forecast' to be dashed, making the actual current values stand out more. You can also adjust the stroke width and turn on markers to distinguish data points from the line connection. If you have stepped data (such as my Alert Levels) that you don't want a diagonal line for, turn the stepped layout to 'On'.

Plot area

This just contains the standard plot area format options - adjust the transparency or add a background image. 

Note: this is different to the Background image and transparency - plot area does not include the legend and title of the visual.

Title

Includes all the beautiful title formatting options, including conditional formatting and word wrap if you have long titles or tiny screens.

Background

This is where you set the transparency and image for the entire visual - useful if you have something behind this visual that you want to hide or show (which sometimes happens when I'm creating mobile only visuals since Power BI has no option to hide a visual in desktop but show in mobile). 

Otherwise I leave these as defaults.

Lock aspect

Turn this on if you want the visual to behave like images in Word or PowerPoint. 

When Lock aspect is set to Off (the default), you can grab the corner of your visual and drag it anywhere - changing the aspect ration of the visual as you do. 

When Lock aspect is set to On, you can grab the corner of your visual and it will only let you drag it along the diagonal of the existing visual - thereby maintaining the current aspect ratio. 

If you resize using the side, top, bottom or General section of the visual Lock aspect has no impact. 

Border

If you turn this on, you can add rounded corners by adjusting the 'radius'.

Shadow

Make sure you leave space between your visuals if using the Shadow effect. 

Tooltip

This is how you can change what the additional information that shows up on hover looks like - I'll detail these settings in a separate post because they are very cool and deserve their own post.

Visual header

A while back the visual header was updated to a new look and feel, and now you can control which features show up at the top of the visual when users hover or select the visual. 

Note: This only impacts the header behavior in PowerBI.com so you need to publish the report to see the impact of the visual header changes.

Power BI Forecasting with irregular time periods

Will Auckland move to Alert Level 3 on Monday?

It's day 31 of Lockdown Level 4 for us Aucklanders (how did that happen and what have I done with all that time?!). Monday we will find out if Auckland stays in Level 4 or moves to another alert level.

This is already Auckland's longest time in Level 4, and it seems everyone is getting stir crazy; We've all walked our neighbourhoods so many times we know every nook and cranny by heart. It's been rainy the past week or so, so we've also probably finished all the puzzles in the house (twice), read plenty of books, watched the Top 10 on Netflix, run out of flour from excessive baking and put on a few kilos. Well, that's been my lockdown anyway. 😂

After all that activity, I'm left wondering what Monday's announcement will bring for us Aucklanders; will we get to enjoy someone else's cooking skills with Lockdown Level 3? (See my Lockdown with Takeaways post if you're not from NZ.) Or will we stay in Level 4 a bit longer to beat the delta variant? (If so, send me your favourite recipes - I need new ideas!)

I have been wanting to overlay the current case numbers with the last big lockdown we had in Auckland, so finally got around to making it happen. 

View the Live Power BI report

DAX Data Overlay for Forecasting

Power BI has some built in forecasting that will automatically calculate and forecast, allowing you to change the number and length of periods, as well as the seasonality. However, this doesn't work for irregular time frames. COVID lockdowns in New Zealand don't really have a seasonality. It also doesn't allow you to overlay past actuals at the same time as forecasting, so I decided to create some crazy DAX measures to show what I wanted.

GENERATESERIES: Days since start of Lockdown

The first calculation I made was to create the X-axis: Number of Days in Lockdown

DAX has an easy function that can make this axis for you. Click New Table to create a new table for this calculation: 

Days In Lockdown =

GENERATESERIES (

    -10,

    100,

    1

)

This creates a single column table with numbers ranging from -10 to 100, increasing by 1 each row. I decided to start at -10 because the first lockdown in NZ started about 10 days after the first reported case, so this would ensure we could see that history in the graph.

Key Dates table

The next step was to list all the key dates and alert levels throughout both lockdowns. I know that I can use approximate lookup in DAX to display the current level if I just provide a list of Alert Level start dates, which is conveniently compiled on the Timeline of Key Events COVID19.govt.nz website.

This table should NOT be related to any of the other tables in the data model - we'll make all the magic happen with DAX.

Along with key dates table, I created two measures for the start date of the two periods I wanted to compare: 

Key Start Date Period to Compare

Lockdown March 2020 Start Date =

DATE ( 2020, 3, 23 )

Key Start Date Current Period

Lockdown August 2021 Start Date =

DATE ( 2021, 08, 17 )

If you prefer, you could add a Parameter (an unrelated Date table) allowing users to select the Key dates themselves. This will make it possible to compare dynamic date ranges and custom time periods.

DAX Variables and Approximate Lookup: Convert Date to relative time since Start of Lockdown

I'm comparing to the start of lockdown, but you can use this method to compare any two time periods by relative length/duration. We simply need to use the value of the GENERATESERIES column we created, add that to the value of the Key Start Date value. I'm calling this value my 'CaseDate', but it's the date in your Fact table. 

Key Date Approximate Lookup

For the alert level status, we need to use the DAX approximate lookup technique, I have chosen the MAXX(FILTER( option: 

Lockdown August 2021 AKL Level =
VAR _StartDate = [Lockdown August 2021 Start Date]
VAR _Duration =
    MAX ( 'Days In Lockdown'[Days In Lockdown] )
VAR _CaseDate = _StartDate + _Duration
VAR _KeyDate =
    MAXX (
        FILTER (
            'NZ Key Dates',
            'NZ Key Dates'[Date] <= _CaseDate
        ),
        'NZ Key Dates'[Date]
    )
RETURN
    IF (
        _CaseDate
            <= TODAY (),
        SUMX (
            FILTER (
                'NZ Key Dates',
                'NZ Key Dates'[Date] = _KeyDate
            ),
            'NZ Key Dates'[AKL Level Number]
        )
    )
 

This will allow us to calculate the Alert Level in Auckland on each relative day since the Key Start Date of Lockdown. 

Key Date Exact Lookup

The DAX for the Case numbers is a bit simpler, since we have a value for each date that has cases (unlike the Key Dates table which only contained the Start date value). Therefore we don't need the MAXX calculation and can do an exact lookup: 

Lockdown August 2021 Cases =
VAR _StartDate = [Lockdown August 2021 Start Date]
VAR _Duration =
    MAX ( 'Days In Lockdown'[Days In Lockdown] )
VAR _CaseDate = _StartDate + _Duration
RETURN
    IF (
        _CaseDate
            <= TODAY (),
        COUNTX (
            FILTER (
                'NZ Cases',
                'NZ Cases'[Report Date] = _CaseDate
            ),
            'NZ Cases'[Index]
        ) + 0,
        "TBC"
    )

This allows us to calculate the total case numbers on each relative day of lockdown since the Key Start Date: 

Then put them both together in an area chart and voila! Easy to see the patterns and similarities and differences between the two time periods. 

Sunday's spike of 40 new cases is concerning for what Monday's decision may bring, but only time will tell the actual result. For now, I'll content myself with forecasting and playing with Power BI visuals.

Welcome to Level 3: Lockdown with Takeaways

Auckland and north remain in Level 4 lockdown for the time being, but for those of you living south of the Bombays in New Zealand - welcome to Level 3 lockdown, or as it is affectionately called: Lockdown with Takeaways.

View the Power BI Report

Lockdown with Takeaways

For those of you who don't have the great fortune of living in New Zealand, Level 4 lockdown requires all non-essential businesses to shut. This basically means we can go to the supermarket and pharmacy, but nowhere else. I'm still unclear on how a store gets classified as 'supermarket', as butchers and bakeries cannot open, but veggie stores and convenience stores can open. Level 3 enables any store or restaurant to open for contactless service. This means we can get 'takeaways' as we call them here in NZ, or 'fast food' as you may call them. You can also act as your own delivery driver, as click and collect is available if it can be done contactless. 

Flatten the Curve

In her daily briefing yesterday, the Prime Minister showed off another visual aid (last week it was a map of close contacts of known cases): a graph of where the New Zealand case numbers could be had we not gone into Level 4 lockdown. 

I have been tracking the NZ COVID 19 cases on a logarithmic scale since the beginning, but yesterday's briefing inspired me to highlight it more prominently.

Page Navigation Buttons

I realized that depending on your browser, screen resolution, device, etc, accessing the built in page navigation of Power BI might be tricky. So, I have decided to build it into my report pages.

The image above shows an example of what you can create using Power BI buttons.

How to create a Power BI Table of Contents

Creating Buttons in Power BI is easy, it just takes time and care to ensure you have set all the correct actions, tooltips and formats for each button. 

In the page above, I have actually created 8 buttons - two for each page. I did this because I wanted the flexibility of being able to change the image without needing to use another application to merge it with the label, and I wanted the user to be able to click either the image or the button in order to navigate.

Step 1: Insert Button

On the Insert tab in Power BI, you'll see Buttons, Shapes, Image. Any of these can actually be used as a 'Button', so pick whichever you prefer. In the above I used Buttons > Blank.

Step 2: Format Button

If you chose an image as your Button, you have already done half the work. Now just format the size, border, etc.

If like me you chose a Blank Button, we need to fill it with an image. Expand the 'Fill' property of your button and click the + to add an image.

Set the transparency - I set mine to 0% because I am creating the text labels as separate buttons.

Step 3: Button Action

Finally, turn the Action to 'On'. Note, this can be done for a button, shape or image, so it doesn't matter what you picked in Step 1.

Choose 'Page navigation' for the Type and select the page you want to navigate to. Note: This can be conditionally formatted which means your button behavior can change depending on the user selections. How cool is that?!

Finally, type something useful in the 'Tooltip' box. This does take extra time, but I think it makes your report MUCH easier to use, so is worth spending the time now as it will save you answering lots of questions from users later.

If you don't want to add a tooltip, leave it blank for the default 'Click here to follow link' message, or as of this month you can even turn the tooltip off!

Step 4: Repeat

Now that you have one button, repeat the process for all other buttons (you can save some time with copy/paste, but you'll need to set all the actions). This is where the care and testing comes in - it's very easy to miss a tooltip or action if you're not careful.

Don't forget to add back buttons on all the other pages so that the user can return to the table of contents.

Try it Out

Have a go navigating through the report below to see how the buttons work. There are lots of other ways you can use buttons for navigation - add them as tabs to the top or side of your page, use a slicer to let the user select the desired page and click 'Go', or tell a story enabling the user to navigate through the report in a specific sequence.

Preview Feature: Data Point Rectangle Select

Today is the day New Zealanders will learn the fate of COVID 19 Lockdown levels for the next few days/weeks. Those of us in Auckland are guaranteed Level 4 through Tuesday, but the rest of the country holds hope of moving down to a lower level in time for the weekend.

What do the numbers look like? 

Since we've had a long (enjoyable) gap with no Community COVID 19 cases in NZ, I thought this would be a good excuse to play around with the Power BI preview feature: Data Point Rectangle Select

Data Point Rectangle Select

In order to use this in your reports, you must:

Step 1: Enable the Preview Feature

Open Power BI Desktop

Click File > Options and Settings > Options

Tick the box to enable 'Data Point Rectangle Select' and click OK.

Restart Power BI Desktop.

Step 2: Use It!

If in Power BI Desktop, you'll need to use the Ctrl key to activate this feature. If in Power BI service it works with just a simple drag and drop. 

That's it! You've now got the power to select specific areas, outliers and ranges using visuals as filters. 

Demo

Below is a short 1 minute video I made on Wednesday, showcasing how I used this feature to hone in on the most recent COVID 19 outbreak in New Zealand and compare that to our first outbreak and the only other time we were in Level 4 Lockdown. The numbers look pretty comparable, but hopefully more isolated to one part of the country.

Stay Safe NZ!

BONUS: Toggle and Bookmarks

At the very end of the video, you'll notice I toggle the map to show the COVID 19 cases for all time across New Zealand, rather than focusing just on the current active cases. I have a tendency to go overboard with Power BI DAX, visuals, and features, which can lead to information overload if you're not careful. 

Historically I've struggled with deciding what information to cut, or how to make it easy for the user to choose without taking up precious real-estate on my screen. 

Well, the Power BI toggle might just be the perfect solution. Thanks to Havens Consulting: Creating Sliding Toggles With Native Buttons in Power BI for the tutorial and David Johnston's winning Enterprise DNA report for the inspiration to toggle! Check out their videos and let me know your thoughts on toggles.

Refreshing COVID-19 NZ Data: RelativePath and Query options with the Web.Contents M function

The first of the Paralympic Games medals have been earned already - I'll be posting a blog soon on how to use .pbit files and update data sources using parameters. 

COVID 19: New Zealand is now in Level 4

Today though I'm going to digress slightly from the celebratory mood of the Olympics and return to the COVID 19 data analysis. I've had a few requests from friends and family to dig up my old report and visualize what's going on in the NZ community (not including MIQ facilities). View the full COVID 19 NZ report in Power BI.

Question: What do the NZ Community COVID cases look?

The Ministry of Health website graphs all NZ cases, including those in Managed Isolation and Quarantine. It's difficult to get a good picture of how many community outbreak cases we're dealing with, and how that compares to the other outbreaks we've seen in New Zealand. 

So what does the COVID 19 data look like over time if you remove the Managed Isolation & Quarantine cases?

Unfortunately, the Ministry of Health website has changed quite a bit since I first created the COVID report, and there are no longer any web based tables available to use as data source.

Challenge: Auto Refresh a Dynamic Data Source

Instead, all the data I need is uploaded daily in a CSV file which includes the name of the date in the filename. The web link to access this CSV file is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

In order to make the refresh dynamic, I'm going to use a few Power Query tricks. 

Step 1: Identify Data Source parts

The CSV file seems to be uploaded between 1-4pm each day for that day, so if I schedule the refresh for 4pm daily I can use today's date as part of the data source URL.

Recall the URL is: 

https://www.health.govt.nz/system/files/documents/pages/covid_cases_2021-08-25.csv

We're going to break this into:

• Root website: https://www.health.govt.nz
• Relative path part 1: /system/files/documents/pages/covid_cases_
• Relative path part 2: 2021-08-25
• Relative path part 3: .csv

Note the Relative path part 2 matches today's date in the format: yyyy-MM-dd

Step 2: Generate Dynamic Relative path part

I can't use a normal parameter for the Relative path part 2 because I need it to update automatically on refresh. So, I'm going to create a new blank query instead. This Query is really simple, just has one step that combines a few M code date functions: 

let

    Source = Date.ToText(Date.From(DateTime.FixedLocalNow()), "yyyy-MM-dd")

in

    Source

I have named this query: DateCSV

Step 3: Use relative path to get Dynamic Data

Normally we'd be able to split the URL into parts, but with a dynamic data source that will give an error.

INCORRECT Code

let

    Source = Web.Contents("https://www.health.govt.nz/system/files/documents/pages/covid_cases_"&DateCSV&".csv")

in

    Source

Unfortunately, if you use the above Source code and Publish to PowerBI.com, you'll get the following error explaining that dynamic data sources cannot be refreshed in the Power BI service: 

This error is not very helpful - it doesn't even specify the correct name of the query, instead just referring to 'Query1'.

When you follow the link to Learn more from the error message, you will find a short message that doesn't quite provide you with enough information to resolve the data error: 

In most cases, Power BI datasets that use dynamic data sources cannot be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

Picking up on the keywords, a quick web search for "RelativePath and Query options with the Web.Contents M function" returns Chris Webb's super helpful blog.CrossJoin post Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code, to which I have referred many times.

Finally, we're getting somewhere and you may finally be able to see why I split the URL into a Root path and four Relative paths in Step 1.

CORRECT Code

let

    Source = Web.Contents("https://www.health.govt.nz", [RelativePath = "/system/files/documents/pages/covid_cases_"&DateCSV&".csv"])

in

    Source

This correct code uses a static URL for the Web.Contents() function. You'll find this process easier if the root URL is an actual functioning website, which is why my Relative Path part 1 is still static.

Now we can refer to the dynamic DateCSV query within the RelativePath portion of the Web.Contents() function and overcome the dynamic data refresh limitations in Power BI service.

Voila! Now we can schedule a refresh on this dataset in Power BI service.

Real Time Data Updates: COVID-19 in New Zealand

COVID-19 has occupied the headlines, our minds, and our daily lives for quite some time now. There are lots of statistics and reports available on the web, many of them created using Power BI.

What I wanted to know, was not how many total cases of COVID-19 each region has, but how many cases as a percent of total population. In my opinion, this is a far more useful measure of comparison between regions.

To start this off, I have used my home country of New Zealand and some simple DAX measures to calculate Cases Per Capita by region, gender and age.

Cases Per Capita =
DIVIDE (
    [Total Cases],
    CALCULATE (
        [Total Population],
        ALL ()
    )
)

You can interact with the embedded report below (see page 2 for the per capita data). Even better yet, have a go at making your own report using data from the web and leave a comment below to let me know how you get on. Subscribe to this blog to get updates - I'm working on a post detailing how to use parameters to combine data from similar sources.

Power BI makes it easy to combine two or more data sources (such as population and COVID-19 statistics) into one cohesive data model that can be automatically refreshed as much as 8 times a day.