PBI Spotlight Logo

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…Report Drill Through.

Report Pages Are No Longer Islands

DrillthroughReport Drill Through enables users to create a report page, filtered to a single entity (E.g. Customer, Employee, Store, Product). Reports often have a summary landing page where there might be a lot of information, but not much detail about a specific item. Traditionally in Power BI we’d create a separate reporting page for further detail breakouts on an entity. However, that would require it’s own set of SLICERS that you would have to re-select if you wanted it to mirror the reporting page you were coming from…that’s too many clicks!

 Selecting an item in Drill Through in a table from one reporting page will take you to another page, FILTERED to the entity you selected! This feature essentially let’s us create detail sub-pages that are linked to whatever primary reporting page the report uses. Features like this have been available for YEARS in Excel using linked cells, I’m super happy we finally have this as a feature in Power BI Desktop. It only takes a few steps to setup, but I’ll leave the instructions for that over at the Power BI September release page. With that said, let me run you quickly through how this looks like in a sample report. Smile


Animated GIF showing Drill Through functionality from the Company Page TO the Employee Page

PBI Drill Through Feature

 

One HANDY feature included in Drill Through is the addition of a back button on the page you drilled into. The back button allows you to quickly and easily return to the page you came from, especially useful if you’re working in a report with MANY report pages! You can see where I’ve placed the back button next to the (A) in the image below. You’ll also notice the addition of an additional filter field on the right side of the page (B). This release added a Drill Through filter here, and it is where you put the item you want to Drill Through into this page. In our example that item is the Sales Person column.

Employee Report Drill Through

What If I Wanted To See My Drill Through Filter?

I also took it one step further, and created a handy DAX Measure to DYNAMICALLY display the text of the Drill Through filter. If you look at the top of the report in the image above (C) you’ll see that my report title shows the name of the Sales Person I filtered to. I came up with this solution because I really wanted an obvious callout to the filter that was being supplied from the Drill Through feature. All we need is a DAX measure and a card visual!

Employee Name =
CONCATENATE ( SELECTEDVALUE ( ‘DIM Sales Person'[Sales Person], “Employee” ), ” Report” )

This DAX Measure will return “Employee Name” & “Report” if filtered, and “Employee” & “Report” if unfiltered. Placing it on a card visual and removing the category label makes it look just like a regular textbox too! Now we essentially have a dynamic report title on our page, calling out the exact filter being applied from our Company Page. Whenever I implement the Drill Through feature I will use a DAX measure like this as a best practice. Mainly because it will help avoid any confusion to the client about what was (or wasn’t) filtered.

The Honorable Mention Goes To…

Just because of how much I like their new Ribbon chart, I wanted to include that in this post as well. They also added this chart type in the September release, and it’s super powerful to show the performance of a category over time. In the image above you can see how easy it is to trace performance trends for Product Category over time. Using DAX it is possible to retrieve a Top Performing “thing”, Rob even wrote a post about that years back. I got excited when I realized I could now observe the performance of all PRODUCTS over TIME! Again, you can read more about this feature on the Power BI September release post. That’s it for today P3 Nation! I’ve included the embedded report below as well as download link to the pbix file, until next time. Smile

Power BI Embedded Report

Download the Power BI Desktop (.pbix) Report Here

X

Get Your Files

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro and the Owner of Havens Consulting Inc. His main goal is to collaborate with individuals and organizations by helping them analyze data to understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around teaching Business Intelligence, Reporting, & Design. 

This Post Has 22 Comments

  1. This is great! My boss has been asking for this feature for a couple of years now. Great way to demonstrate the functionality here also.

  2. Drillthrough is a welcome addition but still not good enough to be able to click on a number and get a report that shows what makes up that number right?

    1. Hi Vivek, that’s correct. It won’t create a report for you. But you could design a page built around that concept. So build another page with appropriate tables / charts to show the necessary details, then use the drill through function to get to that page and apply the filters.

      1. I didn’t mean create a report automatically. What I was getting to was it only allows 1 filter and not multiple filters plus it appears to be dimension based so not sure how a value can be clicked on to get to details. Maybe I need to give a good try and see if it handles what I am thinking about.
        e.g. Say I see that we have $20M worth of deals in Jul’17 and I want to see the deals we won that make up that 20M. I will have a report that shows deal level details but how would I setup Power BI so user can kind of click on the 20M and be taken to the Deal details report/page with just the deals that we won in Jul’17?

  3. You actually don’t have to go through the trouble of writing that measure to display what’s being filtered. You can just use e.g. the Text Wrapper custom visual and drop the dimension being filtered in there. It will display the value filtered from the drill through filter. Very handy if you’re direct connecting to a Multi dimensional cube where it’s not that easy to write your own measures (yet! *Fingers crossed*)

    1. Ivar, that’s a great suggestion. However, one thing I want the title to do is say “Employee Report” when unfiltered, something which I believe wouldn’t be shown with the text wrapper custom visual?

    1. Hi Dan, I’ve heard from some people that it downloads as a .zip for some reason (company intranet portals, etc…) If it’s downloading a file but it is not a .pbix file, go ahead and rename the extension to that, and it should work!

  4. Hi Reid –

    Thanks for structuring your blog with 1) Here’s something new from Power BI with a little more instruction on how/why to use it.

    and more importantly 2) Here’s some best practices to follow when you use these new features. The dynamic title is GREAT, and should save a lot of headaches.

    ~ Chris H

  5. As always, you guys are awesome! Other than presenting the new Drill-through filters, I loved the intelligent use of Selected Value to change the page title. Thanks also for sharing the file – it’s always nice to see how exactly you did something and compare design approaches.

  6. Both “Customer Details” report and “Sales Person Details” report have the same measure ” Top Selling Product”. I tried to use ISFILTERED function to leave the Total row blank on both reports. My problem was that I could only choose either ‘DIM Sales Person'[Sales Person] or ‘DIM Customer’ [Customer Name] as the argument of ISFILTERED to cope with each report respectively. I just don’t think it’s a must to author two ” Top Selling Product” measures when unfiltered for the Total row. Is it a workaround to tackle my issue? Thanks.

  7. Thanks so much for the great presentation. I also really like how you created the dynamic “Filter Title” to provide what I consider * a must when filters can be set by the user on any report *. Really nice work! PBI Desktop now with drill-through… I have been hoping this would be included in PBI at some point. It’s a super nice feature to have in a similar fashion the way SSRS reports has the feature. Thanks again for taking us through a demonstration to give everyone a sense of how it will look in Power BI and the bonus of a way to provide the filter information on the report. Good stuff!!

Leave a Comment or Question