BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Power View is an add-in included within Excel 2013 and later versions. This tutorial shows how to create Power View reports based on Analysis Services tabular models.
- Overview - what is Power View?
- Creating your first Power View report
- Creating and formatting basic visualisations (tables)
- Text boxes and images in Power View
- Filtering reports using tiles, slicers and filters (this blog)
- Matrices and cards in Power View
- Charts in Power View
Posted by Andy Brown on 08 December 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Filtering reports using tiles, slicers and filters
Power View provides three main ways to filter the data included in the visualisations for any report, with scope as follows:
|Tiles||Tiles only control visualisations contained within them.|
|Slicers||Slicers control every visualisation on a report.|
|Filters||Filters control either a single visualisation or everything on a report, depending on what you had selected when you created them.|
Let's look at each of these in turn, starting with tiles.
Tiles are - confusingly - the equivalent of pivot table slicers:
Here we're looking just at data for the North quadrant, using a tile.
There are two ways to create tiles like this. The first is to start with a visualisation the first column of which is the field you want to tile by:
Click on the Tiles icon on the DESIGN tab of the ribbon with a visualisation selected.
Power View will take the first column of your visualisation, and turn it into the field to tile by. You can achieve the same effect by using the fields window on the right:
Drag the field that you want to tile by to the TILE BY section of this panel.
Note that tiles have two looks (although Tile Flow looks weird):
Tile Flow puts the tiles underneath the visualisation, not above it.
The best thing about tiles is that they can control multiple visualisations:
Here we're showing two separate tables for the North quadrant.
Drag fields into a blank space in your tiles to create additional visualisations controlled by them.
Finally, you can remove tiles in the same way as you remove fields:
Click on the drop arrow next to a field you're tiling by to stop doing so.
Slicers in Power View aren't really like slicers in Excel (for one thing, they don't look nearly as good):
Using a slicer to show only data for regions in the North quadrant.
To create a slicer like this, tick a field to create a table based upon it:
Click on a blank part of your report, then click on the field you want to slice by (here Quadrant). Power View will create a table showing all possible values for this field.
With this table selected, choose the Slicer option on the DESIGN tab of the ribbon:
Click on this tool to convert your table into a slicer.
You can now choose for which combination of quadrants you want to view data:
Here we're looking at all possible quadrants.
When you've had enough of your slicer (it may not take long!), click in it and press Delete to get rid of it (this seems to be almost the only way to remove a slicer).
Is it just me, or are slicers unintuitive to use? Give me tiles over slicers any day of the week ...
Filters for a report
The third way to filter data is to add a filter, which you can do either at visualisation level or at report level:
If you add filters to the VIEW tab, they'll affect everything on this report; if you add them to the TABLE tab, they'll just affect the selected table. Here we've set a filter for the whole view, so that we're only showing data for factory outlets for both the table and the chart.
You can filter by a field by dragging it onto the Filters panel:
Drag a field from the field list to the filters section of the view to filter by it.
Once you have a field to filter by, you can choose from up to 3 different ways to set filters, all of which are pretty self-explanatory:
Click on this tiny icon to change the filtering mode.
For the text field shown above, only two of the modes will show up - which is a shame as the third one is the most fun to use:
For numeric fields, you can click and drag to change a range (here we're showing sales for shopping centres having between 49.506 and 94,802 square metres!).
Hopefully with slicers, tiles, view filters and filters specific to individual tables or charts I've given you enough to work on to filter your data!
So far in this blog I've looked almost exclusively at tables; it's time now to widen our horizons, and have a look at matrices, followed in close succession by charts.