DRILLDOWN and DRILLTHROUGH in Power BI - Taking it to the NEXT LEVEL

are you ready let's do this so we have drill down drill through expand down mind-blowing at the beginning but what they all have in common is that they let you explore in depth more details about your data now let's have a look at drill down first so the first thing that we need is unhide key and the most common one that you will find is a date hiking where you have the year the quarter then the month but it can also be for the category subcategory than the product so this visualization gonna first use the date hierarchy so I'm gonna create a bar chart and I'm gonna take over here from the dem daily table the date field let's put it on to a visualization and you see it creates the date Heike automatically for us I want to analyze sales so I'm gonna go here to my metrics table and I'm gonna drag total sales on to my column values now that we have a visualization you see that here in the top right corner we have different buttons and they let you drill down drill up expand down now let's have a first look at the drill down the future turn it on just by clicking on it and now if I click on one of the bars I will drill down to the next level in the hockey for that gear column that I click on so let's for example take 20 20 I'm gonna click here on this column and it drills down the year 2020 to the next level and hockey which is quarter so therefore and now see it's sales values for quarter one and quarter two now if I want to go back up I can simply use the drill up button over here alright so then what is this button that we see right next to it so here we can also go to the next level in the hierarchy and now we have quite a one two quarter four however not for one specific year but for all of the years added up okay so when we have here quarter month this is the sum of all the quarter ones in years 2018 2019 and 2020 okay this might not be what you're looking for no no so let's go back up by ending up and let's click now on the next button expand all down one level and Nike and you see that now we have still the year information in there and the next level in the hierarchy which is quarter so it combines the two so we don't lose any information about the year so we have quarter one two four four 2018 2019 and 2020 and also here if I want to expand further down one level I just click on it again and I go to the next level which is the month okay and if I click on it again I would go to the day level and also here I can drill up to go one level up in my hockey now this also works with different hierarchies okay so let's take the product hierarchy as it example so I'm gonna take out the data hierarchy now I'm gonna go to my name product table for products we didn't create our IKEA but what we can do is drag on put a category subcategory put it name one by one and it still adds us drill down to the next level that I put on the axis so let's start off with product category name and then I'm going to put in product subcategory and then also product name now that we have our fields on the axis you see we have to drill down buttons at the top right corner and I can go over here to drill down for example on the audio cone now you see we have only those subcategories that fall into the product category audio now let's drill back up and now I'm going to use the expansion down button ok so now we have all of the subcategories concatenated with the different categories and you will see over here we have computers projectors and screens first-day category computers then the subcategory laptops so you don't need to create an express high in your day the model for this to work however it makes things a lot more convenient instead of dragging all three fields separately we could create a hierarchy and then just drag the IQ on to the axis so let's do this so over here to create a higher key we just take the subcategory name put it on to the category name and this will already create the hierarchy for us and it leaves the original fields in the lace okay so it doesn't mean place them now we also want to have product name so we can drag and drop it onto the hierarchy or you just click here on the ellipse right next to it and then add to hierarchy product get going name hierarchy and it will also be added to our hierarchy now of course in the end you want to rename it so let's double click on it to rename it to product ID so now that I have my key I take away all of the fields that I put on my access and now I can simply click on product hierarchy and there you go now I did exactly what I did before but just with one click so not necessary but it makes stuff a lot easier ok so let's give this a try I'm gonna expand one level down we have the categories as well as the next level in a hierarchy the subcategory name however you don't see these nice buckets where we have one bucket for computers that one for audio etc now to do this you need to go here to format then we go to the x-axis and you have to scroll down over you to concatenate labels first of all this needs to be off however somehow it's still concatenating the labels in our visualization now this is because you need to do a second thing and that is you need to sort on the product category subcategory so go to the top right corner and make sure that you sort by of total sales but for the category name and subcategory name and now we have clean buckets one for computers cell phones audio if all the corresponding sub categories just like we have before if the years quarters and months just makes it look a little bit clear okay so if we use the drill down feature on a specific visualization what does this do to all of the other visualizations that are in the same page in a report now for this let's have a look at a different page in a report now here we have a similar bar chart as before however I also added some other visualizations to it for example here we have a tree map and some cards now if I go to my bar chart and I turn on drill down and let's say we want to drill down on quadrant 2 then this also filters all of the other visualizations that are inside of my report you saw the tree map changing and also the two cards changed now if I drill backup you can see it again no but what if you only want to drill down there specific visualization right then you would have to go here to data and drill and here you find apply drill down filters to either the entire page or it's like the visual okay so if I select from here you select it visual I'm gonna do the same thing again I'm gonna drill down quarter to of 2020 you see that's nothing to the other visualizations now let me switch this back to entire page now what if I do want this drill down to work for the cards but not for the tree map what you can do then is to go to format and then edit interactions and then here we can change how this visualization interacts with the two cards so we have the bar chart selected I want to format added interactions and then here we can just say do not filter this card and do not filter the forecast cards okay so added interactions gives you full flexibility of how this specific visualization interacts with all of the other visualizations that are on this report so we have seen how the drill down feature works now let's also have a look at drill through go through also lets you explore your data in more detail however now the detailed view you're gonna put on a different page so let's have a look how that works so we're gonna need two pages one page that will serve as the main overview bench and one page which will contain more details that we want to see about a data now for the main page we're going to use the report page from before and we're gonna use dispatch for our detailed view where we show more details for that say a certain subcategory now to know which subcategory we are drilling through on I created here a card visualization and I'm gonna put the subcategory onto this card now at the moment we have not drilled through on any of the subcategories so it just shows the first one which is Bluetooth headphones now to turn on the drill through feature you have to be here in the field section and all the way at the bottom we have the 12 room section where we can add the fields that we want to use for 12 now in our case that's going to be the product subcategory I'm gonna put it on there and when you do this you enable the drill through functionality and this also means that if you want to go back to the main page the main overview page you can use this button that has automatically been added for you now instead of putting in here I want to have it here in the top right corner let's make it a little bit smaller alright and let's see if this works okay so I'm gonna switch to the main overview page and let's take the tree map as our example here so I'm gonna right click on laptops then go to drill through and here you see the name of the details page which I'd called details and this brings us to the details page as you can see now that we have the sales for the subcategory laptops and if I want to come back I can use the button in the top right corner just control click on it and it brings me back to the main page now let's give this another try for projectors and screens I'm gonna right click drill through and then let's click on details you see now the details for projectors and screens now you can also decide if you want to keep the filters that you have on the page from which you do the drill through now I'll simply be here on the details view and then go to keep all filters and turn it on or off now if we have this one on and I go back to the main overview bench let's say I go and select a specific storm let's go for Amsterdam and I form the same drill for that before right-click drill through details now it shows the sales for projectors and screens but only four they answer them stored because I put on the keep all filters button here on the right hand side now a few more things about drill down and drill through and from where you can perform these operations so a drove through we just did by right-clicking on a certain sub category and then going to drill through now instead of that you actually can also select the visualization go to data drill and then from here turn on the drill from the future now what does this do well it's that right clicking you can now left-click and you see to a few will pop up now if this one is not checked and you click on certain sub category what happens is that it uses the filter or highlights feature so it filters or highlights everything for projectors and screens if I click now on laptops it does the same thing but now for laptops and if we go to the bar chart visualization where we have a hierarchy we can go to data drop and use drill up drill down expand that to next level just the same as you find here in the top right corner or when you right-click on that visualization I hope this video give you a good overview of how to use drill down and drill through if you want to learn more about power behind consider subscribing to this channel or give it a thumbs up thank you and see you in the next video

Channel: How to Power BI

Views: 60,028

Keywords: power bi drill through, power bi drill down, power bi drill up, drill through power bi, drill down power bi, drill through in power bi, drill down in power bi, drilldown power bi, power bi drilldown, power bi drillthrough, drill through button power bi, how to use drill through, drill through how to power bi, power bi for beginners, power bi, power bi tutorials for beginners, power bi desktop, drillthrough power bi, drill through, drillthrough, drill through report

Id: O7aL5v1r6Q4

Length: 12min 55sec (775 seconds)

Published: Thu Jun 18 2020