Microsoft Office Tutorials and References
In Depth Information
Using this feature, you can produce a report that gives you the set of the 10 top-selling
products that changes whenever you change the filtering condition.
Note Please remember that the set is conditioned by the filters and the slicers, not by the
selection of rows and columns. This means that the set produces the 10 top-selling products over all
time, and then the PivotTable shows the sales in the different years. If you are interested in the
top-selling products in a specific year, you need to move the year to a slicer or to the filter part of
the PivotTable so that the year acts as a filter and operates over the dynamic set too. Dynamic sets
obey the filter context imposed by the filters over the whole PivotTable, not the ones imposed by
the rows and columns to each single cell.
This small example just scratches the surface of the power of MDX. When you use MDX to
query a cube, you can produce very complex and interesting reports, even if to create them,
you definitely need to learn MDX and the structure of an OLAP cube. We believe that when
you are a PowerPivot power user, you want to buy one of the many beautiful books about
MDX and start learning it if you want to get the best out of PowerPivot. Nevertheless,
PowerPivot can still produce great reports without your ever opening an MDX editor, so the
message is this: if you need to use MDX, get prepared for a very heavy period of study; on
the other hand, if you can get the results you need with the PowerPivot user interface only,
life is certainly easier.
Creating Sets of Measures with MDX
The MDX editing feature that we discussed in the previous sections comes in very handy
for defining a different type of set: the measures set. You previously learned how to create
a set filtering some values (such as the product category or the year), but it might be
interesting to create a set that filters measures. If, for example, you normally look at order quantity
and sales amounts together, you could use the standard way of adding both measures to
the PivotTable each time, or you could be tempted to create the set of both measures so
that it becomes easier to create frequent reports containing both values.
You cannot create measure sets by using the Excel user interface, but you can easily
create them by using the MDX editor. In fact, in the OLAP data model, measures belong to a
special dimension called measures and can be treated much the same way as dimension
You can, for example, define a set such as the one shown in Figure 8-41, which contains three
measures (order quantity, sales amount, and tax amount) as members of the special measures