Microsoft Office Tutorials and References
In Depth Information
In this PivotTable, we added a couple of slicers to filter for product category and product color.
Nevertheless, if you select, for example, the blue color, the PivotTable does not compute the
top 10 blue products; it instead filters the top 10 products showing only the blue ones, as you
can see in Figure 8-39.
FIguRE 8-39 When a color is selected, the top 10 product set is filtered and not recomputed.
The reason for this behavior is that by default the set, even if defined with MDX, is still a
static one. Even if you do not know in advance its content and used MDX to define it, the
set is evaluated once during the first query after data is refreshed in the PowerPivot
workbook. So it is not recomputed for each query. When you apply a filter, that filter is applied
to the set too, reducing its content.
You can convert a standard set to a dynamic one using the Recalculate Set With Every Update
check box in the MDX Set Editor window. If you check this box, the set is marked as dynamic
and is computed for each query, reflecting the filtering made on the PivotTable.
In Figure 8-40, you can see the same query with the blue color selected, but this time the
set is marked as dynamic. Its behavior is different: the set now contains the 10 top-selling
products, which are blue.
FIguRE 8-40 When a color is selected in the top-10-products dynamic set, the set is correctly recomputed.