Excel with Allison

and

Bookmarks

When I say the word 'bookmark' what do you think of? I must say, I've always had an affinity for bookmarks, but not always Power BI bookmarks. When I was in primary school, a bookmark was a witty or artsy piece of cardboard that I collected more for the love of their design than for their functionality (I plowed through books so quickly I often read them in one sitting). My great grandma knew this and taught me how to crochet bookworms, which she used as gifts for her 4th grade students (who I'm sure also loved them more for their design than their function).

As I grew older, the books I read got longer and bookmarks became more functional. Design wasn't as important and I used anything and everything to mark my page: paperclips, hair-ties, the due date card from the front of the library book, or a piece of yarn leftover from my knitting.

In college, bookmarks were a necessity, helping me to keep organized and find important pieces of text, data or references for final reports and exams. I even used bookmarks on a computer to find my favorite sites on the internet.

Now I sit here writing a blog on bookmarks in Power BI and wondering about the etymology of the word. When did bookmarks move to computers? What book am I marking in Power BI?

I must say, it is very intuitive to use a good old fashioned bookmark - just slide it in a book on the page you want to be able to return to quickly. To return to that page, simply find the bookmark. If you have lots of pages you want to find, you can get fancy with some colored sticky note tabs, but that's really where the functionality ends.

Power BI Bookmarks

Power BI bookmarks may be slightly less intuitive, but they are so much more powerful. Not only can you use bookmarks to return quickly to a page in your report, you can:

save your current filter and slicer settings by enabling the 'Data' bookmark property
hide or display buttons, visuals and tooltips by enabling the 'Display' bookmark property
jump to a specific page by enabling the 'Current page' bookmark property

And unlike paper bookmarks, you can put them in any order you want and use them to retell a story in the form of a click through presentation.

Save Filters

Power BI Service has a "Reset to default" option that enables you to restore all filter and slicer settings to how they were saved by the report owner. This means we don't need to create this bookmark ourselves, just teach people how to use the functionality.

However, the "Reset to default" option only resets the filters and slicers to how the report owner designed - it doesn't return you to the original page that the report was saved to start on, and it doesn't let you save filter settings that you have created. In some cases that's exactly what we want, but in our SantaList example, we want to make it really easy for people to return to the Home page and select another name. I want the user to click one button and do two actions:

Navigate to the Home page.
Reset filters so no name is selected.

To do this, we will need to use both the 'Data' and 'Current page' properties of the bookmark. The 'Display' property is optional here - the Display property remembers if visuals are hidden, spotlighted, etc. We haven't done any hiding of visuals in this report so it won't matter if we have this property enabled or not.

Lucky for us, the default bookmark has all properties enabled, so we don't need to do a lot of work to make this work.

How to:

Open the SantaList.pbix file.
Click the View tab in the ribbon and click the 'Bookmarks' button to turn on the bookmarks pane.
Navigate to the 'Home' page and clear all filters.
Click 'Add' in the Bookmarks pane.
Rename this bookmark to 'Reset Home'.
Navigate to the 'Check the List' page.
Select the 'Return to Santa's Workshop' button you created in Day 7.

Change the Action to 'Bookmark'
Select the 'Reset Home' bookmark

Save and test the report.

There are so many cool things you can do with bookmarks, this is just the beginning. I highly recommend you explore and see what you can do, and comment with any questions or struggles you encounter.

Thanks for celebrating the 12 Days of Christmas with me. If you missed any of the days, check them out in the links above. I hope you've enjoyed the posts, learned something new and are inspired to build stunning data stories in 2021.

Eleventh Day of Christmas: Enlighten Data Story

- January 04, 2021

🎵 On the eleventh day of Christmas, my true love gave to me...

Data storytelling,

Data driven emotions,

Meaningful Switches,

and

Custom Visual Review: Enlighten Data Story

Scores

Data Clarity: 7/10

Versatility: 8/10

Fun Factor: 3/10

PBI Certified: No

Description

The Enlighten Data Story custom visual allows you to add key facts and figures directly to a text narrative. It's so simple that it is truly effective and provides endless opportunities for data storytelling.

While you could achieve a similar effect with a custom DAX measure concatenation and a card visual, the Enlighten Data Story highlights the data figures to help them stand out from the rest of the narrative.

Formatting Options

The Enlighten Data Story has the typical formatting options for General, Title, Bacground, Lock aspect ration, Border, Shadow and Visual header. There is nothing extra or different in these categories.

The two additional formatting categories that make the Englighten Data Story stand out are the 'Story' and 'Data' categories.

Story

This allows you to format the static story element of your narrative. You can set the Font colour (yes it's a Kiwi-made visual so colour is spelled with a 'u' in it) and Font size.

In the Text section, type the narrative you wish to display. A # acts as a placeholder (though this can be changed in the Data formatting category) and will return the value of the data fields you have added to the Data values section of the Fields for this visual. For example, type:

Total sales are # for the period #.

Then add [Total Sales] measure and [Current Period] measure to the Data values of the visual. You must add them in the order you want them to appear in your narrative. If you have three fields but only two #, only the first two fields will display in your data story. If you have more # than fields, the extra # will show up in your data story.

Align allows you to left, center or right align the entire narrative. Scrolling enables is 'On' by default, in case your narrative gets too long to fit in the space you have allocated.

Pro tip: Try adding to your story to add a line break (this one may be easy to copy and paste in as it tries to reformat the HTML code).

Total sales are # for the # period.

Data

This allows you to customize the Data values that you add to the narrative. The first thing you can do is edit the placeholder, handy if you need to display # as part of your data story. In this case, simply pick a symbol that you know you won't need in your data story.

You can set the Font colour and Font size for the data values, as well as set Bold to 'On' or toggle it off. This enables you to make your data figures stand out from the narrative in your data story, as they can take on a different size and colour to the rest of your text.

You can also change the native formatting of the fields. For example you may prefer to write 1,300,000 as 1.3M instead.

The 'Blank label' box is my favorite feature. We're still waiting for this feature in native Power BI visuals, but Enlighten have given us the option to replace a Blank() result with any value we desire in the Englighten Data Story. You might put 'NA' or 'Unknown' or 'Not available' or in some cases even '0' in this box to make your data story easier to read. You can see an example of this in the Enlighten Data Story Formatting image above.

'Show tooltips' is set to 'On' by default, but if you are using mostly measures, you may prefer to turn this off. The Enlighten Data Story doesn't have a separate 'tooltip' field area, so there's not much added information provided in a tooltip for a measure that you can't already see in the data story.

Report Example

We used the Enlighten Data Story visual to help compose Santa's letters letting each person know if they're on the naughty or nice list. Recall we created a measure using SWITCH to help display what Santa would say:

Letter =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Looks like you have been nice in 2020. Keep up the good work!",
    "Naughty",
        "Looks like you have been naughty in 2020. Best change your ways before Christmas.",
    "Pick a name."
)

Now we can use that in our Enlighten Data Story.

How to:

Open the SantaList.pbix file we've been working with.
Select the 'Check the List' page - it should have the Comicgen visual there and not much else.
Click the three dots in the Visualizations pane > Get more visuals and then search for and add the Enlighten Data Story.
Add the Enlighten Data Story visual to an empty space on your report page.
Add the SantaList[Name] column and [Letter] measure to the Data values for the visual.
Click the paint roller to format the visual.
Expand the Story section and type: Dear #, # Yours truly, Santa
Test your buttons, slicers and report functionality. It should be pretty functional by now.
Add a background photo or splash of color to your pages if desired.
Save the file.

Tune in again tomorrow for the final gift in the 12 Days of Christmas series where we'll refine the user experience of returning to the home page and look at how to use bookmarks to reset the report filters so that you don't have to clear the name from the last search before selecting a new name.

Tenth Day of Christmas: Comicgen Custom Visual

- January 03, 2021

🎵 On the tenth day of Christmas, my true love gave to me...

Data driven emotions,

Meaningful Switches,

and

Custom Visual Review: Comicgen

Scores

Data Clarity: 4/10

Versatility: 7/10

Fun Factor: 10/10

PBI Certified: No

Description

The Comicgen custom visual is a great way to bring emotion to your data. It uses cartoon drawings to display different emotions and poses based on data.

I must admit, I would never have sought out this visual in a business scenario, but maybe it has a place. Humans are more likely to remember emotive material than neutral material (Hamann, 2001). Emotive material is something we can connect with, usually through remembered experiences. Words such as laughter, joyful and success are more memorable than the neutral words of month, sales, and invest. So, if you want people to connect with your data and remember your report, add more emotion to it.

Sure, this leaves more open to interpretation, so you definitely want to back up this visual with some hard facts and figures, but maybe there's a reason emojis are so popular and perhaps there is a place for them in business reporting. I'd love to hear your thoughts.

Overall, it's a fun visual and emotions are applicable to almost any dataset so it is fairly versatile (that is if we can learn to accept comics in our business reports).

Formatting Options

Like most Power BI visuals, Comicgen has the following formatting options:

General
Title
Background
Lock Aspect Ratio
Border
Shadow
Visual Header

There is nothing special in any of these options, just the typical settings that all visuals have. However, Comicgen has one additional formatting option called 'Comic Settings' that makes the visual work.

Comic Settings

This is where all the magic happens. Comic settings lets you adjust the character, pose and emotion of your visual. It's possible to pick a static pose and emotion, or tell the visual to update based on your data.

Comic

At the time of writing this post, the Comicgen visual has 7 comic options available: Ava, Bean, Evan, Dee, Dey, Priya and Ringo. These must be preselected, and cannot update with a change in your data.

Pose

The Comicgen visual has many poses to choose from: Ponting Right (pointingright), Ponting Up (pointingup), Yuhoo (yuhoo), Super Perfect (superperfect), Holding Laptop (holdinglaptop), Angry Frustrated (angryfrustrated), Hands Folded (handsfolded), Hands on Hip (handsonhip), Holding Book (holdingbook), Reading Paper (readingpaper), Thumbs Up (thumbsup), Thinking hmm (thinkinghmm).

In addition to all those options above, there is an option for 'Data Driven' emotion. In order to use the 'Data Driven' option, you must have either a measure or column which returns exactly the value in (brackets) in the list above. Basically, you have to spell the poses exactly as they are written in the visual, but without the spaces. For some reason, they fixed the spelling mistake on 'pointing' for the data driven, but not in the format pane when you select it manually. ;-)

Emotion

The Comicgen visual currently has seven emotions: Normal, Laugh, Sad, Angry, Worried, Surprised, Wink.

Emotions can also be 'Data Driven'. So far, all emotions are single words so you don't need to worry about removing spaces. Make sure you write the data exactly as in the list, and your visual will work.

Mirror

This is such a simple option, but so important. You may have been distraught to discover that Dey can only point right and not left, well don't worry, just change the Mirror setting from 'Regular' to 'Mirror' and he'll point left.

Only Face

This is another handy option, and I used it to place my comic inside the donut hole of my donut chart. The default is 'False', which means you can see the body and poses of your comic, but you do have the option to set this to 'True'. Unfortunately, there's a lot of white empty space when this option is set to 'True', so the responsive sizing of this visual could use some improvement, but with some clever layering it can work.

Report Example

Alright, so how do we put this Comicgen visual to use in our SantaList.pbix file? Most datasets will not contain perfect pose and emotion words created for this visual, so we'll use the DAX SWITCH function that we looked at yesterday to convert our data into something this visual can understand. Recall we wrote a measure for pose and a measure for emotion:

Pose =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Yuhoo",
    "Naughty", "AngryFrustrated",
    "HandsFolded"
)

Emotion =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Laugh",
    "Naughty", "worried",
    "normal"
)

Today, we will use those two measures in our Comicgen visual.

How To:

Open the SantaList.pbix file.
In the Visualizations pane, click the three dots, then select Get more visuals.
Search for 'Comic' and add the Comicgen visual to your report.
Select the 'Check the List' page of your report. It's still looking pretty empty.
Add the Comicgen visual to this page.
Put the [Emotion] measure in the Emotion Data box.
Put the [Pose] measure in the Pose Data box.
Click the paint roller and expand the Comic Settings section.
Select any Comic you like.
Select 'Data Driven' for Pose.
Select 'Data Driven' for Emotion.
Save and test your file. It should show a different pose if you select a name on the naughty list.

It's starting to come together! Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at another custom visual, the Enlighten Data Story, and see how this can help us compose Santa's letter.

Ninth Day of Christmas: DAX function SWITCH

- January 02, 2021

🎵 On the ninth day of Christmas, my true love gave to me...

Meaningful Switches,

and

Conditional Statements: DAX IF() Function

The IF function is really powerful, and since it's also an Excel function many of us already know how to use it. In fact, we've used it before in this 12 Days of Christmas series. Let's do a quick review of how the IF function works. It takes three arguments:

Logical test (required)
Value if true (required)
Value if false (optional)

We can see that IF allows us to ask a question (aka logical test) that has exactly two responses: True or False. This worked well when we were checking if the SantaList[Name] column was filtered to exactly one value. HASONEVALUE function returns only two choices - True or False and that works perfectly with the IF function.

It can work in other cases where only two choices exist, for example Santa's List only has two options:

Naughty
Nice

That means we can write a measure that returns a number based on the status of the name (we can use this for conditional formatting if it's a number):

Icon IF =
IF (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ) = "Nice",
   1,
    0
)

Then we can apply conditional formatting to our visualizations. Like I've said before, conditional formatting has permeated many areas of Power BI, so you'll get good at it before too long.

However, on second thought, we actually have three options:

Naughty
Nice
No selection

A single IF statement cannot handle three cases, but SWITCH can. (Yes, I know nested IF statements can too, but they're messy.)

Conditional DAX Function: SWITCH

The SWITCH function allows us to provide as many options (aka cases) as we want. The SWITCH function takes at least three arguments:

Expression (required)
Value1 (required)
Result1 (required)
Value2 (optional)
Result2 (optional)
ValueN (optional)
ResultN (optional)
ResultElse (optional)

There are lots of Value and Result arguments floating around, so this can be a confusing function the first time you see it. Let's see what our Icon measure looks like with SWITCH:

Icon =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", 1,
    "Naughty", 0,
-1
)

Note I have used the Shift key and Enter key on the keyboard to put Result1 ("Nice") and Value1 (1) on their own line. This makes it easy to see that -1 is the ResultElse. If the selected value in the Naughty or Nice column is not Nice or Naughty, then this measure will return -1. If the ResultElse is left blank, then the measure will return BLANK().

Now that we know how SWITCH works, we can write a few functions to add more meaning to our report.

How would you feel if you were on the naughty list?

Emotion =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Laugh",
    "Naughty", "worried",
    "normal"
)

What would Santa say to someone on the Nice list?

What pose would you make if you found out you were on the Nice list?

Pose =
SWITCH (
    SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ),
    "Nice", "Yuhoo",
    "Naughty", "AngryFrustrated",
    "HandsFolded"
)

How to:

Open the SantaList.pbix file.
Create new measures as above for:

Icon
Emotion
Letter
Pose

Create a Card visual on the 'Check the List' page using the [Letter] measure.
Select the table visual from the 'Summary' page.
It should have SantaList[Name] column in values.
Click the down arrow next to the [Name] column in the values > Conditional Formatting > Icons
Use the [Icon] measure we created above to apply conditional formatting.
Pick appropriate icons.
Click OK.
Save the report.

Cool! You've just becoming a coding guru. SWITCH functions are a handy tool to have in your toolkit, so keep practicing them. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at the Comicgen custom visual and see how those poses and emotions will come into play.

Eighth Day of Christmas: Sync Slicers

- January 01, 2021

🎵 On the eighth day of Christmas, my true love gave to me...

Syncing Slicers Slicing,

and