Microsoft Office Tutorials and References
In Depth Information
Simplifying Browsing with a Period Table
In this chapter, you have seen how to create single measures with special calculations over
time, such as year-to-date, year-over-year, and so on. One drawback of this approach is that
you have to define one measure for each of these calculations, and the list of the measures in
your model might grow too long.
A possible solution to this issue, which is also an interesting generic modeling solution, is to
create a special table containing one line for each of the calculations you might want to apply
to a measure. In this way, the end user has a shorter list of measures and possible operations
on them, instead of having the Cartesian product of these two sets. However, you can also see
that this solution has its own drawbacks, and maybe it is better to create just the measures you
really want to use in your model, trying to expose only the combinations of measures and
calculations that are meaningful for the expected analysis of your data.
First of all, you create a Period table in Excel, which contains the list of possible calculations
that should be applied to a measure, as you can see in Figure 7-45. The complete model
used for this example is available in the CH07-10-PeriodTable.xlsx workbook included on
the companion DVD.
FIguRE 7-45 A Period table in Excel.
The same table has to be imported as a linked table into PowerPivot. However, you do not have
to define any relationships between this table and other tables in your model because you use
the selected member of the Period table to change the behavior of a measure through its DAX
definition. Nevertheless, PowerPivot warns you of a missing relationship when you browse data
in a PivotTable, as you can see in Figure 7-46.