Filtering allows the user to filter data and items in queries using values. Use filtering to filter hierarchies by a variety of different formulations like top or bottom count or percentage, according to the values (measures) or text (labels) in the current query.
Quick filtering (covered in this topic) offers a context-driven technique for settings and applying a filter through a point-and-click menu option. The Filter wizard dialog, on the other hand, offers more options to create one or more value filters with greater control over the filtering logic and mechanics. It is also the venue to edit and refine quick filters created through query interactions.
Value Filters vs Slicers
Value filters should not be confused with slicer based filters. Slicers attempt to filter a query using element selections like "USA" to show US states; while value filters attempt to filter the data based on numerical or text based limits like "Filter above $500" or "Filter all states starting with 'New' ".
- Click here for a deeper explanation of value filters vs filtering with slicers.
The filter type determines the overall filtering approach to be used on the targeted hierarchy or combination of hierarchies in a drop zone. Use the Filter Wizard to access more filter types and or more advanced settings for the basic types offered in Quick Filtering.
- Counts - Takes the top or bottom "n" elements from a sorted list of elements based on numerical values.
- Percent - Takes the top or bottom elements that are cumulatively larger or smaller than the provided percent figure of the result set.
- Limit - Takes the elements that are above, below, equal to, or in between the provided figures of the result set.
- Statistic - Takes the elements that are above or below average or standard deviation statistics based on values in the result set.
- Sum - Takes the top or bottom elements that are cumulatively larger or smaller than the provided figure of the result set.
- Label - Takes elements that have captions that match the text filtering criteria specified (this does not work on metric elements directly).
Quick Filters are driven by the interactive clicks of the end user - so they take the context of the click into account when determining the 'ingredients' of the filter algorithm.
Right Click Context Menu
To filter grids,right click on the column or row header to choose the Quick Filter option and the type sub-options.
To filter charts, right click on an element label in the legend panel to filter the x-axis.If you have multiple hierarchies in the chart, you can right click on the x-axis background to filter them.
Once you right click, the context menu will open - hover the cursor over 'Quick Filter'. Using this method, you can apply separate filters to multiple hierarchies in the query. If you want to apply one filter to all the hierarchies in the query, right click on a Measure header (instead of a hierarchy header), when working with grids
From specific hierarchies (blue chips): Right click on a blue chip from the drop zones to specifically filter the chosen hierarchy
Across one or more hierarchies in Drop Zone: click on the drop zones menu to filter across all the hierarchies currently in the drop zone.
Hard Filters vs Soft Filters
Some of the quick filters offer hard values to drive their logic, this includes options like "top 5", "bottom 10%" and even "above average". However, many offer a "soft" choice - which means the user will be prompted to enter in a value to drive the filter. The prompt is driven by "parameterization", which an input item that is automatically added to the canvas as part of the quick filter. The user is expected to put values into the input before the filter becomes truly functional.(See the example below).
Note: All soft quick filters include the text "@" in their menu title.
You can review and edit quick filter selections from the Filter Wizard:
- To open the filter wizard:
- Click the Filter button from the Query ribbon, or
- Click the context menu of the filter panel on the canvas, and select edit
- To delete quick filters, click Remove Filters from the Filter drop down in the Query ribbon, or select remove from the filter's context menu on the canvas
Right click on the header of the column you want to filter. In this example, we'll choose cos / incumbents.
Navigate down to Quick Filter, then across to choose "Show Top > 5", to produce a listing of the top 5 'color's based on incumbent cost values.
The resulting grid shows the outcome and the definition of the filter is also added on to the report canvas on the side.
Using the same grid above, right click on the row elements, and choose filter by label, the "starts with @"
The canvas will suddenly change, and show a new text box, waiting for your input.
Type a search string into the box (in this case "b"), and hit enter on your keyboard. This will trigger the filtering of this hierarchy to show all those colors starting with the letter b.