Smart Filtering for Pivot Tables

Not all measures in an SSAS cube or PowerPivot cut all the hierarchies and vice-versa. This is best explained by example. Let’s use the Adventure Works sample cube and Microsoft Excel 2010.

Start with a pivot table that only has Geography in the rows. In the Adventure Works cube the “Internet” measures don’t cut Geography, so if we were to add a measure from the Internet Customers measure group, e.g., Customer Count, we’d see a peculiar result:

Indeed, we see 18,484 for all of the values for all of the countries. Is that accurate? No, that is the value for the total of all countries, but there is no breakdown by countries in the cube. By default this duplication of values is what you’ll see when you have a measure that doesn’t cut a hierarchy in a pivot table.

Looking at any given hierarchy/measure it’s not too difficult to determine what measures/hierarchies cut it. But as you add additional fields into the pivot table, determining which fields cut those that are in the pivot table becomes increasingly difficult.

Vivid’s SmartFilter fixes this issue with a simple solution. If you have the SmartFilter enabled the pivot table editor will only display those measures which cut all of the hierarchies in the pivot table, and only those hierarchies that cut all of the measures in the pivot table.

Here's an example of two pivot table editors side by side. The one of the left has SmartFilter disabled while the one on the right has it enabled. Notice that the pivot table on the right has many measure groups and dimensions hidden as they don't cut all of the measures/hierarchies in the current pivot table. It greatly reduces the complexity and clutter associated with large cubes.

SmartFilter Computation

For determining which measures remain visible, Vivid takes all of the hierarchies that are in the row or column of the pivot table (page filters and slicers are not used for computing SmartFilter function) and places the measure groups that cut each of them into a set for each hierarchy. Thus if there are n hierarchies in the rows/columns then there are n sets of measure groups. Vivid applies set intersection to the n sets which results in a single set that is the set of measures that will be visible in the pivot table editor.

The same rules apply to displaying the hierarchies in the pivot table field list.