The Secrets of Drillthrough in Power BI

guys this is rick from bi gorilla and in this video i will show you how to use drill through pages in power bi i will show you the different methods on how to create them and i'll show you some tricks on how to make your life much easier so drill through pages allow you to get the details of a data point in your model so if you have a selection somewhere in a visual you can click on for one of those numbers and the drill to functionality allows you to navigate to the next page hold on to the filter context of the first page and basically specify the value that you already had it has some similarities with what you could do with pivot tables in excel by double clicking on a value and then seeing the details now this is big benefits let me let me show you what i mean with this so if we go to a report like the one we have here then on a high level you might have a page called sales and on this page you'll see several things like for example the stills over time you might see your stills by color and you might have a table that also shows the years and the color and then have the seal split out by those values and on a high level this is fine for a page but at some point you might also want to see the details of this so if i would click on for example this table here and i also want to see items like the sales order then of course if you wanted to you could go to the sales order number put it here as well and click it open but this is not a very convenient way to analyze your data because there's not enough space to see all of this and you might not just want to see the color but you might also want to see besides the order number you might want to see the color you might want to see the customer its first name is last name the quantity all kinds of things now i'm mentioning this because there is a functionality called drill through which allows you to navigate to these details and let me right away show you how that works so for example in 2012 for the color white there was there were sales for 2230 euros now i already configured drill through and how that could work is you could right click on it drill through next page and you will see the 2230 specify now with this specified you can of course scroll through and have a look at what this all means that's what drill two is now let's go through the basic setup and the different ways on how you can set this up if you want to get started with drill through the first step is to create a new page this new page will be your drill through page we start with an empty page and we can call this drill through now if you want to show a lot of details then i recommend you adding a table object you can click on table we'll put it down here a bit and increase the size and of course the fun thing is that now we have a lot of options to add columns in here so we might want to see the sales order number we might want to add the order date and then really the order date and then we might want to have some information about what was sold so we might want to see the product name there you see the color that sold who was the customer first my middle name last name and it's interesting to see how much of the sales were there and let me increase the size so you can all see this easily now if you're all happy with this then let's see how you can turn this into a drill through page so the first option to make something into drill through page is to drag a column into the drill to area and this column has to be part of the visuals you're having so on our first page here for example we see the color the color is in the table object here and if we want to specify this we want to go to the drill through page now where do you add those columns for your drill through you actually do that on the drill to page itself so i'm saying i want to be able to navigate to this page if there is a filter on the on the column with the color in there so i go to the product table select color click and drag and then there is an area in the bottom here which has a header that's called drill through and it allows you to add drill through fields now watch this the only thing i'm doing is dropping this column right here and in the left top you will find that a new button is created and this button allows you to go back to the page you came from let me show you this right away so we created this empty page we're on sales and you now want to see the details of this 185 000. so you can right click and now it says drill through and you go to this page and only because of what we just did adding that column to your drill through area we're able to use this as a drill through page now since we just got here you can control click on this arrow and you return on the page where you are now let's have a quick look at what other characteristics this drill through page has so because we have a drill to page any of the visuals that allows you to drill through if you put your your mouse if you hover it over it you will see that at the bottom of the tooltip it now shows right click to drill through and this is helpful to have some kind of visual cue for your users so this helps and then of course people still have to right click drill through and get to that page now it's good to know that when you drill through then in the bottom of that same page where you drill through fields are new fields are added so you can see that this color has a lock on the right here so that color field indicates that it was already there but you can also see what filters are in the filter context so that cell that we just clicked that amounted to the 42 000 was for the the color multi multiple colors and it was for the year 2012. now notice that if we switch this we can also take another year like 2011 and black we right click drill through drill two page then this overrides the filters that were in this drill for area so the color is now black and the fiscal year has turned to 2011. you don't need to change anything for that now i often recommend also hiding this page so people don't navigate here right away but need to use drill through so this was method number one method number one allows you to create the drill through page based on one of the columns in your model now what is one of the disadvantages of this method the disadvantage is that it only works for visuals that have the color column in there so that works for the matrix that also works for the bar chart that we have here because it's color and you can drill through to one of the colors and it's called black but where it doesn't work is a visual that does not have the the column with the colors in there so the visual right here i could right click but it won't work so let's see how we can make this more robust and always make this work with a measure so method number two allows you to configure your drill through page and navigate to it based on a measure and to do that i recommend deleting all the other columns now as as of now if i would go back and go to drill through it doesn't work anymore and then another thing you could do is you can drag and drop your measure that you want to use so your total sales you drag it right here and then if you go back to the sales page then you'll see that this right click to drill through now works for this first table so the 42 000 now drills through and again it still holds on to all the filters of all the columns that were in the filter context but if we go back you'll now also see that in this line chart which wasn't working before but this line chart uses the measure that we just put in a drill through page and because of that we also here can now right click drill through and drill to this page and that's super convenient because this right away makes it work so that all of the pages that make use of the sales measure allow you to drill through to these details now let's get to some of the tricks that make your life easier because as you might have seen it's not always super clear that you can drill through to a page and there is a way to make this more convenient for your users so what can you do well something that helps is to create a button so that your users know with this button if it lights up i can go to a next page so to make your life easier you can add a button and to do that you go to the insert tab you click on buttons and you click on a blank button and the blank button by itself doesn't do anything yet so we need to add an action to this button to be able to go to your drill through page so the first step is to select it then navigate to action select drill through page and then it will ask you what the destination page will be and in our case we only have a single page which is called drill through so you can select it and only by doing this you have already configured your button to navigate to that page so if you for example click on the 185 i could then click on this button and it navigates to that page but because it's not so clear that it navigates to the page like this we're going to make some adjustments so it becomes clearer so the third let's go to this button and the first thing i want to do is add some text and for me i usually like adding the word details great now the next thing is i want to get rid of the outline that's here so i click the outline away and now let's see something interesting the button that we use actually knows in what state it is so if the button is in the state where you can navigate to a drill through page you can give it a certain color now let me show you what i mean with that so the default state is the state where the button can drill to the next page so let's say i want to make that one blue without any transparency now what this does is let's see i could for example click on the h62 and after clicking on this the button should know that i can now navigate to the drill through page so as you can see i click and it turns blue and that has everything to do with what we just did here so in the default state it turns blue and if we don't select anything it is basically disabled and it will be light gray like here so it's light gray and here is blue now the same thing you can do for the for the text that you're using so we have text here and we can have the font color in a default state to be white and in a disabled state it's just gray like it is right now so we click again the details light up and that's perfect now of course this button already indicates that you can drill to the next page so i can click on the color red and then click on details and there you go so try to make use of that because your user is going to love it and personally i also find this button a little bit easier than right clicking and clicking on drill through so i hope that helps you in this video it just was a short introduction to drill through and how it works in its fundamentals watch out for my next video where i'll be posting how you can use drill through with different relationship to your measures so you might have a measure that has a relationship with the order date or the ship day and how can you incorporate those two to work with a single drill through page if you want to know look out for my next video and if this brought you any value so far give my video a like and i'll see you in the next one [Music] you

Channel: BI Gorilla

Views: 18,491

Keywords: Power BI, Power BI Desktop, Power BI Tutorial, Power BI Tutorial for beginners, Drillthrough Power BI, Drillthrough, drillthrough with measure, drillthrough with column, drillthrough page, drillthrough button

Id: uDC1dQxJXdk

Length: 12min 16sec (736 seconds)

Published: Fri Jan 07 2022