Freediving and Your Lungs: Charticulator Custom Visual for Power BI

This weekend was the Freediving NZ AIDA Depth Nationals in Lake Taupo, New Zealand. Freediving athletes competed to see who could dive the deepest on one breath safely. 

Freediving 101

If you're familiar with freediving you can skip this part, but since it's a bit of a niche sport I thought I'd better give a quick description of what happens at competition. All athletes dive with a depth lanyard which is attached to the competition line. This lanyard has a carabiner at one end, similar to a climbing harness, and ensures that the diver can be easily rescued if something were to go wrong. All athletes must nominate a specific depth that they will attempt to reach on each day of the competition, and must also choose from the following disciplines: 

• Free Immersion (FIM) - Hop in the water with your weight belt. No fins or other propulsion aids are allowed in this discipline, but you do get to pull yourself along the competition line, sparing your leg muscles and giving your arms more of a workout.
• Constant Weight No Fins (CNF) - Like free immersion, no fins or propulsion aids are allowed for this discipline either. You also can't use the competition line to help you at all - all the power to dive down and back up must come from your arms and legs. This tends to require the most energy to make progress and therefore has the shallowest depths. A modified breast stroke will get you farthest in this discipline. 
• Constant Weight BiFins (CWT-Bi) - For this discipline you get to wear a fin on each foot. Long freediving fins are encouraged. Finning or a kick similar to free-style stroke is the most efficient way to make progress in this discipline. 
• Constant Weight (CWT) - If you choose CWT as your discipline you can choose to wear a monofin and have the added benefit of being able to kick like a dolphin. This is generally faster and more efficient than using bifins, so depths tend to be deeper for this discipline. 

The deepest dive dive of the competition this year was 65m CWT. Keep in mind that's a whopping 130m total distance travelled and about 2 minutes without breathing. 

On top of the time and distance challenges, depth adds increased pressure as the diver descends. Deeper Blue are a great resource for freediving facts. Every 10 meters of depth, the pressure increases by 1 bar. This means that your lungs are 25% of their original volume by the time you get to 30 meters (100ft).

Visualizing Lung Volume at Depth - Charticulator Custom Visual

To help visualize the size of a diver's lungs as they dive deeper, I created a custom visual for Power BI using the Charticulator Custom Visual builder for Power BI. 

Charticulator is designed to make custom visuals accessible to non-developers and enables you to create your own Power BI visualizations without any code. You need to get familiar with a few new terms, but I'm pretty pleased with how my first attempt turned out. 

I chose to use Charticulator because I wanted to customize the shapes, labels and locations of the items in the chart. 

Glyph

The shape of the data points is called a 'glyph' and you can use shapes, lines, images or icons for the glyph. I went with simple icons and uploaded them directly from my PC. 

You may be able to see in the image above how the Image of the glyph is a data url, but also has a link icon to the right of it meaning that you could connect this to your data points if you wanted it to be more dynamic.

In the Size property, you can see I have selected the Volume. I didn't need to write that formula myself - it was super easy to select from the Charticulator build interface: 

All I had to set was the maximum size I wanted the icon to be, and Charticulator takes care of the calculations necessary to scale it according to my data points. 

Plot Segment

Every glyph must go inside a plot segment. Charticulator gives you lots of options for formatting these - using (x,y) coordinates, radial, or even custom curves. Don't get confused by the word 'scaffold' - it's just a fancy term for axis when there is no data to apply to it.

When designing the Freedive Effects custom visual, I used two plot segments in my chart. One for the diver, and one for the lungs. 

Guide

Guides are really important in Charticulator custom visuals as they determine where the elements will be placed in Power BI when the visual is resized. I found it challenging to visualize how my visual would look in Power BI without testing it. As I progressed, I started to learn the importance of guides and how they fit into the custom visual development and what impact that has on the visual in Power BI final version.

How to use Charticulator

There are plenty of blogs and videos out there on how to use Charticulator. Just get stuck in and have a play. Here's a good Getting Started blog from Charticulator themselves. 

You can check out the Demos page of my website to find the custom visual download for this Freedive Effects Power BI custom visual if you'd like to modify it or use it for your own reporting.

March 2021 Power BI Desktop Update

The March 2021 update of Power BI Desktop has been released. There are quite a few neat features released this month, I'll highlight just a few of them here. Read all the feature updates in the official Microsoft Power BI blog. 

DAX Updates

DAX users will love this month's update. The CALCULATE function has been updated to be more flexible, now allowing you to filter multiple columns in the same DAX expression. And just when I thought I had mastered DAX! But seriously, this is a handy feature and a good reminder that all CALCULATE functions written with a single column boolean expression, such as:

Red Sales =
CALCULATE (
    [Total Sales],
    Product[Color] = "Red"
)

Are actually evaluated as: 

Red Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Product[Color] ),
        Product[Color] = "Red"
    )
)

Comicgen visual named Editor's Picks Visuals of the Month

I highlighted the Comicgen visual in my 12 Days of Christmas blog series and this month it has made the list of Editor's Picks for Visuals of the Month. Let's see if it gains momentum and brings more insight and storytelling to the reporting world. 

Your Turn

Enjoy the March update and let me know what some of your favorite features are.

Data Story of the Month: Employee Form Completion

This month's data story is courtesy of Sarah (sk15227) from the Power BI Community forum. Power BI makes it really easy to show relationships within our data. We can easily filter for a region and date range to see which employees were in attendance or which products were sold, or how many people filled in the daily paperwork. 

Sarah wanted to know which employees did NOT complete the paperwork each day. More than a count, she wanted to know their names - who does she need to chase up?

Show Records NOT IN results / filter / selection

Sarah's question is a common request I get with Power BI. How can I filter for a specific region/category/segment and show the missing records? This applies to more than just Sarah's employee form example, but her scenario is so simple and yet powerful that I thought it was the perfect story to highlight for my data story of the month. 

The Challenge: Find missing records

We will start with three simple tables in our data model: 

• Employees 
• FormSubmissions
• Calendar

The Employees table has three columns: ID, Name and Station. 

The FormSubmissions table has two columns: EmployeeID, Submission Date. 

The Calendar table is a typical DimDate table and should have any columns you want. As a good practice starting point you will need at least a Date and DateKey columns. 

Without doing any DAX calculations, I can create a simple report to show the:

• number of submissions per Employee (Count of Submission Date)
• number of employee forms submitted per Date (Count of Employee ID)

As you can see in the image above, the North Station has 5 total employees. In the top chart, you can see that we are missing some forms from Alison, Dave and Eric. Alison has only submitted 21 forms while Bob and Charlie each submitted 24. Dave and Eric also appear to be missing a few submissions, but Alison is the worst offender. 

How can we determine which dates these employees are missing? The Submission Date and Count of Employee ID shows that Feb 1, 2, 9, ... have not received submissions from all 5 employees. I could even filter this second table for Count of Employee ID less than 5, but this won't update when I select another station (the South station only has 3 employees). 

Even if the filter were dynamic, it still requires me to know my employees by heart as it only shows the employees who have filled in the form. We want to see the employees who have not filled in the form. Below you can see that Eric is not in the list when I select Feb 1, but I want to see Eric's name listed as 'MissingForm'.

The Solution: DAX 'NOT IN'

To solve this challenge we will use the DAX function NOT() in combination with the IN operator. First, we will create a list of all the Employees who have completed the form within the current slicer and filter selections using: 

VAR formcomplete =
    VALUES ( FormSubmissions[Employee ID] )

Then, we will check that list of Employees who have completed the form against the entire list of ALLSELECTED Employees, again within the current slicer and filter selections, using: 

FILTER (
            ALLSELECTED ( Employees ),
            NOT ( Employees[ID]
                IN formcomplete )
        )

Finally, we will use the CONCATENATEX function to create a simple list of all Employee Names separated by commas. The final DAX function becomes: 

MissingForm =
VAR formcomplete =
    VALUES ( FormSubmissions[Employee ID] )
RETURN
    CONCATENATEX (
        FILTER (
            ALLSELECTED ( Employees ),
            NOT ( Employees[ID]
                IN formcomplete )
        ),
        Employees[Name],
        ", "
    )

And as you can see, this gives us the names of the employees who have not completed the form on any given day and highlights that no one completes the form on Sundays.

March Madness

 The countdown clock for March Madness is getting closer and closer to zero. How will you build your bracket?

Analyzing March Madness Historical Data

I was chatting with Hoosier BI last week about Power BI, data and March Madness. It's taking place entirely in Indiana this year and I got inspired to take an interest. I found some data on Washington Post Sports App and ESPN that I have combined to analyze historical wins, records and upsets across the entire March Madness tournament. 

Just a few notable figures:

• Teams seeded 1, 2 or 3 are significantly more likely to make it to the National Championship game, with no one below seed 8 ever making it to the National Championship. Also notable; although Seed 5 has made it to the National Championship game 3 times, they have never won.
  
  
  
  

  

  
  
  
  
  
• The Final Four will usually have one or two, but not all, of the No 1 seeds. All four number 1 seeds have made the Final Four only once (in 2008) while 2006 and 2011 saw none of the number 1 seeds in the Final Four!
  
  
  
  

  
  

  
  
  
  
• The last upset in the National Championship game was in 2016 when Villanova beat North Carolina 77-74. There have only been 7 upsets in the 35 National Championship games since 1985.
  
  
  
  


• UConn has made it to the the National Championship 4 times and won every time (tied for second most wins with North Carolina).

• There's a 54% chance that at least 1 number 10 seed will make the Sweet 16, but only a 9% chance that 2 or more number 10 seeds will make Sweet 16.

Below is a preview of the report. Click the icon on bottom right to view it in full screen. Click around and see what insights and analytics you can discover to help you make the best March Madness bracket. More info on how it was built coming soon in future blog posts.

MS Ignite Updates

Microsoft Ignite Announcements

Microsoft Ignite is on now and there are so many fabulous sessions to choose from. I'm co-hosting a Power BI table talk in a few hours and enjoying all the other presentations in the meantime. 

Check out the Ignite Book of News for the latest announcements. 

Teams

Teams Presenter controls and flexibility are improving, giving you options for how to view video and attendees, and making it possible to see the presenter view of PowerPoint while sharing on Teams - all with just one screen! Yay!

As someone who does a lot of webinars, I'm really keen to see how the attendee registration will work - this could make a few of my flows in Power Automate redundant!

Cool Outlook Updates

I'm so excited that Outlook is getting a face lift. I feel like Outlook has been neglected in feature updates over the past many years. Why fix what isn't broken? Well, modern work is changing and we need to be more dynamic, flexible and agile in our work approach. 

The new Outlook board view experience will make it easier for you to keep exactly what you need at your fingertips and customize your Outlook Productivity Platform to meet your needs. 

Power Platform

Power Fx

The Power Automate language, Power Fx, is being rolled out across the Power Platform. You may have heard this previously called 'Excel Speak' because many of the functions are based on or borrowed from Excel. It is designed as a low code coding language and making that transition from Excel to Power Fx is easy if you start with the basic, similar functions. 

I am so excited to have some consistency across the Power Platform and can't wait to see how Power Fx evolves now that it's open source.

Power BI Premium Per User

Woohoo! It's finally here - a Power BI Premium that's accessible to small companies. 

Power BI Premium Per User is really well priced at only $20 USD per month per user and comes packed with all the greatest features that you didn't have with pro, including:

• Paginated reports
• Improved email subscription functionality
• Machine learning and AI integration

Register for MS Ignite 2021

It's not too late to register for MS Ignite - many of the sessions are available on demand and there's lots of good networking opportunities too. See you there!