PowerPivotPro

PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms

MARCH 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

Bookmark Bill

Every month Power BI gets an update that adds a nice handful of new features. Here at P3 we ALL get excited for this! We often stand around the water cooler talking about all the new features. When a REALLY exciting feature comes out we get as gleeful as school children! Well starting this month we want to share our excitement with you. We will showcase our favorite features (typically one to two) with you, with the aim to give you a few more ways to use the new features.

Our aim will be to keep these posts bite sized and easily digestible. We’ll also aim to get these posts out at the beginning of the week, shortly after the monthly Power BI update release. With that being said I want to get into this month’s awesome feature…Bookmarks.

The Legacy of Bookmark Bill

I heard a story once, probably in history class, about some time in the past where the people of the world were stuck on the edge of some great intellectual precipice. Apparently, someone in the village had created paper out of piths from a papyrus plant (back when it was common to know all about plant pith) … and that person’s buddy (possibly relative, cousin maybe?) had stumbled upon ink by smashing a bunch of flower petals or some other thing that causes stains… and then that was pretty much it for a while. People colored. Eventually some nerd got bored with that, probably Voltaire, and wrote a white paper that at least three people read. The topic of that white paper? How civilization could use this new ink and paper invention to make books. Books! That’s basically how books happened.

Crazy right?

But that only get’s us to the edge of the precipice. That’s where they got stuck. These ancient people now had books with a bunch of knowledge in them, VALUABLE KNOWLEDGE! All they had to do was read them and TAKE ACTION to improve the world. But wouldn’t you know, every time they made progress in their reading, they’d get tired and fall asleep. When they woke up they had no idea where they left off from their last reading sesh.

So they’d start over……from the beginning…….and…….would…….never…….finish.

We now refer to this period in history as the dark ages. But there was someone out there who was a real innovator, a guy named Bill. He got sick of this whole reread thing real fast, and in December of 1614 he invented a mark. He put the mark in a book, and started calling it a bookmark. The idea caught on like wildfire and soon a period of great learning and intellectualism took off – today we call it the Age of Enlightenment. So here’s to Bookmark Bill, the patron saint of going back to where you left off before you napped.

“Cool story Ryan, why did you tell me that?” Well, dear reader, its because Power BI has bookmarks now! It’s a preview feature. So, let’s update some Power BI Desktop settings and use this new shiny toy!

And somewhere out there, Bookmark Bill is smiling. Let’s learn 😀

Enabling Preview Features

Open Power BI Desktop and go to where the cool stuff is in any application, up under “File”, “Options and Settings”, “Options”. I’m not sure why they still call it options and settings. It needs to be called, the cool stuff. Rummaging around Options and Settings in an application is always a good idea- it’s like turning the bass all the way up in that Toyota Tercel you had in high school, can’t drive without those sick beats.

Power BI – Bookmarks – Options Menu

Power BI – Bookmarks – Options Menu

Once you’re in Options, click on “Preview features” in the “Global” list, and check that box at the bottom, the one that says “Bookmarks”. Now click OK.

image

Power BI – Bookmarks – Preview Features

Navigate up to the ribbon and click “View”, and there should now be a little box that says, “Bookmarks Pane”. Click it. CLICK IT NOW! Smile While you’re there, click selection pane as well, it’ll come in handy later.

image

Power BI – Bookmarks – View – Bookmarks Pane

Disclaimer: Before we get into our demo its worth saying that bookmarks seem like one of those features where one is only limited by their imagination. The demo we show is pretty straight forward, but don’t’ let it cause you to underestimate the power of bookmarks.

The fun part

Let’s prove that Bookmark Bill really did help usher in the Age of Enlightenment. In order to do this we need some data.  I called up my favorite history/archeology Professor, Henry Walton Jones, to see if he had any data on reading patterns during this tumultuous time in history. Sure enough, he had a nice little sample on that exact subject – reading sessions logged in the 17th century! Now this is a relatively small sample- but it will work nonetheless.

Look at this nice little data model:

Power BI - Bookmarks - Data Model

Power BI – Bookmarks – Data Model

Let’s look at the data within the table. What were people reading in this sample?

Power BI - Bookmarks - Data View

Power BI – Bookmarks – Data View

Ok – cool, pretty standard stuff. What about the data table?

Power BI - Bookmarks - "Clean" Data Model

Power BI – Bookmarks – “Clean” Data Model

Wow. They sure knew how to keep their data models concise back in the day.

Right on! Let’s define some measures for context.

ReadingSessions =
COUNTROWS ( StudySessions )
This will give us an idea of how often people were reading.

AvgStartingPage =
AVERAGE ( StudySessions[StartingPage] )

This will show us if people were making their way through the books prior to Bill’s invention, or if they had to keep starting over- judging by the picture above… I think the case for Bookmark Bill is pretty compelling.

FinishedBooks =
COUNTROWS (
    FILTER ( StudySessions, StudySessions[EndingPage] = RELATED ( Books[Pages] ) )
)

This will show us how many study sessions resulted in a finished book!

First things first: let’s get a line chart with Year on our axis, and our three measures in the values. Simple, and beautiful… and would you look at that? Our AvgStartingPage skyrockets in 1614 – the year Bookmark Bill invented the Bookmark! And look at the books that were finished in 1615 – right when the Age of Enlightenment started. Glad we were able to prove that with data.

image

Power BI – Bookmarks – Line Chart

Wow – the data doesn’t lie! You can clearly see an increase in the average starting page starting in late 1614… around the time Bookmark Bill invented the Bookmark! The [finishedbooks] starts showing growth around the same time too. Fascinating!

But this will never be enough for everyone. You still have the “show me the numbers” people. They don’t want graphs, they want tables / matrixes. So let’s add a matrix in there for them. We’ll place Datekey[Year] on Rows, Books[Title] on Columns, and then our three measures in values. The crazy part? We’re going to put this visual directly on top of our line graph (In the screen shot they are not directly over each other to more easily see what we’re doing).

image

Power BI – Bookmarks – Matrix

Next we need to add in a couple of pictures that we’re going to use as our bookmark buttons:

Power BI - Bookmarks - Buttons

Power BI – Bookmarks – Buttons

With our images added, I’ve clicked “Add” in the bookmarks pane, and created one bookmark called Binary, and one called Chartychart.

Power BI -Bookmarks – Buttons – Matrix

Select one of the image, and then on the right side of the screen, there is a menu option for “Link”. We’re going to turn that on, change the type to “Bookmark” and select which bookmark it corresponds to. Point the Binary image to the Binary bookmark, and do the same for Chartychart.

image

Power BI – Bookmarks – Buttons – Binary and Chartychart – Linked

The final magic lies in the selection pane. We’re going to click the “Binary” bookmark, and then unselect “Matrix” in the selection pane. Right click the Binary bookmark again and click update in the Chartychart bookmark, and unselect the line graph (labeled by our measure names).

There it is. Now you can CTRL+Click (in PBI desktop) or regular click the two images and switch back and forth between the visible visuals!

Its pretty wild to think that all those years ago Bookmark Bill changed the world with his simple idea. And then Microsoft, paying homage to Bookmark Bill, has added a digital version of that feature to their incredible software. Thank you Bookmark Bill – wherever you are.

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Have you done anything cool with Bookmarks? We’d love to hear about what you did in the comments below.

  Subscribe to PowerPivotPro!
X

Subscribe

This Post Has 10 Comments

  1. This is a great new feature. I use it to provide explanation for numbers or concepts – click on a number or word and a pop-up with the definition will show up on the screen. Unfortunately, when I pin the live page to a dashboard, the bookmark feature doesn’t work. Same thing happens with drill through, so my end users won’t be seeing these cool features for now…

  2. Hi Ryan,
    Thank you for the I/E (Informative and Entertaining) post. Is there a link to the data file available so that reader may follow along with the adventures of Bookmark Bill?

  3. Hello Ryan, from a technical standpoint – great article and great example to highlight such a great new feature. From a creative/ teaching standpoint – I absolutely LOVE how you presented the concepts – even more so than the actual content. I do agree with Frank – the images are a little hard to read. Other than that – this is brilliant! Thanks!

  4. Entertaining and Informative, two things I thought I would never say about a Microsoft feature article. Keep it up Ryan and I look forward to your next article!

Leave a Comment or Question