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

5. Slicer

Now there is one of a type of filter and it’s not strictly speaking of filter, it’s a visualization. And this visualization is called a slicer. And what it does is it goes through the slices data, which affects all visualizations on a particular page or in a particular report. So what I’m going to do is I’m just going to delete a few of these items and I’m going to insert a slicer in the spare gap. So it’s the one just to the left of the table with a little filter symbol on it.

So just click there and I add what I want to be potentially sliced or filtered. Well, what I’m going to do is I’m going to add the date. Now, notice the difference between these two, at least at the beginning. Here we have a list of years and the sales volume. Here we have absolutely no measures. It is just a list of the fields. And this particular list is being shown in a style which allows for visual manipulation as well as entering the dates. So let’s see what happens when I go to the slider and I move this across. You’ll see that all of the visualizations change and you’ll also see that overall the bottom two also change as well that we set up in the previous video.

It used to be just South Yorkshire, Tiny and Weir and now it is Melodyside and Tiny wear. Similarly, I can change the end date and you can see everything changing. We can enter the dates direct, so we have a calendar picker. Wherever you get to enter dates, you usually have the option of a calendar picker. So I’m going to say the first of the first 2000 to the December 31, 2009. Now all of these can be shown or not shown based on the formatting. So here we have the slider, so we can turn that off or on. Here we have the date inputs. We can’t turn those off or on, but we can change the size and the four color and the back color. For instance, we can add a header. There already is a header, but we can change attributes about it, including outline.

So whether we’re going to have a box around the title or not going to add a title to this. So filter by dates, there we are and going to middle align it again, center of it. And actually now I’m going to have it blank with a white background as before. So this affects everything that is in this particular page. Now similarly, I could have a filter or slicer, that is to say based on something else. So if I have a slicer, I don’t want to change that to slicer, I need to deselect it first. Add slicer. I’m going to have a slicer based on the region name. So here we have a series of tick boxes.

And notice here I’m just being allowed to select one particular item or all of the items you’ll see that I can add a selector and I can turn off the single select or I can select multiple items. But by default, this particular slicer, which I’ll just blow up a bit, I have to go down to the items and increase the font size. This particular slicer, by default for text only allows you to put one particular item. So if I can do this for individual items or do it for a range of dates, can I do it for measures as well? So let’s put in into a new slicer the sum of sales volume.

And indeed you can see, yes, we can operating fundamentally like the date. So we have numeric inputs as opposed to the date inputs in the formatting that we had previously, but everything else is the same. Now, there’s just one thing about this particular slices. I’ve arranged this so that this is just for 2000 to 2009. Let’s have a look at the matrix. The matrix continues from 1995 to 2016.

This slicer is not affecting anything other than this one page. Now, this following feature I’m about to show you has actually been added moderately recently, certainly not right at the beginning, and allows for slices to affect a single page or multiple pages. Now, you’d think it would be somewhere in the visualization so it’d be somewhere in the format of the visual tools. It’s actually in view and it says sync slices. So that creates a new page pane. I mean, and here we have what is this going to actually represent? What is this going to change? Is this going to change the visibility or is it going to change the synchronization of it? Is it going to do in other words, are you going to see it and are you going to be able to sync? So the advantage of being able to see it is if I check matrix and go to the matrix, you will see that the same filter, the same slicer is being shown.

So it’s not a different slice, it’s not two independent slices. If I change this to 2001, you’ll see that the slicer on this page hasn’t changed. So why is that? Because this slicer, whilst affecting this page, is not connected to the rest of the slices. So if you want this slicer to be the same as this slicer, then you need to check this box here for the page that it’s on and sync. So now let’s change this. So it’s 2002 and you’ll see that this slicer now is 2002. So it’s a very odd quirk that when you introduce a slicer, it is visible on the page, but it will only by default sync to a particular page and not when you have the sync to additional pages will it be the same slicer. It’s very confusing.

So if I were you, when I insert a new slicer, so I’ll just insert another date slicer just here and go to sync Slices. The first thing I will do is check this box which is this particular page, the page that it’s on and whether it’s syncing because the rest of it doesn’t really make sense. Now, you can have a slicer filtering on the page, in other words, it will do the slicing but you can’t see it. So I suppose this slicer filters this bar chart. So I’ll just go back up so you can see that this bar chart goes from 1995 all the way to 2015. So if this slicer affects the bar chart, it can do that. So tailor to 2000. We’ll now see that this starts at 2000 but you can’t see the slicer. So something on page one affects the bar chart and you can’t see what it is. So this causes a problem. Of course, I’m just here, I’m looking at this bar chart, I see that it is filtered because I know my data range starts at 1995.

OK, why is it being filtered? Okay, I’ll go into my visualizations, I will go to my filtering and there’s no filters whatsoever. What’s going wrong? So with that, I then have to look at the remaining of the pages and see if there is a slicer which is synchronizing to that particular page. So it might be you want a slice of synchronizers to all pages. Great. So just check check jobs a good one. I’ll just insert a new page, go back to the slicer and you see it’s not selected.

So any new pages you add, you have to go back to the initial slicer and go check if you want that slicer to be synchronized to this new page. So I’ll just get rid of that page by clicking on the X next to it. So slices a very simple tool but something that can have big impact across not just this one page but across all of the pages. And the key key thing to remember once you’ve inserted the slice so it’s to go to view sync slice. So it’s a bit out of the way. So you have to remember that it is there.

6. Slicer Warning

Now I do have one word of warning regarding slices. Let’s add a slicer. So we add a slicer based on the date. I will use my view slip sync slices and I will synchronize the matrix page to it as well. I will take this up to 2002 and we can see that the matrix is so affected. I’m now going to delete the slicer. So you can see this slicer here, 19, this visualization, 1995 to 2015, 1995 to 2015 or 2016 even go to the matrix. It starts at 2002. So the slicer which has been deleted is still affecting this matrix visualization. And you can’t see any photos in the page level photo, the report level photo.

So really it is a very dangerous thing to do to have a slicer which is synchronized and yet you then delete. So if you find yourself in that sort of situation, you can always press undo several times if you can. Or you can insert another slicer which affects the matrix. So this one should affect there we go, we’re now up to 90, 97 and reset it that way. But just a word of warning before deleting any slices, make sure that they are not affecting any other pages or you might find yourself with an unexpected filter that you can’t seem to get rid of.

So how can we get rid of the effects of a slicer that we can’t see? The solution is to see it and to do that we go into view and selection. So this shows all of the objects which are on this page. Currently, you can see two of them. However, when I click Selection, there are actually three. We have the matrix. So that is this, which you can see here, filter by days. This is the slicer that we can see and then we have a further slicer. Clicking on that doesn’t actually highlight it because it is invisible.

And you can see it’s invisible because we got these little eye icons which when you click on it, you can hide it. So what we need to do with this slicer that we can’t see is unhide it. So click here and now we can see the slicer. We can adjust just the slicer so that we now start with 1995 and then we can delete the slicer. So if you’ve got a slicer that you’ve previously set up, synchronized to more than one page and then deleted the slicer to be able to see it again. To delete it, you need to go to view selection.

7. 8g. Sort visuals

Now, in this video, we’re going to have a look at sorting visualizations. So, going to have a look, first of all of this table visualization. Now, currently you can see it is being sorted by the region name. In fact, there is a filter on it which might be helpful if I just click on the eraser and remove. So it’s been sorted on region name descending. So that means letters. Anything starting with Z or Z is right at the top and A is right at the bottom. If I wish to change the sort order, then I can do so simply by clicking on it. Similarly, if I want to sort by a different column, I can do that by clicking on that column. Now, if I click on the dot, dot, dot, the more options, which is just outside the visualization, you’ll see that there is a sort descending, sort ascending and then a sort by a particular field.

So if you didn’t want to click on any particular column, you can say, I want to sort by sales volume descending. If we go to the matrix where we got the years going down and going to Manchester and the other regions going across, again, we can sort. But you can’t just click on the individual region that uses the spotlight. So here you will have to use the more options to sort by the year or sort by the sales volume. And you can see it’s only the overall sales volume that can be sorted. You can’t sort of a particular column. There is no sort by the West Yorkshire sales, unfortunately. Now, what if you wanted to do a bit more of a custom sort?

So, for instance, let’s add a new table and I’m going to show in there the region name, but I’m also going to show the area cord name. So it’s a bit small, so I’ll just expand it by going to the bottom of the grid and I’ll expand it to there. Now, you will see that the region name is not in identical order of the area chord, but maybe you want it to be. Maybe the area called order is important and it should go greater Manchester. Merseyside. South Yorkshire. West Midlands west Yorkshire.

Tiny. And we are but there doesn’t seem to be a way to just drag, say, Merseyside up or West Yorkshire down or something. So let’s say this was important. I wanted the region name to be in exactly the same order as the area chords. Well, what I can do is I can click on region name in the modeling section or column tools. We have sort by column enabled. So what this allows us to do is to sort a particular column based on a second column. So I want region name to be sorted in the same order as area code. So now look what happens. Greater manchester, west midlands, west yorkshire, merseyside.

South yorkshire and thailand. Weir that’s not the right order, it’s currently not being sorted. You can see the little arrow next to Sales volume. And if I click on the dot, a bit tricky to do, you’ll see that there are little yellow signs next to sort descending and sales order, sales Volume. So that’s telling you what is being sorted. But now if I sort by region name ascending, you will see that tiny weir is now at the bottom, exactly the same as in the area cord. If I sorted by area code, tiny weir would be right at the bottom. Now, that sorting by column, that is not a one off. That affects all visualizations on all pages which use this particular data source. So use that with some care.

A good example of this is suppose you had a column which had the month name january, February, March, April. If you tried sorting that in order, you’d get April 1 and then August alphabetical order. Instead you can say, I want to sort that in association with a month number. We’ll show you how to do that much later when we get into modeling. Now, let’s all look at the sorting for this graph. So I click on the dot, dot, dot next to it and oh, absolutely no sorting. So why is that? Well, just enter focus mode and the reason for that is because this x category is being sorted continuously. It’s being shown continuously, so it’s going from 194, which is blank, all the way to 2016. And the consequence of that is that there really is no sensible way to override that sorting. However, if we change this from a type continuous to a type categorical, so taking each of these years as a separate category, in the same sense as car, lorry, van would be different. And now we can sort by a particular thing. So we can sort by year ascending, year descending as is here, or we can sort by something else, sort by sales volume. So you can see 2006 is the highest sales volume and in 2003, 2004, all the way down to 2009.

So if you want to be able to sort the x axis of a chart to be something different, then you better change the x axis from continuous, which is its own sort and can’t really be overridden to categorical. So if I change that back to continuous, you can see we have lost the sorting, change it to categorical and we’ve got it back again. So that is sorting.

Sorting allows us to present information with the most important at the top. Maybe it needn’t be by the first column, it could be by sales volume, it needed in alphabetical order, it could be based on another column like area code, and you can also change the x axis of charts to be based on categories and then you can sort the charts in a non continuous format. Now I’m just going to change all of that back because I think I would prefer to have that as continuous and I’m also going to change the region name so that it is sorted by region name, which is the default again. But it’s nice to know that you can change it as and when you need it.

8. 8j. Use Bookmarks for reports

Right, I’m going to create a storyline, I’m going to do a presentation and I’m going to say, first of all, this is the overall picture from 90 95 to 2016. Then I’m going to look at 2000 to 2009 and I’m going to just show the top two regions so we can fork us on them. Then I’m going to talk about 2006 and then 2009. The problem with all of this is that in a presentation you could sort of say, okay, I’m going to change this to 2000, I’m going to change this to 2009. But then changing this so that it’s the top two and then clicking on it. It’s not really a very presentable way of doing things. What you can do instead is set up your display exactly how you want and then create a bookmark. BOOKMARKS allow you to save which page you’re on, any photos, slices, including what the values are, sorts, drills, locations, whether things are being shown of the visibility and the focus or spotlight of any particular visible object. So, let’s start off at the beginning. I want this to be my first bookmark.

So I’m going to go to View and bookmark Panes. So I want to add my current state of what I’m seeing as my first bookmark. So I’ll click Add and I’m going to rename that overall. Next, I’m going to change this so that it is the year 2000 to the year 2009. Make it the 31 December. And I’m going to change this so that it is filtering on the top two. So I’ll go into the visual photos and say that the region. Name the top two values according to the sales volume.

Apply filter. So this is going to be my next. So I’m going to let say 2000 to 2009 with top two. And then I’m going to look at 2004. So this is a highlighter. So I’m going to click this and say rename 2004. And then I’m going to compare and contrast that with 2009 or 2000. Then 2006 has got West Yorkshire is one of the top two. 2008 has got West Midlands. So I want something with West Midlands. I’ll use 2008. So I will add this as a view. So now to get in between these views, all I have to do is click on a bookmark and it instantly takes me to where I was at the time of creating the bookmark.

Now, maybe my BOOKMARKS, I don’t want it to affect absolutely everything. So I can click on the dot dot and say, what are you going to affect? Are you going to affect data properties like photos and slices and sort states? Are you going to affect display properties such as the spotlighting and any visibility of objects and which page is visible? And also, is it all visuals or is it just selected visuals? Now, let’s say I also wanted to not show the slicer or the instructions. Once I get past overall. So I get into BOOKMARKS. So now I’m going to select the selection pane. And here I’m going to say, okay, these instructions, I want to hide this visual. So just click on each one if you’re not sure where things are. So here’s the shape, here’s the filter. I’ll keep the filter.

So I’m going to therefore say that this is going to be my new 2000 to 2009. So I’m going to update this bookmark. So now here’s my overall with all of this shown. And then here’s my bookmark. And you see the objects that have asked to not be shown. They’ve got a little icon next. When that is shown, then those particular shapes are not going to be displayed. So filter by dates is displayed, but here they’re not going to be displayed. And finally, these pre BOOKMARKS, these are Yearly Views. So I’m going to select them all. I’m holding down control and clicking each one.

Click the dot dot and I’m going to group them together. So this group is going to be Yearly Views. And with this I can have a lot of BOOKMARKS that are hidden away by just clicking on this arrow. So if I need them, they’re there, if I don’t, I can just hide them away. So hold this shows the power of BOOKMARKS, especially when used in conjunction with the selection pane. So you can go to a particular layout, particular filter, particular objects being shown, and then very swiftly move to another one and make your presentation a lot easier to do.