Power View provides different ways to filter and highlight data in reports. Since the underlying data model contains metadata, Power View knows the relationships between the various tables and fields in a report, so one visualization can be used to filter all the other visualizations. Chart highlighting, slicers, and filters are three of the ways to filter all the visualizations in a view in Power View (SharePoint) or all the visualizations in a sheet in Power View (Excel). You can choose to create filters for the whole view or sheet or for individual visualizations. None of these filters apply across the whole report: in Power View in SharePoint, they are specific to individual views in the report. In Power View in Excel, they are specific to individual sheets in the report. All of the filters allow you to select one or more values.
Charts can act as filters via interactive cross-filtering. When you select a value by clicking directly on the chart, it will filter the values in all the tables, titles, and bubble charts in the view or sheet based on the value you clicked on. It also highlights parts of the charts that pertain to the value, showing the impact of the filtered values on the original values. You can select multiple values by using Ctrl + left-click. To clear a filter, click inside the background of a report item, not on a value. Interactive cross-filtering selections are saved when you move from one view or sheet to another, but are not saved with the report.
In the following example, the Professional value is selected by clicking on it in the pie chart legend. This action results in the bar chart display the SalesAmount value for only the selected EnglishOccupation in a dark color, with the overall values in a lighter shade. Values in the two tables are also filtered by the selected ProfessionalEnglishOccupation.
Interactive cross-filtering
Slicers are a type of filter and are identical to the slicers you find in Excel. They filter everything in the view or sheet. To build one, you first create a single-column table from any attribute field, select the table, and click on the Slicer button. Each value in the table becomes a button, and when you click on the button, the data is filtered in the report immediately. You select multiple values by holding the Ctrl key when you click on each button. There is a Clear Filter icon at the top-right of each slicer that resets the filter, resulting in all values being selected. You can then click on any value to deselect it.
Note that clicking on a value in a table that is not a slicer does not filter the report. You can add multiple slicers to your report (see the Promotion Dashboard screenshot in the Getting started section). Be aware that slicers filter each other. So, for example, in the Promotion Dashboard screenshot, we have a slicer for FiscalYear, Gender, and SalesTerritoryRegion. When you click on a fiscal year, it filters the other two slicers to show only the particular gender and sales territory regions for that fiscal year. The result is the filtering effects of all slicers are combined.
Slicers are different than chart highlighting in the following three ways:
- They filter charts rather than highlighting charts
- They are saved with the report, so when that same report is reopened, the same values in the slicer will be selected
- They allow for the use of image fields as the slicer, in which case the Row Identifier field for that image will be used as the filter in the slicer
In the following screenshot, the product category slicer on the left-hand side is filtered by bikes, so only the bikes in the product subcategory slicer on the right-hand side are displayed. In effect, the slicer on the left-hand side filters the values in the slicer on the right.
Slicers
Power View has basic and advanced filters that can be applied to an individual visualization or to the whole view or sheet, but not the entire report. There is a specific Filters Area for these filters that are saved with the report. If you set a filter in the Filters Area, it will continue to filter the report even if you close the Filters Area. Note that you cannot filter an image field.
A view-level or sheet-level filter will filter all the visualizations as well as all the slicers in the view or sheet. To see the Filters Area, on the HOME tab, click on Filters in Power View in SharePoint or Filters Area in Power View in Excel. You can also click on the Show Filters icon in the top-right corner on any visualization. To add fields to the Filters Area, drag the fields over from the fields section of the fields list or click on the down arrow to the right-hand side of a field in the field section of the fields list and click on Add to View Filter.
You can create a filter that will be applied to only one visualization. This visualization can be a table, matrix, card, or chart, but not a slicer or tile container. If you wish to view the filters on a visualization, click on the Show Filters icon in the top-right corner of the visualization. The fields in the visualization will then be displayed in the Filters area so that you able to filter them. You can drag other fields from the fields list to the Filters area; this can be done even for fields that are not part of that visualization. Alternatively, you can click on the down arrow to the right-hand side of a field in the field section of the fields list and click on Add to <visualization type> Filter.
If you expand the Filters area, when you select a visualization, you will see the visualization type in a gray heading to the right-hand side of the word VIEW in the Filters area. For example, if you select a chart, you will see a VIEW heading and to its right-hand side, a CHART heading in the Filters area (see the following screenshot). Click on the VIEW heading to see the view-level/sheet-level filters and click on the CHART heading to see the filters for the selected chart.
Filters area
Any filter that you add to a visualization can be deleted using the Delete filter icon, located to the right-hand side of the filter name (see the previous screenshot). However, you cannot delete the filters for any field that are in the visualization. Rather, you can clear them so that they have no effect. Note that if you add a filter for a field that is in the visualization and later delete that field from the visualization, the filter will remain in effect for that visualization. To remove the filter, you will need to go to the Filters area for that particular visualization and delete the filter.
In the Filters area, by default, you will use basic filters. Click on the right-arrow to the left of any filter name to expand the filter (see the following screenshot). For non-numeric basic filters, there will be checkboxes for each value that you can select and deselect. There will be a number after each value that indicates how many records have that value. For numeric basic filters, there will be a slider that you can drag to select the values between the markers.
Expanded filter
You can use advanced filters by selecting the Advanced filter mode icon to the right-hand side of each field filter. This will open up an advanced filter dialog as shown in the following screenshot:
Advanced filter
With this option you can apply multiple filters using And/Or. Each filter can be a range of values or freeform values. You can enter partial values to be included or excluded. There is a drop-down list of phrases to choose from as shown in the following screenshot:
Advanced filter phrases
When you expand a filter, you will see a search box (see the earlier Expanded filter screenshot). This will allow you to search for a value in a text field within a visualization or view level (it does not search the whole report). You can then choose whether to filter on it. The portion of a field's value that matches the search text is highlighted in yellow in the search result list. You can use wildcard characters in the search box: a question mark (?) will match any single character and an asterisk (*) will match any sequence of characters. Use the tilde (~) before the question mark or asterisk if you actually want to find a question mark or asterisk. A search is not case-sensitive.
For example, in a list of colors, typing l*e in the search box returns three colors: blue, silver, and silver/black.