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.