Microsoft Office Tutorials and References
In Depth Information
FIguRE 8-30 The merged table shows information coming from both OLAP and Excel.
Once again, the real power of PowerPivot remains in the capability to integrate data coming
from different sources. As simple as it is, the Paste Append option lets you build complex
Let us continue our coverage of advanced Excel features by analyzing another useful option
of Excel 2010—the ability to define sets. Like many of the other functions described in this
chapter, this feature is tied not only to PowerPivot but is an Excel feature that works on any
PivotTable type. Before we go on speaking about how sets work, let us explain what a set is.
Intuitively, a set is a list of items, where item might be anything we would like to think about. You
can define a set by enumerating the items in it, as you normally do with your shopping list, where
each item is listed separately. Or you can define a set by giving a description of what belongs to
it, such as the set of all countries in North America. Both are good definitions of a set.
Nevertheless, because this is a PowerPivot topic, we need to understand the meaning that
PowerPivot gives to a set. A PivotTable is made up of cells, and each cell is defined by the
filter context that determines which rows to take from the underlying PowerPivot tables to
compute its value.
So you might think that each cell defines a filter context. The contrary is true as well: a filter
context defines a cell. Moreover, if you move a step forward, you might think of a filter context
as something that defines the set of rows that need to be accessed so that you can compute
the formula that gives a value to a cell.