I will show you five different ways. You can use Power BI
drill through. If you love measures you're gonna love number two and number one is so cool
It almost feels like science fiction. So let's get started. My name is Avi Singh
I'm a Microsoft MVP and best-selling Power BI author
And if you want to become a Power BI Pro, make sure to subscribe and click that bell
So you are notified whenever I go live to answer your Power BI questions.
Before we talk about the five different ways
Well, there is the standard way, which is done in three easy steps
Which I've already covered in my last video
If you missed that then you can click in the corner to check out the full video. Later
But we are gonna recap the three steps really quick. This is how easy it is step one
You create the drill through page step two you add the drill through field on
The drill to field panel here and step three. Well you go ahead
right-click and drill and again
You can watch the full video later on to check out more a step-by-step approach on how to do that
So let's go beyond that the standard way and let's figure out
What are the other ways that you can use power bi drill to and you're gonna find these really powerful?
Now the file that I'm about to show you you can always download that and frankly any video
Any files shown in my video and by going to learn power bi comm slash download?
So let's start and number five
Which is this idea that you can use multiple drill through pages to show different detail?
Let's take a look now drill. Truths are so cool
I want you to know that you don't have to stop at one as you can see in this report
I have quite a few of drilled through pages and you would notice that they're all
Hidden and that I covered in my last video in one of my usage tips
So go check that out later on but here are all all the different
Drill through pages that I've built and each one is serving a different purpose, but they can all play well together
So, how is it gonna look like for the end user? So in this case, I have multiple two options for country
So in the case where the user is, you know
What a visual is showing country and if the user does right-click and drill true
You notice that quite a few different options pop up here
So if I want to just look at the country detail kind of how the sales are doing
I can click on that and I can see oh great United Kingdom. This is how everything is going
And of course this itself is fully interactive and I can toggle my bookmarks or you know
Click on road bikes or helmets to look at anything like that. And then of course I can go back
to my back back to my report, but if I wanted to show a different
Drill true then I could maybe select the country demographic drill through so here, you know I Kingdom now
It's showing me the kind of the country demographic the population or been aged meaning age and it can I can go back right?
So you can see how this can provide a very rich experience to the user where they're looking at a report
But they can drill through to see these different
You know flavors or different depth of the data
And of course you don't have to you can also build drill to four different fields
So here we have the drill through per country, but I also have two available for the customer
So Janet Munoz, I can right click on that and drill through and say hey
I want to see the customer detail tell me everything that we know about Janet Munoz and and that pulls up this page
And so you can use it for that and then when you're done
Examining the state I can click and go back to your main page. So that was number five
Let's move on to number four, which is drill true on do different fields at the same time, right?
So you saw how I had built these different?
Drill to details, but is it possible to drill through using two fields at the same time?
It definitely is and you can see here how easy it is and it's as easy as adding
multiple fields on through the drill through panel here
so, let me just take these off for a second and we'll we'll add them back in so country and
Product category, so I'm going to go here and in here in this area
Which says add drill through fields you can add as many as you want in fact so I can add country
And I'm going to collapse that and then go to product and category
now
This is gonna be enabled when these fields are
Displayed are used in any visual
so now if we go back to our
To a different sheet that I have and in here you can see that I have a table which is showing
Both country and category and is showing me kind of this detail. So if I wanted to find out what's happening
Let's say in in Germany for the bikes category. I'll just right click and drill through that
so remember that Germany and bike so I say country product detail and
notice that both of them are fast in so now I'm looking at Germany and I'm looking at bikes and
You can see kind of the map with Germany
You can see details and bikes so that's how you can have drill through on multiple fields
Number three is a slightly advanced option and this is about knowing that you can drill through with filters on or off
Now if you look at some for drill through pages, you would notice this option there. It says keep all
Filters on and it is on by default and I would definitely recommend that you leave it on by default
But let's just see how this works and then I'll come back and talk about it more
So it's actually easier for me to demonstrate it by turning it off
So I'm going to turn it off for now and then I'm gonna check out the behavior. Alright, so let's go back to our
sales main report and here let's say so you notice here that if I hover on France
Or if I click on France, then I'm seeing a total sales of
1.5 million, right? So that's the number for France
So if I right click on this and then drill through an SI country detail remember that 1.5 million number?
Perfect. You see that in how it matches it up. So now I'm seeing the details of that 1.59
I can see the trend I can see its breakdown I can see you know
Kind of zoom in on the map and all that sort of stuff, right?
So all of that is available to me
but now let me go back and what if the report I was interacting and I was doing
Some of the stuffs and maybe maybe I was looking at UK and that's two point one, you know
But I was only interested in maybe accessories
and in their hydration packs or something like that, right, so
Accessories hydration packs, and if I look at UK, that is three five seven four, so that is the three five seven phone number
Right here. But if I right-click and real true and I say, okay, I want to see detail for that country
Watch what happens here?
the number here is
2.1 million, so that's that's again. This is not the default. I have changed the default
so this behavior the other filters on the page are not passed through and usually this is not
intuitive this is this is
Confusing to the users and that is that is why there's a good reason that this behavior is the default
So I'm going to turn it back on and then we're gonna check this behavior again
right
So I'm gonna go back here and turn it on which is the default and I'll come back
And and then let's try the whole thing again. So accessories hydration packs da da da and
UK that's three five seven four and let's go in here and say hey yeah, I want to see a little more detail
Given all the filters that I've applied in my page and perfect
So now you see that I see that number and of course the the page in this case is not that very interesting
But but still it's it's it's accurate and it's a it it's more it's gonna be more intuitive to the users
And of course all the other functionality is available here now
Another thing that I want to point out is that in this case keep awful there's on
Notice that this is the only one that we had added, but when filters are being passed on using this option
Then those are added as well
And if you notice carefully that these aren't italics which is brilliant work done by the power bi team
Just do you know give us a hint that hey, I mean, this is something different
This is not you know
So now you know that when you see these in italics does it mean fast in?
From the source space because it's passing in all the filters that were active on that page
there would be some scenarios where indeed you would want to turn the
Keep all filters off. And so you just keep that often in mind
Hey as I said
If you love measures then you're gonna love this one because this one lets you use measures as a drill through field
But hey, what does that even mean? So so far you've seen that we can
Take fields from our you know, kind of feel this here
You know country or category or something like that and just add them here, but you can also add
Measures here and and you know, it's easy for me to just show you how it's done. So sales detail
This is just a page that I made up and in this case
I'm gonna take it off for now and just add it back and show you
So again, you may not realize it but you can drag measures in but you know in in some sense
Dragging category make kind of sense that hey
You know, I wanna see detail for that category
And and so if the category is filtered the bikes of the country was filtered to Canada or US that's was being passed in
What is really being passed in if I say a measure?
well, as I said, it's easier to show so
By dragging a measure here. The first thing that you do is you activate this as a drill through options
so now in any place in any visual where the sales measured this measure that you dragged in here is
Being used if any user right clicks on there. This page is going to be available as a drill through options
so let's go back to our sales and let's recreate an example of
The hydration packs that we were looking at our hydration packs and United Kingdom
and I said
we don't have to necessarily filter on it and notice here that
This definitely uses the sales field and that means that the drilled through option is available to us
So here let's even find that you're not Kingdom daily
Alright so earlier we were seeing like a different level of detail
I country level of detail but now because this measure has that drill through activated that one is available to me as well
So any place where the sales measure is being used I'm gonna have this option available to me
Now so so again just watch this three five seven four and I see sales detail
Actually, what I should have done was I should have cleared this filter
So if you notice earlier than not this number was something crazy
I mean, I don't know sixteen million is something like that
but now you can see that this match is the filter that I brought and now of course I can go back and
Maybe do something else I can say in Europe France
2804 and go to the sales detail of that and I'm seeing the exact detail of that
So watch how powerful this is, right?
So anywhere where sales measure is being used you can see examine the detail can dive into it
You can show a lot of detail for that and not just that you could link it to your own
You know or like order tracking system or things like that or where people can go and I don't know process
Refunds or something like that. Now, of course, I don't have any of those. So I just I just use Google
But yeah, if you download this file, you will see how easy it is to kind of, you know
Create a URL like this and where I'm just I'm just saying
Yep
Go to the system and and and and pull up the details for this sales order number so you can see how powerful this is
So let's come back and talk about what does it mean to to add a measure in that field?
so what it really does is if you have
Gone through my tutorials you would you'd you know, you know, I can't talk about the filter context essentially
It's passing in the filter context at this cell
So whatever the filter context and of course, that's not a hard turn to understand. It's pretty intuitive
Frankly all the filters that are in place in calculation of this, right? So all the filters that are active for this cell
That's what is being passed in over there
We are on to number one, but at this point I would do a quick shout-out to our amazing power bi tutorial
So if you're just getting started or keep getting stuck in power bi make sure to check out our complete
Step-by-step power bi tutorial just click at the corner and you would have a link to this and all the other videos I mentioned
So let's move on to number one. And the number one feature is cross rapport drill. True now folks
I know you've probably heard that I'm like well now I know I really wish this had a really sexy name and if you can
So watch the video, this is really cool
And if you think of a really cool name, I drop down in the comment below. Let me know so cross rapport drill through
I'm just gonna take you to the browser. That's where it's easiest to show this so here
I am on power bi comm the service that website, right? So this is the same report that we were looking at
it's all published now, but you're going to see an
Interesting option which I've turned on here
so if you right-click on
on this country, you're gonna have the you know,
Because the standard drill through that we saw earlier and I can say country detail and that shows me all the country detail for United
Kingdom and that's how I want good map is taking a bit. There we go
Right, so, you know Kingdom it so it's working as usual
But if you're gonna notice something else here
Which is if you right-click there's one option
Let's try that again
There we go. And there's one option which looks slightly different
It looks slightly different because it is actually pointing out to a different report
So this is actually the report name and this is the name of the page inside that report. This is a totally different report
So again look over here in this corner and you can see that I'm right now looking in this power bi drilled through report
But now if I say hey United Kingdom drilled through and I and I click on this option
Notice that the URL changes up top
I'm looking at a completely different report, but look at the parameter got passed in and this is oh
This is so exciting. Right? I mean, it's it's like wow, how's this working? So
And of course they have made it really easy to use
So let's try something else and we're gonna take Australia and drill through and again population trend and and boom, Australia
So how cool is this? And again, this is a totally different report that is being loaded and
I'm looking at there's something really interesting that happened in Australia in year 1971
Hey
if you are able to figure out what happened in Australia in
1971 to make them so happy to have that spike in the population growth rate
Let me know just leave that in the comment below
I was just wondering about that but you probably saw that feature and maybe you're still wondering that
Really? Why are you getting so excited about this? Why is it so cool? So check this out
Imagine a really large organization doesn't have to be but you know
I don't know 50 people five hundred five thousand people something like that, right? So
yeah, so
55,000 it works the same way. It's still really cool
So imagine there's salmon production working over here and there's Karen marketing working on what their way over there
but the boat create Barbie iReports focus on different areas now if
Sam has something like oh cool carol has that detail in her report?
Sam can build a drill true from his
Barbie on model and report to the other report that Carol built now
Let me take you back to the browser now. You probably noticed that
these two
Were different reports, but what I didn't tell you was that these are not just different reports. These are different data set
Which means these are different B bi X. These are different power bi desktop files and again could have been created by two totally different
People so right so these different ppi x files, but they can each talk to each other and and say hey
Yeah, go go to that report and that dataset and get more detail from there
That's what makes it super duper cool for me man. This is like science fiction stuff. So he at this point cross report drill true
Currently it only works within the same workspace
Now that is a limitation and in general. What I would say is that drill true isn't always the best
Option for what you're trying to do. In fact, there are there are at least five other
Alternatives that you can use instead of drill through because again, sometimes it isn't the best option
So we're gonna cover that in our next video and you can click the icon in the corner to see if the video is now
Available or if it's not then make sure to subscribe and click that bell. So you're notified whenever our new videos go live
Until next time power on my friend
Hey keep watching more videos and keep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends