Filters and slicers

Available values in multiple slicers cascade (filter each other) from left to right. That is, a value selected in the left-most slicer limits values in subsequent slicers to those with available data.

Cascading slicers generally work intuitively. On occasion, it may be necessary to understand their precise operation, which is explained below.

Cascading can be understood as applying a Remove Empty Rows filter to the set returned by each slicer before it is passed to the next slicer. To decide if a row is empty, one or more measures are examined to see if they are empty (null).

If no measure is present in the analysis or other resource, the slicer examines all the measures in the cube's default measure group.

If one or more measures are present in the analysis or other resource, the slicer creates a list of the measure groups that the measures come from, then examines all the measures in every measure group in the list. Note that dashboard slicers will examine every measure in every measure group in every resource in the dashboard.

For example, an analysis that displays sales of products by customer might have two cascading slicers: Product Name and Month. If Bicycles is chosen in the Product Name slicer, the Month slicer might not display January 2012 in its list because there were no sales of bicycles during this month (in other words, the Remove Empty Rows test found that all measures in the Sales measure group for Bicycles were empty for January 2012).

However, if a new measure, Units Produced, is added to the analysis, January 2012 might now be displayed in the Month slicer's list because some bicycles were produced in January 2012, although none were sold (in other words, the Remove Empty Rows test found that at least one measure in the Units Produced measure group for Bicycles was not empty for January 2012).

The same behavior is seen if the same two cascading slicers, Product Name and Month, are added to the Dashboard Slicers placeholder in a dashboard that contains an analysis with the Sales measure and a second analysis with the Units Produced measure.

Members from multiple levels within a hierarchy can be accessed in drop-down slicers. Members in the different levels are accessed using triangle icons to expand and collapse the levels. This multi-level drop-down is known as a hierarchical slicer.

Hierarchical slicers are created automatically based on the initial query which created the original slicer (as described in Slicing with the Dimension Tree). The initial query needs only to return members at one level, as lower levels can be accessed by expanding members to show their children, as needed. Once the slicer is created, you are immediately allowed to drill down on each member displayed by this initial query.

In the following example, a slicer has been created based on the Calendar Year level from the Calendar hierarchy. Notice that all members within that hierarchy (semesters, quarters, months etc) are immediately available for drill down.

To prevent performance problems if a slicer is configured to display a very large number of members, a slicer limit of 500 is enforced by default.

You can control what is displayed in the hierarchical slicer, limiting the default behavior of allowing access to everything in the slicer's hierarchy. This type of control is useful if you wish to limit the information displayed in the slicer.

Formatted check boxes in Multi-select slicers, known as four-state check boxes, are used to indicate whether or not all the descendant members of a parent member are checked, and whether new cube items that appear as descendants of the parent member are automatically included in slicers when a cube is processed.

The inclusion of items depends on which parent and descendant members are selected in a slicer.

Four states are used to indicate the status of the selection:

To create a dynamic entry in a slicer, first specify the static members, then create a tab on the Slicer placeholder that returns a dynamic member, for example Most Recent Period. More than one function may be added using this method, as shown below.

The resulting entries appear at the bottom of the slicer and can be selected. The entries dynamically revaluate the member function used each time the analysis is displayed.

When selected, the value returned by the function (instead of the member function's name) is displayed in the slicer.

Dashboard slicing can use one or multiple of the various slicing elements to enhance dashboard interaction. Various slicer techniques like cell slicers, global slicers and slicers cells are available.