Microsoft Office Tutorials and References
In Depth Information
■ Now PowerPivot can proceed with the evaluation of the COUNT of PriceList[Freight],
and in accordance with all we said before, it counts only one row. In other words, the
filter context applied to the PriceList is composed by these elements:
■ Country = Australia. This value comes from the PivotTable coordinates (which
filtered Orders), then it has been propagated to the CountryWeight table due
to the first relationship and then again to PriceList due to the relationship with
■ Weight = <the weight of the specific order>. This value comes from the Orders
table, which has selected only one row due to the behavior of the SUMX function.
That row has been then propagated to the CountryWeight table and from there,
following the same relationship as before, to PriceList.
■ Courier = BlueYonder. This filter is coming directly from the PivotTable coordinates
of the cell being evaluated.
■ So the count of different Freight values in the PriceList table yields only one because,
after the filter, the PriceList table contains only one row. The value of the freight for that
order and courier is returned by the function.
■ This process is repeated once for each row in the Orders table, summing up all the results
until the table is completely scanned. At this point, SUMX returns the final value.
As you can see, the complete evaluation algorithm is pretty complex, but is it based on very
simple steps that, mixed together, yield the correct evaluation of the cell.
Mastering all these steps takes time and experience but it lets you build very interesting
data models that provide complex simulation tables. We strongly suggest that you follow the
complete low of evaluation until you understand it well because it will transform this data
model from something “magically working” into a clear view of the internals of PowerPivot.
In this chapter, you saw how to create complex models that let you solve many problems with
PowerPivot. We used complex data models, such as many-to-many relationships, and complex
DAX formulas, which use CALCULATE, VALUES, EARLIER, error handling, and most of the
functions available in PowerPivot.
The final result, when used with the PivotTable, is easy and hides the technical complexity
of the data model. As a PowerPivot expert, you have learned how to create complex models
that are easy to use.