Microsoft Office Tutorials and References
In Depth Information
As we anticipated, this solution has several drawbacks.
■ After you put Period in rows or columns, you cannot change the order of its members.
Actually, you can do this by using some Excel features, but it is not as immediate
and intuitive as moving the list of measures into the Values list in the PowerPivot
Field List panel.
■ The number format of the measure cannot change for particular calculations
requested through some Period values. For example, in Figure 7-48, you can see
that the DiffPercPriorYear and DiffPercYTDPriorYear calculations do not display the
CalcLineTotal value as a percentage because you can define a single number format
for a measure in a PivotTable. A possible workaround is to change the number
format directly in Excel cells, but this change is lost as soon as you navigate into the
■ If you use more than one measure in the PivotTable, you must create a set based on
column items in Excel, choosing only the combination of measures and Period values
that you really want to see in the PivotTable. You can see an example of how to create
these sets in the “Defining Sets” section of Chapter 8, “Mastering PivotTables.”
■ You have to create a specific DAX expression for each combination of Period
calculations and measures that you want to support. This is not flexible and scalable as a
more generic solution could be.
You have to evaluate case by case whether or not these drawbacks make the implementation
of a Period table a good option.
Calculation Parameters using an unrelated Table
In the last section, you saw us apply a technique that depends on a table in the
PowerPivot model that does not have any relationships with other tables in the same
model. In general, this technique might be useful as a way to pass information to a
measure as if it were a parameter. For example, imagine that you define a table in
Excel that contains all the integers from 1 to 10, and then you import this table into
PowerPivot, calling it SimulationParameter. At this point, you might use the value
selected in this table in the DAX expressions of your measures, using that number as
if it were a parameter passed to your formula. Moving that table into a slicer would
be a convenient way for an end user to look at results by changing the selected value.