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.