Power BI New Format Pane

 I'm so excited to finally be able to share the new Format Pane with you. As part of the November 2021 Feature Update, Power BI format pane has got a new face lift.

Format Pane

Formatting visuals can take a lot of time and for new Power BI users it's a bit overwhelming and confusing. As of the November release, we have the opportunity to get familiar with a new format pane, one that will soon become the new normal. 

Enable the Preview Feature

Since this is in preview, you'll need to turn it on. 

Click File > Options and Settings > Options

Then Select 'Preview Features' from the 'Global' menu on left hand side.

Tick the box next to 'New format pane'.

Click OK.

Save and close Power BI desktop.

Re open Power BI desktop and welcome to the new format pane.

Have your Say

The really exciting thing about this feature is that it is still in Preview mode - which means that you still have time to provide feedback and have your say. Check out the Official Microsoft blog: Introducing the New Format Pane and add your comments to the blog to provide feedback on what you like, what's missing, what you don't like. 

What's new?

There are lots of changes to get used to with the new format pane, luckily we can turn it on and off as needed, but it's definitely an improvement.

Two pivots

I'm so excited about this feature - I actually requested this in MVP meetings and now it's here! You'll notice the Format pane is now split into two topics: 

• Visual - formatting options specific to this visualization
• General - formatting options available for all (or most) visualizations

Microsoft call these 'pivots'. 

General Pivot

The 'General' pivot has most of the formatting options from the 'General' section of the old formatting pane, but also has lots of other familiar Categories too. The key to this pivot it that it only contains formatting options that are available in bulk across all Power BI visualizations:

• Properties - here you'll find all the size and positioning stuff that was in the old General section
• Title - every visual has a title, so makes sense to see this under the General pivot
• Effects - this is where things like background, border, shadow have moved
• Header icons - this is the new 'Visual header' section
• Tooltips - almost every visual has tooltips, so again logical to find this under General pivot. There is some additional formatting options in here too!
• Alt text - Accessibility is important for all visuals. 

Visual Pivot

The 'Visual' pivot has formatting options specific to the visualization type. I'm going to use the slicer visualization as an example. The old format pane for slicers was one of the least intuitive and most challenging to navigate.

Looking at the new format pane for slicers we have three categories:

• Slicer settings - All the fun stuff has moved here, including the Orientation to turn your list into buttons and the search option that was previously hidden away in the three dots. LOVE this!
• Slicer header
• Values

Right Click

You can now right click on the cards for more options: 

I'd like to see this made a bit more obvious, but it's nice that it doesn't take up extra space and detract from the heading of the card itself - we can finally actually read the entire heading description!

Hover

You can also hover over any of the formatting options to get a quick description of what they do. Continuing with the non-intuitive slicer visual, 'Multi-select with CTRL' scares a lot of new users as it sounds like turning it off might turn off multi-select. When you hover over this in the new format pane, it tries to explain what this actually means, though I think it could still use a bit more clarification. How would you re-word it? "CTRL key required for multi-select" makes a tiny bit more sense to me.

Conclusion

The Power BI new format pane is still in preview mode, so you need to enable it and be patient with updates and bug fixes, but it's worth turning it on early to get in on the feedback stage and take advantage of the upgraded functionality and ease of formatting visualizations.

Custom Sort Order for Text Columns in Power BI

How to sort months properly in Power BI

How do you sort your months chronologically? Or how can we put 'Other' at the end of the list without putting zzzz in front of it?

In this post I'll look at two methods for sorting your data correctly and in any custom sort order of your choosing in Power BI.

Method 1: Use 'Sort by Column' feature to sort Months of Year 

Power BI Desktop (and Excel Data Model) have a feature that enable us to change the sort order of any column. The Sort by Column button works when you have another column in your data model that tells Power BI how to sort your data.

For example, in the table below, we have [Month] and [Month Number] columns. If we leave everything as default, the Month column will sort alphabetically, but by using the Sort by Column feature of Power BI, we can tell it to use the Month Number column to determine the order of the months.

In order for this to work, you must have:

• Two columns: the Display column and the Sort column
• A single value in the Sort column for every value in the Display column
• The Sort column must be independent of the Display column (it cannot use DAX conditions)

Let's look at some examples to see what that means. 

It's okay to have the same value in the Sort column for different values in the Value column. 

Here we have a Value column [Month Year] and we want to Sort by [Month Number]:

This works because every value of Jan-21 has the same value in the [Month Number] column. It's okay to have Month Number 1 for multiple values, they'll just get sorted by default within that range. For example if we sort Month Year by Month Number, and then sort the Month Year column Ascending, the result is: 

Error: There can't be more than one value in 'Sort Column' for the same value in 'Value Column'.

We can't sort the 'Month' column by 'Month Year'. There can't be more than one value in 'Month Year' for the same value in 'Month'. Please choose a different column for sorting or update the data in 'Month Year'.

If you've seen some version of that error message before, it means that you have too many sort order values for the same value in the value column. In this example below, we are trying to sort [Month] by [Month Year], but January has the sort value 202101 and 202201. Where then do we place January in the list? It's ambiguous and cannot be used as a sort by column.

Error: 'A circular dependency was detected:

Failed to save modifications ot the server. Error returned: 'A circular dependency was detected: Table[Display Column], Table[DAX Sort], Table[Display Column].'

If you don't have a Sort column in your data model, you need to add it using Power Query M code, not DAX. Due to the Order of Operations in Power BI, we cannot use DAX as a Sort by Column in Power BI.

To overcome this problem, open Power Query, click New Column > Conditional Column and create your Sort column there. Then follow the steps in the Microsoft Docs link above to use the Sort by Column feature.

Method 2: Use M Code Zero Width Space Characthers to sort Power BI data

Power BI sorting sorts in ascending order with spaces and special characters first, then numbers, followed by text: 

Your report users will notice if you start putting spaces at the beginning of your data, but we can use the Zero Width Space Character to add an invisible space that can't be seen by the naked eye, however Power BI will still see it and sort it before any other values without that space. 

Zero Width Space Characters

In M code, the Zero Width Space Character can be written as: 

Character.FromNumber(8203)

So to use this, we just need to add it as a prefix to any data. This will require some custom M coding, so we're going to use the interface to learn a few things. If you don't have the formula bar turned on, click the View tab in Power Query, tick the box next to 'Formula bar' to turn it on.

Add Prefix to Column

To add a prefix to any column, we just use the & operator in M. In order to see that in action, click the 'Add Column' tab > Format > Add Prefix

Type anything in the Prefix box:

Now check the formula: 

= Table.AddColumn(Source, "Prefix", each "zero" & [Customer], type text)

Note the part that is underlined in the formula above. The prefix you typed in the box is in quotation marks, followed by the & symbol and the name of the column you had selected. If you look at your data preview, the prefix appears in front of the text in the column you had selected.

We can replace the "zero" with Character.FromNumber(8203) so that our formula now reads: 

= Table.AddColumn(Source, "Prefix", each Character.FromNumber(8203) & [Customer], type text)

If you look at your data preview now, the new column should look the same as the original column, only there's an invisible space character there that we can't see.

This is cool, but doesn't help us with sorting when every value has this character.

Add Conditional Column

Now we're going to add a Conditional column, this will help give us the base structure we'll use to add our prefix. We want our VIP customers to appear first in the list, followed by our Standard, then Other. So to do this, we'll put two zero width space characters in front of the VIP customers, one zero width space character in front of the Standard, and none in front of the Other customers.

We're still using the built in interface to learn M code, so click the Add Column tab > Conditional Column. 

In this area we'll just create the conditions and use the same 'Output' for each condition. This will give us the base formula, which we can then change to: 

= Table.AddColumn(Source, "Customer Sort", each if [Category] = "VIP" then Character.FromNumber(8203) & Character.FromNumber(8203) & [Customer] else if [Category] = "Standard" then Character.FromNumber(8203) & [Customer] else [Customer])

Now you'll see if we sort by this new column, or use this new column in visuals, it will automatically sort the VIP customers first, then Standard, then Other: 

Top 3 Reasons to Use Tabular Editor

Tabular Editor may seem a bit scary and technical - it is. However, it can also save you a LOT of time, and teach you some good best practice tips too. Are you using it? What for?

Create a single value slicer (parameter)

Use Tabular Editor to create a single value slicer. We can do this with the 'New Parameter' button in Power BI Desktop, but how can we convert an existing column into a single value slicer? Here's a handy article from SQLBI that shows you How to Enable the Single Value option in a Power BI Slicer.  

Optimize Data Model - Best Practice Tips

In January I wrote a brief Intro to Tabular Editor post on how to install and start using Tabular Editor and focused on the Optimize Data Model feature, which includes some best practice tips for managing your data model.

Calculation Groups

This is fast becoming the new buzz word, and if you're not using them, you may feel left out. I myself am still using the long way and creating all my time intelligence measures one by one - I like the control and flexibility I get when I author the measures myself. However, I'm going to start working through the SQLBI Calculation Groups Blog Series and will try to summarize my learnings each week as I go.

Summarization

One thing to be aware of when using Calculation Groups is that enabling this will disable all default summarizations of your columns. This means you MUST create Explicit Measures (no more using Implicit Measures). You should really be using Explicit Measures anyway, so great way to force best practice behavior, but can be a bit inconvenient when you're trying to do data quality checks or still getting familiar with your data model. 

Reply to Teams Message

Woohoo! It's finally made it to Teams Desktop. Thanks Microsoft for the updates - keep 'em coming!

Reply to Teams Chat

As of this month, we can now reply directly to a message in a Teams chat on mobile AND desktop: 

This functionality has been available on the Mobile Teams app for quite a while, but only just made its way to desktop. If you don't regularly check the 'What's New' section of Teams, add it to your list of things to do while procrastinating. Just click the 'Help' icon at the bottom left of Teams > What's New.

Reply to Teams Channel

Unfortunately this functionality is not available in Teams Channel conversations. You can reply to the entire thread, but not to an individual comment. In order to reference a specific comment or message in the thread, I can still recommend using "Windows Shift S" to take a screenshot, followed by a "Ctrl V" to paste it into the reply window. 

Screenshot / Snipping Tool Windows Shortcut

These screenshots are brought to you by the letter S.

This is probably my most used keyboard shortcut of all time. Just hold down the Windows key and Shift key at the same time, then tap the 'S' key (S is for Snipping or Screenshot):

Windows Shift S

Even if you take screenshots 10% as often as I do, you'll love the ease and flexibility of this tool. It enables you to copy a specific area of your screen straight to the clipboard, or edit it first and then copy to clipboard. Then you can paste it anywhere you want to send. Try it out now and see what you think. Does it do everything your current snipping tool does? 

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.

Advanced Filter OR vs AND

This post was inspired by @saud968 from the Power BI Community and their post on Multiple values from same column under same visual

Their question centered around being able to find all the survey results that were equal to 4 AND all the survey results that were equal to 5. While the wording I have used here, specifically the word AND, is commonly how we approach and think of the problem, it will unfortunately trip you up when working with advanced filter logic. 

I am going to keep this post generic, as this concept can apply to DAX Logical Operators (specifically && and ||), Excel Advanced Filter conditions, Power Query Advanced Filters with And / Or selection and more!

The Problem

Let's use the below table as our sample data. Nice and simple, only 5 rows of ice cream orders:

Order ID	Name	Location	Country	Flavor	Scoops
1	Allison	Chicago	USA	Chocolate	2
2	Phil	Wellington	NZ	Vanilla	2
3	Allison	Auckland	NZ	Chocolate	1
4	Vijay	New York	USA	Mint Chocolate Chip	1
5	Agnes	San Francisco	USA	Vanilla	1

The boss wants to know the total number of scoops for Chocolate of any kind, so Chocolate and Mint Chocolate Chip scoops combined total. How do we write the filter - using AND vs OR?

Let's start simpler:

If we want all Orders for vanilla that's easy: Order IDs 2 and 5. 

If we want all Orders for 1 scoop that's easy: Order IDs 3, 4 and 5. 

But what if we want Orders for vanilla AND 1 scoop?

What about Orders for vanilla OR 1 scoop?

AND: 1 scoop AND Vanilla

Let's start with the AND condition: 

As you can see in the Venn Diagram above, I have moved Order ID 5 to the middle of the Venn Diagram, since it is both a Vanilla AND a 1 scoop order, so it falls into BOTH filter conditions. 

If we choose an AND filter, we will only get the intersection of the chosen conditions, in this case, Order ID 5 only.

OR: 1 scoop OR Vanilla

But what if we wanted all four of those Orders 2, 3, 4 and 5?

Well, again referring to the Venn Diagram above, in order to get all orders, we need to include ALL orders that fall into Vanilla Orders and ALL orders that fall into 1 Scoop Orders. 

As I have mentioned before, the use of that pesky little word and in the sentence above often gets us into trouble. We have already demonstrated that when using the AND filter, we only get Order ID 5. So to get all four orders we must need to use the OR condition. But why?

Let's rephrase our question to look at EACH order individually, as that is how the filter conditions are evaluated when we use them in the various Power BI, Excel, etc applications: 

Does each Order have either Vanilla OR 1 scoop?

Now we have the key to success. Looking at EACH item individually:

• Order ID 1, does it have either vanilla or 1 scoop? NO
• Order ID 2, does it have either vanilla or 1 scoop? YES, vanilla only
• Order ID 3, does it have either vanilla or 1 scoop? YES, 1 scoop only
• Order ID 4, does it have either vanilla or 1 scoop? YES, 1 scoop only
• Order ID 5, does it have either vanilla or 1 scoop? YES, vanilla AND 1 scoop

So now we can see that by using the OR condition on EACH item, we get the desired result of all four orders that have either 1 scoop or vanilla.

EACH item is evaluated individually

Now that we know that each item is evaluated individually, let's rephrase our questions and revisit the AND filter. 

Does each Order have Vanilla AND 1 scoop?

• Order ID 1, does it have vanilla and 1 scoop? NO
• Order ID 2, does it have vanilla and 1 scoop? NO, vanilla only
• Order ID 3, does it have vanilla and 1 scoop? NO, 1 scoop only
• Order ID 4, does it have vanilla and 1 scoop? NO, 1 scoop only
• Order ID 5, does it have vanilla and 1 scoop? YES, vanilla AND 1 scoop

Find the Chocolate and Mint Chocolate Chip combined total scoops

To determine which filter condition (AND vs OR) to use, we need to think about evaluating EACH item individually. 

If we draw our Venn Diagram, we can see that nothing is in the middle AND condition section: 

Does each Order have Chocolate AND Mint Chocolate Chip?

• Order ID 1, does it have Chocolate and Mint Chocolate Chip? NO, Chocolate only
• Order ID 2, does it have Chocolate and Mint Chocolate Chip? NO
• Order ID 3, does it have Chocolate and Mint Chocolate Chip? NO, Chocolate only
• Order ID 4, does it have Chocolate and Mint Chocolate Chip? NO, Mint Chocolate Chip only
• Order ID 5, does it have Chocolate and Mint Chocolate Chip? NO, 

Does each Order have Chocolate OR Mint Chocolate Chip?

• Order ID 1, does it have Chocolate or Mint Chocolate Chip? YES, Chocolate only
• Order ID 2, does it have Chocolate or Mint Chocolate Chip? NO
• Order ID 3, does it have Chocolate or Mint Chocolate Chip? YES, Chocolate only
• Order ID 4, does it have Chocolate or Mint Chocolate Chip? YES, Mint Chocolate Chip only
• Order ID 5, does it have Chocolate or Mint Chocolate Chip? NO, 

Therefore, in order to get both Chocolate AND Mint Chocolate Chip orders, we actually need to use an OR filter condition. 

Solution

In conclusion, be careful how you word your filter condition questions - don't mislead yourself. In order to pick the correct filter condition (AND vs OR):

• Evaluate EACH item individually
• Make a Venn Diagram
• AND filter condition: Intersection of all conditions (aka the middle of the Venn diagram)
• OR filter condition: Union of all conditions (aka everything within the Venn diagram)

Now we can confidently tell the boss that there are 4 total scoops that were either Chocolate OR Mint Chocolate Chip because we used an OR filter condition to filter our data. 

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.

Tokyo 2020: Power Query Data Source Update and Error Troubleshooting

Lots of swimming, cycling and more happening at the Tokyo 2020 Paralympic Games. Team New Zealand have already won a Gold and Silver in swimming. Still plenty of time to earn some more. USA have earned Athletics, Cycling, Equestrian and Swimming.

I spent a bit of time creating a report for the Tokyo 2020 Olympics, and the Paralympics follows a very similar format with the same type of information and data to report. Therefore, it should be straightforward to update the report from Olympics to Paralympics, right? 

In theory. In practice, this is sometimes more challenging than it should be and we encounter all sorts of errors along the way. 

Well, I succeeded. Here's the Power BI Report with live data for the Paralympics, updated every 30 minutes:

Change Data Source

In order to update my existing report, I need to simply point the report to the Paralympic-Games link instead of the Olympic-Games link. 

Old Data Source: 

https://olympics.com/tokyo-2020/olympic-games/en/results/all-sports/medal-standings.htm

New Data Source:

https://olympics.com/tokyo-2020/paralympic-games/en/results/all-sports/medal-standings.htm

Unfortunately, not all the column headers are identical, so this is fraught with a few minor challenges along the way. 

In the video below, I demonstrate how to change and update your data source quickly and without stress, even when you encounter errors in Power Query.

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.