Filters aren't applied to related tables in PowerPivot unless a measure is added to the PowerPivot Workbook

  • Article

A colleague, with whom I work on a regular basis, recently pointed out a somewhat unexpected feature of PowerPivot PivotTables. What my colleague was seeing was that when creating a PowerPivot PivotTable, placing a column from one of the Dimension tables on rows then adding a column from a related (Parent) table in one of the slicers and selecting a specific member from the slicer the action did not apply a filter to the related dimension that had been placed on rows. When a measure was added to the PivotTable, however, the dimension that was on rows the filter was magically applied. This behavior is relatively easy to reproduce using a few tables from the Adventure Works relational database. So for those who want a reason to just play with PowerPivot (take your pick of the version), here are some relatively simple steps to reproduce the behavior.

Start by creating a new PowerPivot workbook that uses the Adventure  Works DW 2008 (you can also use Adventure Works DW 2008 R2) as the data source. Launch the Import Wizard and importing the following tables:

DimCustomer

DimGeograpy

DimProduct

DimProductCategory

DimProductSubCategory

FactInternetSales

If relationships don't exist between the DimProduct, DimProductCategory, DimProductSubCategory and FactInternetsales tables aren't automatically imported, create them manually.

Create a new PowerPivot PivotTable, then put DimProductCategory.EnglishProductCategoryName in a vertical slicer (you can use a horizontal slicer if you want). Next drop DimProductSubCategory.EnglishProductSubcategoryName on rows. Now for a bit of fun and entertainment, select "Bikes" from the slicer and pay careful attention to what doesn't happen with the EnglishProductSubcategoryName members that are on rows. Now that we're all satisfied that the filter hasn't been applied, add InternetSalesFact.InternetSalesAmount to the values area and note what happens with the DimProductSubCategory.EnglishProductSubcategoryName members that are on rows.

This is designed behavior. Cross filtering in slicers works in relationship to measures and is "turned off" until a measure is added to the PivotTable. So in this case, even though there are relationships between the DimProductCategory, DimProductSubCategory, DimProduct, and FactInternetSales tables, the filter applied to the DimProductCategory.EnglishProductCategoryName has no impact on the DimProductSubCategory.EnglishProductSubCategoryName members on rows until a measure is added to the PivotTable.