Microsoft Office Tutorials and References
In Depth Information
Let us take, as an example, the cell for <Australia, CY 2001, BlueYonder>, the value of which is
8,248.00. How did PowerPivot reach this result?
■ The filter context of execution is given by the coordinates of the cell—that is,
<Australia, CY 2001, BlueYonder>.
■ The value required is CalculatedOrderFreight, which is the last measure defined in this
section. It contains a SUMX over Orders for the complex expression that evaluates the
freight cost for the courier.
■ Because the formula is a SUMX, PowerPivot iterates over all the orders, satisfying the
filter context, and for each row in the Orders table, it evaluates the expression provided
in the second parameter. The orders taken into account are orders in Australia and for
the CY 2001, the only part of the filter context that actively filters the Orders table.
■ During the evaluation of the expression, in the inner loop of SUMX, the evaluation
context changes. It adds a row context (the iterated order in the Orders table) to the
original filter context imposed by the PivotTable.
■ Now, because the next part of the expression is CALCULATE, the row context in the
Orders table is transformed into a filter context (propagating the selected row to all
the related rows in other tables) that is merged with the existing one, creating a new
filter context that is composed by the intersection of both. Under this new filter
context, PowerPivot initiates the evaluation of the inner IF.
■ The PriceList table is not in direct relationship with the Orders table, so it seems that
it will not be filtered. Nevertheless, before going on, PowerPivot needs to apply the
complete filter context to all the tables, and here comes into play the CountryWeight
■ The Orders table contains the Freight column, and it is in relationship with the bridge
table CountryWeight. Because we are on a specific row, the Freight value of the row
has been added to the filter, and so PowerPivot behaves as if the CountryWeight table
contains only one value, defined by the country of the order (which happens to be
Australia because of the filter context) and the weight of the order. (Whatever value it
has, it is defined by the row context transformed into a filter context.)
■ The relationship between PriceList and CountryWeight (which, filtered, contains only
one row) forces the PriceList table to consider only two rows, one for each courier.
Moreover, because the filter context contains <BlueYonder>, only one of the two rows
survives the complete filter, so PowerPivot behaves as if the PriceList table contained
only one row.