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

1. 11a. Adding Text boxes, Images and Shapes

Hello and welcome to level four. And in this level we’re going to be adding more control to your visualizations. Now we’re going to start off fairly easily. We’re going to add additional elements to your pages that aren’t actual visualizations. For example, previously we have got this item, this visualization which you can click on and it highlights individual elements elsewhere and it’s also filters down on this table. But there’s no real way of knowing that without actually being a proficient power bi user. So what I’m going to do is I’m going in the home tab to insert a text box.

So you can see the text box has now appeared and I’m just going to say click on a year to focus on this year. Now you can see it’s being treated as a visualization. So I might want to add a title instructions for example. So I’m going to have this as a background, color of yellow, foreground color of black, have it center aligned, have it a bit bigger. So let’s close down title. Now you may notice that there isn’t actually a category for changing the font size here of the actual text. But you can also see that when I’m inside here, there is a bar similar to what you might have in Microsoft Word.

So that allows me to change the size alignment, bold italic underline and font as well. So I’m going to now resize this so it’s a bit smaller, maybe drag this down just a bit. And I think maybe also add a border because it doesn’t have a border, it only seems to have a border when you click on it. There we go. Now I want to link this in some way to this actual box. So what I’m going to do is I’m going to put a little shape. So I’m going to go from this particular box to this visualization. So here we have an arrow, a Ginormous arrow as it looks like. So I’ll just make it a bit smaller and then in the format shape pane, so this is the equivalent of the visualization.

We also have things like rotation. So I’m going to rotate this 90 degrees oops, wrong way, 270 degrees, that’s the right way. But you can also change things like the line color and the weight and whether it is filled or not. And again, you can change what the fill color is and even had a title if you so wish, if there was a particular shape that needed a title. Now there is another one called Action, another category here, but I will be looking at that when I look at insert buttons in a few videos time. Now maybe you’ve got some instructions on a website that you want people to be able to go to. Well, you could have another text box. So maybe I’ve got a text box here and I’ll say full instructions, click here. So resize that.

And what I’m going to do is insert a link. So this is a hyperlink to somewhere, so I’m just going to take it to Microsoft. com. But that could be your own website with full instructions or maybe even a video on how to use it. So you can see it’s only linked the word here, but it is hyperlinked. Now you can’t actually click on it while it’s still here in the Power Bi desktop, but when it’s published then you can click on it. If you want to just make sure you’re going to the right address, then you can always select the link and click on it that way. Additionally, you can also insert images. So here we have in Home insert image. This will allow you to find an image and insert it.

Now you notice it goes directly to your hard drive, it doesn’t look from the Internet and try and download an image. So if you want something from the internet, you will have to have previously downloaded it and it’s just any image, it could be your icon, it could be something relating to your company. And what I’m going to do is I’m going to lock the aspect ratio so that when I resize it, it will always resize in the same height and width. Previously, when I was trying to resize it, I could resize it like this. So we’ve got a lot of wasted space, which may be what you want, but I want the aspect ratio to stay the same. So the ratio between the height and the width. So that could be your icon, that could be attractive text to click on or attractive image to click on. Now you can’t add hyperlinks to go on to say, Microsoft Dom website, but you can do other things. Again, we’ve got this action icon, this action categories here and we’ll have a look at that when we’re inserting buttons a bit later.

So this video was all about adding something that could be helpful to the end user. We were inserting text boxes, we were inserting images and we were inserting shapes. So it’s not fully featured in the same Microsoft Word where you can do freeform shapes or whatever, but you can always do something elsewhere. So maybe if I go into Word and I do an image inward, so if I insert a shape which is more of a free form shape, there we go.

 And then I could always use the Snipping tool, which is available on computers from Windows seven onwards, to extract that and then save it as a file. You can tell I’m no artist, can’t you? So here is my shape and then insert that image. So that is one way of being able to import from other applications which are a bit more user friendly. So maybe that’s my new icon of my company. So we can insert text boxes, images and shapes. We will see in the future how we can also use the shapes and the images to do things. Actions. But with text boxes you can also add hyperlinks.

2. 11b. Visual level, page level and report level filters – basic filters

Your boss comes up to you and says, well, it’s looking good so far. There’s just one thing I just want to concentrate on greater Manchester and West Midlands. Can we do that? And you say, well, we could do that. We could click on Greater Manchester and West Midlands and that will focus on them by holding down control. But suppose we want to go a bit more. This gives the user user the end choice and I don’t want the user to the end choice says your boss, I just want to have this about Greater Manchester and West Midlands and that’s it. So what can we do? Well, we can use something called filters. Now, filters means that we don’t have to go back over the data. We have all of this data which includes Greater Manchester and the Westminsters, but also includes Merseyside and Tyne and we and the rest of it. We don’t want to have to get rid of that data physically.

We don’t want to have to re import a new set of data with just that, but we don’t want them to appear in our visualizations. So what we can use are things called photos. Now, there are three different types of photos and we’ll be adding to this by looking at slices later on, but just concentrating on photos. Photos allow you to remove from a particular visualization or a particular page or a particular report all of the pages, various bits of data, so you can concentrate on particular bits. So let’s say I wanted to just concentrate on, as my boss said, gritty Manchester and West Midlands. Okay, well, there are three different types. First of all, we have the visual filters. Now, the visual filters you’ll see are set up based on what you can see. So they are a visualization filter, but they’re also visual, so they’re based off on literally what you can see.

So if I go into region name, currently region name is all. Now, if I click on it, we then have something called filter type advanced Filtering. For the moment, I’m just going to show you basic filtering. So we click on that and change this to Boots basic Filtering. And we can say, well, I only want to see Greater Manchester and West Midlands, and you see that, everything else disappears. Now, you could say, I only want to see one thing, so I only want the user to be able to just click on one particular item. Or you can say, Well, I’d fine. If the user selects multiple items, they can select all. Now, selecting none doesn’t make any sense because then there’ll be no data to be shown in your visualization. And while things like Excel might interpret selecting none of the categories as being shown nothing here in Power bi, it just means it ignores the filter. So I’m going to select Greater Manchester and West Midlands. And you’ll notice that up here it says Region name is Greater Manchester or West Midlands. Even when I collapse it, you’ll see that there is a filter on because it doesn’t say region name is all. Now, when I hover over it, you’ll see this little icon. It looks a bit like an eraser, a rubber, and that removes the filter. So let’s like that again. Greater Manchester and West Midlands.

Now, because this is a visual filter, it is only looking at this one visualization. If I don’t want this, I’ll click on the eraser here and I’ll scroll down to the page level photos. This will affect everything on this particular page, but there’s nothing there. So we haven’t got everything set up like we have with the visuals, because we’ve added the columns. Instead, we have to say what our filter is going to be. Well, I want to filter on the region name, so I’ll drag region name across and here we get exactly the same thing, basic filtering and advanced filtering. You might notice a third option which was available in the visual filters called Top N, which we’ll have looked at in a future video, isn’t there? So again, I can say greater Manchester and West Midlands.

And you’ll notice that instantly all of the visualizations on this page have been updated to reflect that. Now, that’s not the case for other pages. Here we can see in the matrix that we’ve previously set up, we still have all six. So let’s remove this photo. I’m going to click on the X to really get rid of it and take it down to the report level. Now, instantly noticed that I couldn’t have just dragged it in easily from page level to report level. I actually have to remove it and then recreate it. So in the report level photos, I’m going to put Greater Manchester and West Midlands. And now if I go to the matrix page, you can see that it has now been updated to reflect just those two categories. So this is basic filtering and you can basic filter on text, you can basic filter on dates.

So if I drag a date in here, you can see we’ve got basic filtering as well. And you can see this very small scroll bar there. The formatting is not brilliant. I’ve got to hover over each of these to say, well, I just want to concentrate on the March the first, 1995. Maybe I need to just add a bit more weight to really be able to do that. And you can also do basic filtering on measures as well. So I want to have basic filtering only when the sales volume is 458. So you can imagine not as useful for dates and for numbers, which generally are in ranges rather than discrete categories, but still doable. So. Photos, we’ve got three different types. Visual level photos, PageLEVEL photos, which affects all of the page, and report level Photos, which report, which affects all of the pages in this report.

3. Advanced Filtering

In the previous video, we had to look at basic filtering. In this video, we’re going to have a look at advanced filtering for text numbers and dates. So advanced filtering for text gives you a few options, and these are similar options you may have had in Excel. So does the string contain or not contain that certain words, words or letters? Does it start with or does not start with certain words? So, for instance, we can say, does it contain the word the letter W say? And you can see three of them contain W, some at the start, some of the beginning. Does it begin with the letter W?

So I’ll click on apply filter and you can see we’re down to two. Is it so I’m going to put is it West Midlands or is it not West Midlands? Is it blank or is it not blank? So, quite useful. For instance, suppose you have a list of employees names and you wanted to find all of those who had a middle initial or who didn’t have a middle initial. Now, suppose that you wanted anything I’ll just get rid of that photo, anything which has the letter W and is followed at some point with the letter M. Well, this is when we can use wild card. So I’m just going to try W asterix M and apply that filter and you can see it works. So the Asterisks is exactly the same as it is in Excel and in Windows Explorer, we have exactly the same sort of wildcards. The Asterisk of a star is zero to infinity characters. So if we had W star E, then it would pick up both West Midlands and Weir and Tyne and West Yorkshire, because it’s W followed by E with zero to infinity characters afterwards.

So here there are zero characters in between. Similarly, we can have w question Mark s here. The question mark stands for a single character, so it doesn’t stand for zero, doesn’t stand for two, it’s strictly standing for one. Now, we can combine this, so we can have all of those that contains the word west and all of those that contains the words Midlands. So needless to say, there’ll be only one. But it could be that we have a bigger data source with the East Midlands, for instance. So just filtering by Midlands wouldn’t have been as sufficient. Alternatively, you could have something that includes west or includes Manchester, so that would expand the filter to include both West Midlands, West Yorkshire and Greater Manchester. So the or is restrictive, so the and is restrictive. The or is expansive, so the and narrows down your filter. The or includes other options. Now, you can of course use the advanced features, not just in the visual level filters, but also in the page level filters and in the report level filters. Now let’s have a look at examples for dates.

So we don’t have any dates in here, so I’m going to click on this visualization and have a look at the date year here. So we have the basic filtering which gets us to individual years. And because we’re looking at years, we’re really looking at numbers. So let’s see what the advanced filtering is for numbers. And we have probably what you’d expect is less than, is less than or equal to, is greater than, is greater than or equal to is exactly, is not exactly is blank and is not blank. So for instance, suppose I wanted all of the years between 2000 and 2009.

Well, I can say there isn’t a between your notice. So I have to say is greater than or equal to 2000 and is less than or equal to 2009. So that gets the equivalent of a between. So between 2000 and 2009. So now you can see it starts at 2000, goes all the way to 2009. We also have is is not, is blank and is not blank. So that’s what numbers are, which is really what this is. But what about if we were to put in a date filter? Now firstly, we have got two visual level filters already, but we can add a third. So these are the columns that we’ve set up.

But I’m going to add a third filter just by dragging it into that sort of area and it gets added. And it’s the same for page and reports level photos. You can have multiple photos just by clicking them nearby. So a date photo, advanced photo, you can show items when the value is exactly or is after or is on or after or is before or is on or before or is blank, or is not blank. Now there’s just one word of warning of this. Let’s say I wanted everything in the 2000 to 2009 range and this computer is set up in the American date format and you can see that with MDY month, day, year.

So I’m going to say it’s got to between, so it’s got to be on or after the first of the first 2000 and has to be on or before the twelve of the 31st twelve months 31st date. So December 31, 2009. And apply the filter. And the filter appears to work and it does work in this particular instance, but the complication is that we have this time. So let’s just have a think about some of the dates. So January 1, 2000, that will be covered. January the first, 2001, that will be covered. January 1, 2010, that won’t be covered because that is not on or before the 21st december the 31st, 2009. December the 31st, 2009, that may or may not be covered. And the reason is, suppose this was, say a list of orders and the date that the order was received. So let’s say an order was received on January 1, 2000 at twelve midnight. Well, that’s fine, it’s on or after the first of the January 2000 12:00 A. m. . Suppose it happened at one, that’s still fine. Two, three, all of the other time are fine. Similarly, at January the first 2010, midnight would be afterwards, 01:00 A. m. Would be afterwards. But let’s consider the December 31, 2009. If it’s twelve midnight, that’s fine. It is on or before December 31, 2009 and we see a time. But what if it wasn’t 01:00 A. m. ? Well, that is not on or before December 31, 2009 at midnight and that would be excluded. So this is a bit of a danger of setting izano after and is on or before for dates.

Now, what you could do is say make it 11:59 P. m. , but I wouldn’t even do that simply because what if it’s 1159 and so and so seconds? It will probably be caught by this photo, but I want to be 100% sure. So what I use is before and reset the time is before January the first 2010. So in other words, it goes from January 1, 2000 to January 1, 2010, but don’t include January 1, 2010. So that includes December the 31st, all the way up to 11:59 P. m. .

So just a word of warning, when you get close to this end bit, it really makes a big difference what you have put as it is before it’s on or before. So just make sure you’re not being caught out. There is one other type of advanced filtering, I would say, and it’s called relative date filtering. And that says I want to see items which are in the last or in the next 30 days, weeks, calendar, weeks, months, years, so anything which is in the last 30 days, anything which is in the last four weeks, anything which is in the last year, and you can include today or not as you see fit.

So I want anything which has been in the last, I’ll say, five years. And then if you run this next year, you’ll find 2014 gets excluded and any new data might get included. So that is the advanced filtering for text numbers and dates. So you can say is it within a range? Is it before, is it after? Or is it exactly this? And with text you can also use the Wild Cards question mark for one character and Star for multiple characters.

4. Filter Top N Items

Now, the final piece of filtering I want to concentrate on is one which concentrates on just a few items. For example, I might want just two items from region name. Now, it could be that I can select those particular items. For instance, suppose I wanted a visual filter on on Greater Manchester and the West Midlands. Now, let’s just remind ourselves of the various sales volumes as we have previously seen. So you’ll see that suppose I wanted the two most significant items, two more significant areas in terms of sales volume. Well, in 2006, that would be Greater Manchester and West Yorkshire. So I might go in here and go, okay, I want to see just Greater Manchester and West Yorkshire. And that would be sufficient, except it’s not always Greater Manchester and West Yorkshire. Here in 2009, the top two items are Greater Manchester and the West Midlands.

So then I would have to go back into here and individual level filters and say, go to Manchester and West Midlands instead. So what I’m going to do is say I want the top two items. I can also say I want the bottom two items. Confusingly, you also click on top N to get the bottom two items as well. But I need to say, well, how do you say what the top and bottom two items are? If you want to be in alphabetical order, you can say, I want the top two items by region name and that gives me the top two items in reverse alphabetical order. I have to say the bottom two items to get the very first ones. So the reason for this is because if you’re doing comparators, then the letter W is greater than the letter G. The further long in the alphabet, the higher it is. But generally you wouldn’t filter based on that. You would filter based on, say, the sales volume or the average sales price. So I’m going to say I want the top two items based on the sales volume. So I’m going to get sales volume and drag that in instead. And you can see that overall, the biggest sales volume is Greater Manchester and West Midlands. So I’m going to click on 2006 in this visualization and you can see that the greater sales volume in the top two is Greater Manchester and West Yorkshire.

So let’s have a look, see whether that is right. 2006, Greater Manchester and West Yorkshire on the top. Let’s see what happens if I was to click on 2009. Now, the top two is not West Yorkshire but West Midlands. So you can see that saying I want the top N allows us not to say, I just want these two, which are the top overall or the top ones that I decided at the top. But they allow context to be driven. So I can say I want it to be the bottom two items based on whatever year or overall that I’m clicking on. So we got South Yorkshire and Thailand weir but then it becomes merseyside and tiny. Weir so it is quite useful to be able to say, I don’t want very determinative items, I want it to be based on the context of other things. Now, we can only use that in a visual level photo.

I can’t drag sales volume into the paid level voter and say top n or the regional level voter and say top n. And really the reason for that is that it’s all based on context. But if I was to say I want the entire page to be the top two by sales volume, then this particular visualization would be affected. And then clicking on that wouldn’t really affect this one as much, because in terms of the top two, because this will only contain the top two overall, so it can get a bit circular argument.

A PageLEVEL photo would affect this visualization, but want this visualization to affect this visualization and it wouldn’t, because it wouldn’t have all of the data. So top n photos, very useful, only can be used at an individual visual level and it just restricts the amount of information so that it’s just highlighting what’s important in terms of the highest number, the lowest number, and then leaves the rest for other visualizations if needed. You.