A tutorial on how to create Power View reports based on SSAS tabular models
Part five of a seven-part series of blogs

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.

  1. Overview - what is Power View?
  2. Creating your first Power View report
  3. Creating and formatting basic visualisations (tables)
  4. Text boxes and images in Power View
  5. Filtering reports using tiles, slicers and filters (this blog)
  6. Matrices and cards in Power View
  7. Charts in Power View

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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:

Method Scope
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

Tiles are - confusingly - the equivalent of pivot table slicers:

Tiling by quadrant

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:

Tiles option with table selected

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:

Tile by option

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

Tile Flow puts the tiles underneath the visualisation, not above it.

 

The best thing about tiles is that they can control multiple visualisations:

Two visualisations, one tile

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:

Stopping tiling

Click on the drop arrow next to a field you're tiling by to stop doing so.

 

Slicers

Slicers in Power View aren't really like slicers in Excel (for one thing, they don't look nearly as good):

Quadrant slicer

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:

Adding a field to a slicer

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:

Converting to a slicer

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:

Quadrant slicer

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:

Filtering by view or table

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:

Filtering by centre type

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:

Filter mode

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:

Changing integer filter

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. 

 

This blog has 0 threads Add post