DA-100 Microsoft Power BI – Level 4: Adding more control to your visualizations Part 3

9. Drillthrough

In previous videos we had a look at interactions between visuals. For instance, I could click on Greater Manchester and I can see it highlighted. Or if I edit the interactions by going to Visual Tools format edit interactions, then I can change it from a highlight to a filter so that when I click on say Greater Manchester, all I see are Greater Manchester’s results. But what if I wanted to do more than that? What about if when I click on Greater Manchester I want to have the option of being able to see a very different set of visuals potentially based on what I’ve just clicked on saw Greater Manchester. For instance, I want a page specifically designed for individual regions and we can do this by something called drill through. So what happens is that I can click and clicking on a particular region and I can get an option, a drill through option to say, go to another page which is based on your selection.

Now this second page is probably going to be quite differently redesigned from the first page. So let’s see what we can do. The reason for that is we don’t need the column of data plus this graph that’s probably getting a bit too far. We probably just need one set of data. So maybe if I have a clustered column chart and I will put in the date, the years going across. So I’ll put this into the axis and I will put the sales volume in the value so we can have that as one particular visualization. And then maybe I’ll copy this and have a second visualization based on the month so we can see the monthly rate of change. And then maybe another chart which shows the percentage of the change over the twelve months so that’s in the value and the date so we can see that. And then maybe another chart. Let’s have a look.

Maybe we’ll have a stacked chart and this stacked chart can have again the date and we’re going to have the no, we’ll make it a line chart and we’re going to have the flat price, so that’s apartment price and we’ll have the detached houses price and we’ll have the semidettached houses price and we’ll have the terrace price as well. Now I wouldn’t actually design a web page necessarily like this. I would be using other visualizations that we haven’t yet got onto, but we’ll have a look at those in future levels. So currently this is for all regions and I want to narrow it down. So here is my series of charts. Now what I do is I scroll down to the drill through section in the visualizations pane and I drag what I want to filter by, in this case the region name into there. Now you notice there is one option, keep all filters on or off. I’ll just switch that off for now, I’ll turn it on later.

You can see what the difference is. So region name is all and allow dropu when they’re being used as a category or when they are summarized. Now I’ve not actually yet come through any actual use situation when you should use summarized it seems to be the case that you always use it as use as category and I’ve done quite a lot of searching to try and find an answer to that question. So always use the Drill through as useless category.

So all you need to do therefore to activate drill through is just search drag the field that you are going to be filtering on into the drill through section. Now if I go to any particular region nothing seems to have changed but if I write and click then I have a new option drill through and the name of the page that I can go to. So drill through charts. So now it is solely showing me Greater Manchester and you can see that down here region name is Greater Manchester. Now it would be good to have this as a title. What I’m going to do is just use a visualization we haven’t actually covered yet and that is the card visualization. I’ll just insert a card visualization which just carries basically one piece of data and I’m going to add into the region name and there we can see Greater Manchester but I’ll be covering the card in greater detail later. So I’m just going to apply a background to that using the formatting. There we go. And probably just reduce the size of the text as well and that’s in data label so I can go back and click on a different region drill through charts and there we have West Yorkshire and this needn’t just be limited to one particular page. I can go to other pages, pages like the matrix page and here we have Merseyside.

So I will drill through and find the Merseyside data and if you want to make sure that this is merseyside, well you can always get rid of the filter temporarily by just clicking on this eraser. And now it says at the top Greater Manchester. It really is for all the areas. Now I can also drill through analyses as well. I don’t just have to click on the region name. So here I’m clicking on a number and that’s getting me to the time and where chart. Now what is this? Keep all filters. Well let’s go back to this example and I’m going to click on 2004 and you can see it’s going to highlight all of the 2000 and fours we had previously set this up in the edit interactions and you can see that there is a filter going on between these two visualizations. If I now have a look at the drill through for Greater Manchester you can see that it ignores that 2004 filter that I’ve got in my page one chart, my page one page. If however I click on keep all filters, go back to here, I’ll click on 2005, drill through Greater Manchester and now it’s just showing me 2005. So we now have this chart, a monthly chart being solely based on 2005.

So that’s the difference between these the keypole filters or not, whether you have any of the other filters on your originating chart coming through or whether you want to exclude them, and just show the entirety of the data except for the region name that you’ve just clicked on. So here you can see the visualization of all of the other filters. So we’ve got the slicer filter that’s been incorporated. So, again, if there were no filters and I was just drilling through, then you would see from 1995 all the way to 2016. If, however, I do keep all of the filters, even if I don’t select any particular year, if I drill through there because of the slice of filter that’s on the right hand side, it will apply that and will have from 2000 and 2009. So keep all filters very useful, but use with care, I would suggest.

So if you want to be able to drill into greater detail on a particular topic, in this case a region name chart, then you can do that by going to the drill through and setting a filter based on that particular column. And then that becomes available to all of the different charts and pages, not just the one that you might initially thought that would be a good idea if that was there, but to all of them it’s a very useful feature. Just one word about terminology, the difference between drill through and drill down. If I click on this particular chart, I can drill down to the next level.

So currently it’s looking at years. And now if I click on any particular year, it will be showing me 2006 of that particular year. So that is drilled down, drill through, goes through to another page and shows information based on what you have drilled. So drilled down, you remain on the same page. Drill through, you go elsewhere. Just one final note.

You may have seen in more recent versions of Power Bi that there is this Cross report. So this allows you to drill from one report to another report. So this is used on the Power Bi service. So we’ll be having a look at this when we look at the Power Bi service because all of these reports need to be uploaded onto the Power Bi service for this to actually have any functionality. So cross report used for when you’re going from essentially one file to another file on the Power Bi service.

10. Buttons and Actions

Now, when you drill through, you’re going from one page to another and it’d be nice if you were able to go back to where you came from. And you can do there is a back button that gets added as part of the drill through. And if I click on this back button, you can see CTRL and click Here goes back to the previous page. And do that, we get back to our original page. But what if you wanted to make the option more explicit? Well, we can use buttons. Now, buttons along with other things that we have previously inserted can be used as little indicators on how to navigate. So if I click on the left arrow button, you can see we have a much thicker, more prominent left button here. So now if I drill through, you can see there it is. And if I control and click, nothing happens.

After a whole it’s only a button. I’ve not programmed it to do anything. It looks like a left arrow button, but I haven’t said go back to the previous page. And that’s where we can go to the visualizations pane action. So if I put action from off to on, you can see that there are Back Bookmark Q and A and Web URL. So if I go back, or if I say back, let’s see what happens. Now if I go through this, drill through. So control and click here to go back to the previous page. It lets me go back. Now you might be thinking, is that really this little button or is it what’s underneath it? Well, I’m going to move it to one side. So let’s try this again. Drill through, hover over this and you can see it’s offering me control and click here. Now, we don’t just have to use buttons for this, although there are some nice predefined buttons. We can also use images and shapes.

So these again, if I just insert a random image and a random shape, you’ll see that they too have an action with the same options back Bookmark Q and A and Web URL. So whichever of these you use, whatever it is, it needs to be friendly. It needs to be user friendly so that the end user knows what’s meant to happen. So don’t just insert a random shape and assume that people will get it. You can add some text to your buttons. So if I click button text to on, we can say go back. So that is even more of a clue. You can switch the icon off if you so wish, though obviously don’t switch it off and have no text, otherwise it will literally be invisible. But you can change the shape of it if you so wish from the icon section and also the horizontal and vertical alignment and line color.

So you can have a nice red arrow with a deeper weight if you so wished. So let’s go back to my presentation. I was doing various BOOKMARKS, if you remember, from an earlier video. So now I can add little icons to allow me to go on to these different BOOKMARKS without having to open up the bookmark pane. So if I just put in a series of small rectangles, these could be images, say one, two, three, and that sort of thing. And in fact another way of doing this is by putting on a button, dragging this across, adding some text and then removing the icon. So as long as you’ve formatted the text correctly, that’s another way of doing it. But I’m just going to largely stick to the images, the shapes. For now though, as I said, I would probably more likely be using an image. At least I can stylize those a bit easier. And now what I can do is select an action for this first one and say I want to show the bookmark overall and then the next one I want to show the bookmark 2000 to 2009.

 Next one I’m going to show a different bookmark and so on. So now in my presentation I can just click control and click that is there, click click, and it gets me to different parts of my presentation without me needing to open up the BOOKMARKS pane. The last action that I’ll be showing in this particular video is the action to go to a web URL. So this is a fairly obvious thing. You type in your URL, you type in your tooltip, go to Microsoft, hover over it and you get the tool tip. But nothing actually happens. So remember I typed in www. microsoft. com but you notice it was in red. It’s telling me there’s an error. I’ve got to put Http at the beginning for it to actually work. So now when I hover over it and CTRL click, it gets me to the Microsoft website.

So if you see something that as you type in, which is in red, don’t ignore it. It doesn’t look like there’s an error message, but as soon as I clicked off here and clicked back on, the version that I had put in wasn’t there. So I’ll just be careful with that. Now I’m going to click on Revert to default and I’m going to change this action again. And this action is going to be a page navigation action. So this allows me to go to a different page in my file. So it could be that I wanted to go to the bar chart and I’m going to have a tool tip. Go to Bar Chart. Now you may not have by the way, the page navigation action, it has been added, I think it was in 2019 or 2020. So if you don’t have that then you may wish to go to a later version of Power Bi. So now if I click on this you can see the tool tape. Haven’t clicked on it yet.

Click on it and nothing happens because it thinks I want to alter it. Instead, in here, the Power bi desktop, I have to use CTRL and click and that will take me to the hyperlink. Now, there is one additional action which I haven’t covered, which isn’t actually here because we’ve covered all five of these apart from Q and A, which we’ll be covering in the next video. So where is this hidden 6th type? Well, this 6th type is currently only available with buttons.

So let’s add a button. I’m going to use a right arrow button. Now, the 6th action that can be used specifically for buttons is drill throughs. So we had a look at drill throughs in the last video. But instead of having to click on a particular item and write and click and go to drill through, we can use a button to do that. So let’s have this button with drill through. We’re going to our destination. Now, there’s only one destination which is enabled for drill throughs. So unlike the page navigation where we have every single page bar itself, you can’t go from page one to page one. Here we only have the destinations which are drill through enabled. So I’m going to drill through enabled using Region Name chart. So I’ve currently got Greater Manchester selected.

So if I control and click on this, we get through to Region Name with Greater Manchester. If I change this to Merseyside, we get to Merseyside. Now, if I don’t click on any particular item, then you’ll see that I don’t have any ability to control. Click on it and go through to report. It says I need to select a single data point from Region Name first. So notice what happens to this button when I click on Region Name it becomes enabled. And that’s important because we have got an enabled tool tip and a disabled tool tip. So you can see that there are tooltips automatically added first. I’m going to now say click to go through to the Region Name and the disabled tool tape. You need to click on a region name above first. So here we go. Click to go through to region name. And if I deselect all of these region names, then you need to click on a region name above first is shown. Now, you’ll notice that there is a difference in the color between enabled and disabled. And there’s also additional things you can do. It may be that you want a different color when you hover over something.

Well, we can do that in the formatting. For example, button, text, icon, outline, fill. And this isn’t the case for things like shapes. It is specifically for buttons. So if I go into any of these, you’ll see that there is a default state. So this is your standard state. So I have my button text actually saying nothing. I’m going to have it say default. But this isn’t necessarily just the default state. There are other states as well, and one of these is disabled. So if I click on disabled, I can change the text. So let’s change the text to disabled. So we have a default state, we have disabled, we have hovering. So I’m going to say hover and we also have press as well. So I’ve only changed the button text, but you can also change the icon. So if you wanted it to be when it’s disabled blank, you can do. So you can also change things like the outline, the fill, anywhere where you see default state at the very top, you can change. So maybe I’ll have for the on hover, a different line color. I’ll make it red.

So let’s see what we’ve got. First of all, we have got our disabled state. Nothing happening, so I said no icon and I want the word disabled. Now I’m going to click on a region name and you can see that we now have our default state. If I hover over it, then we now have our hover state. And you notice that I’ve asked the line for the button, the arrow to change color. So there are other things I can do, of course. So in the hover state, maybe I want a different arrow. It’s got to be something that is going to be reflective for the end users to be able to go, oh, yes, this is what it means. And then when I click, you see that it says the word press. So I need to control click on this particular version to get through to the region name. So equally on this go back, I can change the default state, I can change the hover.

So when we’re hovering over it, let’s have not the button text, but did the icon change to a color, blue, for instance? That side, that’s the default state. Let’s change that back to red and let’s change the on hover state to blue. Default means this is what all the others will be initially, at the very least. So when it’s not disabled, when you’re not hovering like this, and when you’re not pressing it. So we’ve got there quite a few things that you are able to do with buttons that you are not able to do with other things. So in this video, we’ve had a look at buttons, but we’ve also had a look at actions more generally with images and shapes. Hopefully, you are forming ideas into your head about how you can help the end users of your ports be able to navigate to other pages or to drill through. But there’s one action that I haven’t had a look at, and we’ll have a look at that in the next video.

11. 11e. Enable Natural Language Queries (Ask A Question)

Now, the final action that I haven’t looked at is the Q and A. And I’m going to put this onto a separate page. So I’m going to insert a button, Q and A, drag it down to the middle. You’ll see that the default action is Q and A. For this one. Going to add some button text, what is your question? And make it nice and big. This can be incorporated into your own reports and pages, but I’m just going to have it standalone here. So I’m just going to move this one side just to illustrate what it actually does. And to do that, I’ll go to insert ask a question. So this inserts a new visualization. By default, it’s just a column chart, but it’s actually asking you in plain English, what question do you have? So I’m going to say, what was the sales volume? And you can see the computer is already answering the question, 4 million per year. And it converts that into a line chart, or I could say per quarter.

And then it converts it into a bar chart because it knows that that’s part of the hierarchy, not the top end, not the year. So it’s going to have that is non continuous. It’s going to have it as categories of data or per month in West Yorkshire. And you can see the computer does interpret that as meaning, okay, let’s find out what the sales volume is per month in the region name called West Yorkshire. It’s very clever. It’s not perfect. There are some things that it can’t do that it would like to do. If I control click on my icon here. We have got some suggested questions to get you started. For example, what is the average sales price per year in maybe per region name? So now I’ve done that. Firstly, it’s helpful for you to be able to quickly create your own visualizations.

But secondly, let’s see what happens when we’ve published this to Power Bi. So I’m going to save changes. I’m going to publish this and then I’m going to view it on the web. So let’s open it up. We’re in the workspaces. So it’s gone to my Q and A. I’m going to click on what is your question? And you can see I can’t actually get to the very top of the screen, which is quite annoying. So to be able to see the entirety of the dialog box, I have to click this enter full screen mod. So now I can ask my question. So what is the average price, let’s say per year? And you can see how it is being interpreted. Now, another thing you can do with the Q and A is bring up any one of your pages as an answer to a question.

So let’s have this matrix page, and I’m going to say I want this matrix page to be suggested as an answer to the question, what are the sales prices to do with sales volume. So if I just start typing in sales volume or what are the sales volume? And to do this, I go to the visualization pane, not of any particular visualization but of the entire page. So I’ve got nothing selected and I go to format page information and I select Q and A, put it on. So these are names that I’m putting in that the end user is going to type, which is going to suggest the word matrix. So sales volume, sales volume per year, that sort of thing. So whenever the end user types, that is going to offer this page as a suggestion. Now, just to have a look at the other formatting of the page, you can also change the page size, you can change the page background and you can change the wallpaper as well.

The wallpaper is the bit outside of your page. So if I change the page background here to a yellow and you might be going, well, why can’t I see yellow? Because it’s also set to 100% transparent, so I’ll change that to 75%. Or you could amped images if you saw wished. So I could add an image of a particular shape in the background. But I won’t do that. I’ll add a different colored wallpaper, I’ll make it red and I will have it a bit transparent. So you can see we’ve got wallpaper in the report and outside of the report, but in the report itself, we only have the page background. So you can see if I want it just for the yellow, I’ll turn transparency all the way up. If I want some of the red of the wallpaper to go through, I’ll change the transparency so it’s further down.

So I’m going to make it a fairly light yellow like that and 0% transparency, so none of the wallpaper comes through. That way it’s easy to see what is wallpaper, which is the entirety of it, and what is the page background, which is just the report. So now I have added in this additional question and I have said this page is going to be the answer to the question sales volume per year. I’m now going to republish this. So now it’s going to come up. I’ll just close the old version, click on Open, then I can type any question. For instance, I’ll type in the question sales volume and you can see that it is offering the matrix page as an answer to that question. So it’s thinking it is a synonym. The sales volume and matrix are the same. Now, if I just exit the full screen, go to the matrix here you can see the difference between the page background and the wallpaper.

So this report has been formatted as a 16 to nine. You can see that if I click on the page, not any particular visualization, go to page size, it’s 16 to nine. So anything that is outside of 16 to nine is therefore part of the wallpaper. So in this video we’ve had a look at how to use question and answers. We have put it into our own report so the end user can add questions and we have prepopulated some of the questions they might want to ask so they are not limited to just the ones that you do.

But equally you can ask a question and have a very quick answer to something in English which might be the basis of a visualization that you want to use. And then finally we have looked at synonyms of entire pages and how the end user can bring that out by typing in just a few words. Have that as a suggested answer. So this is your Question and Answer, which are also known as natural language queries.