Seventh Day of Christmas: Conditional Drill-through Button

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

Conditional Drill-through Button,

Verified User Selections, 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Drill-through

Some of you may love Power BI's drill-through functionality as much as I do. If you don't know what I'm talking about, drill-through enables users to right click on a data point and drill-through to a different page that automatically filters to show information only for that data point (usually at a more detailed level). It's a pretty cool feature, but still not widely known and even with helpful tooltips prompting users to "right click to drillthrough" it's not all that intuitive. What does "right click to drillthrough" actually mean? What value will it add? When do I need it?

Today's post is looking at a way to mimic this functionality in a way that guides the end user through the process. In order to make it work we will need to use: 

• DAX conditional measures (see yesterday's post on Verified User Selections if you missed the intro)
• Page Navigation Button
• Sync Slicers (we'll detail this in tomorrow's post)

In our SantaList report, we want users to be able to search for their own name and then check if they are on the naughty or nice list. Drill-through requires the user to right click a data point (such as on a pie, bar or line chart). It is already challenging enough to prompt the user to select one name from the slicer, we want them to just click a button and see their results.

Buttons

Today we will focus on the button part of this drill-through workaround. Power BI buttons are really powerful; their built-in functionality is amazing. In May 2020, Microsoft released new built in drill-through action for buttons that really helps go a long way in solving the problem of drill-through being too hard to discover. In our example though, it would require an extra click from our end users because drill-through is not activated by a slicer - they must select a data point (bar, pie, line, etc) in order to activate the button. This is just too much work! 

Lucky for us, Microsoft also introduced Conditional Page Navigation functionality for buttons in the same month. This is where the magic happens. It gives you the power to dream up any scenario and make it work in Power BI.

Conditional (fx) Page Navigation

Conditional formatting has worked its way into many areas of Power BI. It's not just used to apply pretty background colors to table cells anymore. In the Fifth Day of Christmas, we saw how to use SELECTEDVALUE in a measure to apply conditional formatting to our visualization titles. Well, we're going to use exactly the same approach here. Conditional formatting and DAX go hand in hand. 

Name the pages

Our report has three pages: 

• Home
• Check the List 
• Summary

We want users to navigate from the Home page to the other two pages. The trick: we want them to navigate to Summary page and 'See Santa's Entire List' if they have not selected exactly one name from the list. If they have selected exactly one name from the list, we want them to navigate to Check the List page and 'Check if <selectedname> has been naughty or nice'. 

Yesterday we created the [Button Text] measure that tells the user what will happen when they click the button. Today, we will create a [Page Navigation] measure that will enable the Conditional Page Navigation functionality. 

Create the DAX Measure

All we need to do to make this work, is create a measure that will return the name of the page exactly as it is written in our report. After yesterday's lesson on HASONEVALUE, this is easy: 

Page Navigation =
IF ( HASONEVALUE ( 'Santa List'[Name] ), "Check the List", "Summary" )

Note, the key here is that we have written the name of the pages in our report EXACTLY as they are written. 

Format the Button

Now we're ready to format the button. We will insert a blank button on the Home page. 

Expand the 'Action' section of the button. Set it to 'On' and 'Page navigation'. You'll notice here that you have the ability to select any of the pages in your report. That's already pretty cool, but we want this to be user directed, so click the fx next to Destination so we can add in the conditional formatting. Format by 'Field value' Based on field 'Page Navigation' and click OK. 

That's it! Now your button will take you to a different page depending on the value of that [Page Navigation] measure. To see how it works, expand the "Button text" section of the button and click the fx to conditionally format the button text using the same [Page Navigation] measure. 

The [Page Navigation] measure checks if the SantaList[Name] column has exactly one value, so we need to also insert a slicer for SantaList[Name]. 

Viola! Test out your button - make sure it takes you to the correct pages. If you get an error, check your spelling! Remember, you have to hold the Ctrl key to test buttons in Desktop (this isn't necessary in web service).

Finishing Touches

Okay, now explore some of the other formatting options for buttons - give them a background color. Center the text. Change the padding. 

Hide the 'Summary' and 'Check the List' pages - we don't want users finding them prematurely or if they haven't chosen the right slicer settings.

Pro tip: Hidden pages can still be accessed by report viewers, especially when printing to PDF. Do NOT use the hidden page functionality to hide incomplete work or messy testing ground pages. Do NOT use the hidden page functionality to hide sensitive or confidential data. Do use the hidden page functionality to make your report easier to read, use and navigate. 

Make it easy to get back to the Home page by adding a button to both the Summary and Check the List pages that say 'Return to Santa's Workshop' or something equally appropriate. These are easier - no conditional page navigation required. Just select 'Home' from the page navigation option. 

In the Twelfth Day of Christmas post we will step things up a level and use bookmarks to make this experience even better. 

How to: 

1. Open the SantaList.pbix file we have been working with.
2. You should have two pages: Page1 and Summary. 
3. Add a new page and call it "Check the List".
4. Rename Page1 to "Home".
5. Create the [Page Navigation] measure from above.
6. Click on the Home page.
7. Insert a Slicer visual for SantaList[Name] column.
8. On the Insert tab, click Buttons > Blank.
9. Expand Button Text in the Visualizations pane.
1. Turn it to 'On'
2. Click the fx next to the text box.
3. Select 'Field value' under Format by.
4. Select the [Button Text] measure we created yesterday under Based on Field.
5. Click OK.
10. Scroll down to 'Action' and expand this section in the Visualizations pane (the button should still be selected).
1. Select 'Page Navigation' for the action.
2. Click the fx next to the text box for 'Destination'.
3. Select 'Field value' under Format by.
4. Select the [Page Navigation] measure we created above under Based on Field.
5. Click OK.
11. Resize the Button so that the text fits.
12. Test the button - it should take you to the 'Check the List' page when 1 name is selected and to the 'Summary' page when more than one name is selected. 
13. Add a Button to the 'Summary' page:
1. Set Button Text to 'On' and type "Return to Santa's Workshop"
2. Set Action to 'Page Navigation' and Destination to 'Home'
14. Copy this button and paste on the 'Check the List' page.
15. Right click on 'Check the List' page and Hide.
16. Right click on 'Summary' page and Hide.
17. Save the file.

That's plenty for today. I know it doesn't seem like we have achieved a lot, and it's hard to see the full value in what we've done, but remember, Rome wasn't built in a day. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll use Sync Slicers to mimic that fantastic drill-through functionality in our intuitive button solution.

Sixth Day of Christmas: DAX HASONEVALUE()

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

Verified User Selections, 

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Verify User Selections with DAX Information Functions

There are a few DAX functions that don't really perform any calculation on your data model, but instead simply tell you something about the current state of your data model, including row and filter context. These functions can be really helpful in verifying that your report user has done what you want or need them to, before allowing them to see the next page, number or calculation. 

Today we are going to see how the HASONEVALUE function can help verify that a user has made a single selection in a slicer, which is a common requirement in many business scenarios. In our example, we need to verify that the user has only selected one name before we can check Santa's list to see if that name is on the naughty or nice list. We will use the HASONEVALUE function to verify that they have only selected one name, and then change the button text or prompt when they have selected exactly one name.

DAX Function: HASONEVALUE()

HASONEVALUE is not a vital DAX function. It can be replaced with the True/False expression:

COUNTROWS(VALUES(<column>))=1

That is exactly what HASONEVALUE() does, it simply checks if there is exactly 1 row in the column. HASONEVALUE is a very simple function that takes only one argument: 

1. Column Name (required)

The function returns "True" if there is only one row in the column or "False" if there is more or less than one row in the column.

Okay, so we have lots of columns in our data, and in pretty much any dataset they will all have more than one row, so what good does this function do? It tells us information about the current filter context of the report. For those of you who know a bit about DAX, you'll know that Context is very important. For those of you who are new to DAX, the HASONEVALUE function let's us see what our data model looks like after it has been filtered, sliced and diced by all the report visualizations and interactions from the end user. 

In the table above we have used the HASONEVALUE function on the 'Santa List'[Name] column: 

Name HASONEVALUE =
    HASONEVALUE ( 'Santa List'[Name] )

We have put this in a table visualization with the 'Santa List'[Name] column. You can see that when the table provides a filter context of one name (aka one row), the HASONEVALUE function returns "True", but when we get to the Total row and no longer have that filter context, the HASONEVALUE function returns "False". 

We will get the same result if we put this in a card visual with a slicer for 'Santa List'[Name]: 

This is pretty cool, but not exactly helpful in creating a beautiful report, so information functions such as HASONEVALUE are often used in conjunction with other functions to add functionality. Today, we will use a tried and true IF() function to change the text based on the result of the HASONEVALUE function. 

Button Text =
IF (
    HASONEVALUE ( 'Santa List'[Name] ),
    "Click to see if " & SELECTEDVALUE ( 'Santa List'[Name] ) & " has been naughty or nice",
    "Click to see Santa's entire list"
)

Tip: Information functions that return "True" or "False" are a great partner for functions that require a 'logical test' as one of their arguments. 

You may have noticed that HASONEVALUE returns "False" if the column has been filtered for more  than one name. In our button text above, we have written "Click to see Santa's entire list". This should make it clear to the user that their filter selections won't carry through to the next page. If this does not meet your requirements, you might find the ISFILTERED function to be helpful too.

How to: 

1. Open the SantaList.pbix file.
2. Create the Button Text measure from above.
3. (Optional): Test it out using a table or card visual, or if you're feeling brave try a button but we'll learn how to make those tomorrow.
4. Save the report.

That's all you need to do for today. Tune in tomorrow for the next gift in the 12 Days of Christmas series when we'll put this measure to work as a page navigation button. 

Fifth Day of Christmas: DAX SELECTEDVALUE()

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

Dynamic titles,

Custom theme colors,

Appended queries,

Table expanded columns

and

a PDF in Power BI. 🎵

Dynamic Titles

Today's post is going to look at how I used the SELECTEDVALUE function to make a dynamic title for Santa's list. As you can see in the image below, the title of the table visualization updates based on the selection - if the "Naughty" pie section is selected, the title is "Santa's Naughty List", if the "Nice" pie section is selected, the title is "Santa's Nice List". If nothing is selected, it simply says "Santa's List". 

Nice and simple, but really impactful and helps your data storytelling reach the end user more effectively.

DAX function: SELECTEDVALUE()

SELECTEDVALUE is not an original DAX function, and so I had gotten used to doing things the long way without it. Now I use it all the time! So much so, that I've decided to introduce it in this 12 Days of Christmas series before its predecessor (aka the long way); HASONEVALUE(). 

SELECTEDVALUE() has two arguments:

1. Column Name (required)
2. Alternate Result (optional)

I must say I'm pretty pleased with the name Microsoft chose for this one - it does exactly what it says. SELECTEDVALUE function returns the current selected value of the column. Since we're talking about DAX, this of course considers the context of your report and data model. 

To see how this function works, I am going to create a fun little measure that uses the SELECTEDVALUE function to return a different title depending on what the users selects in my report: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] ) & " List"

Ultimately, I want this to update the title of my list visual as in the GIF above, but to see its behavior I am going to place it in a table visualization next to the 'Santa List'[Naughty or Nice?] column that I have used within the function:

Note I have left the second argument blank, and so nothing is returned in the total row. This makes my title easy to read. I could update this to say something like: 

SantaListTitle =
"Santa's " & SELECTEDVALUE ( 'Santa List'[Naughty or Nice?] , "Entire") & " List"

Note how the total row now displays "Santa's Entire List", when nothing is selected in the  'Santa List'[Naughty or Nice?] column. 

How to:

1. Open the SantaList.pbix that we've been working on. 
2. From the report view, click New Measure.
3. Create the SantaListTitle measure from above (choose the one you prefer or adapt for your own use).
4. Click the + to add a new page to your report. 
5. Rename the page "Summary".
6. Tick [Name] and [Naughty or Nice] from your table to create a table visualization.
7. Click the Format Paint roller icon.
8. Scroll down to find the 'Title' heading - Turn it on. 
9. Click the fx icon next to Title Text.
10. In the Title Text conditional formatting pop up box, select:
1. Format by: Field Value
2. Based on field: SantaListTitle
11. Click OK. 
12. Add a pie chart to the page with:
1. [Naughty or Nice] in Legend
2. Count of [Name] in Values
13. Select Naughty from the pie chart and watch the table title update.

We'll continue working with SELECTEDVALUE function in this report throughout the 12 Days of Christmas series. Tune in again tomorrow for the next gift in the 12 Days of Christmas series where we'll look at SELECTEDVALUE function's predecessor HASONEVALUE and discuss why it still has value over SELECTEDVALUE.