Databases Reference
In-Depth Information
For instance, if we were to use a pivot table with several different dimensions, and
in which the active dimensions are dynamically being expanded or collapsed, the
sub-aggregation used to calculate the average load factor should be adapted with
each new dimension arrangement; the correct aggregation expression will depend on
which dimensions are visible in the pivot table.
To account for the different possible arrangements in the chart's dimensions, we will
make use of the
Pick
function in conjunction with the
Dimensionality
function and
the
Aggr
expression we previously used.
The
Dimensionality
function is used in pivot tables to indicate which level of
aggregation is active in the pivot table for each of its segments or rows. For instance,
if all dimensions are collapsed and only the first dimension is visible, then the
Dimensionality
function would return
1
; if the first dimension is expanded, the
Dimensionality
function would return
2
, and so forth.
The result of the
Dimensionality
function is row-specific, so we could have one
row with one level of aggregation (depending on which of its dimensions
are expanded) and another row with a different level. The
Dimensionality
function will account for each rows' aggregation level to provide the
correct result. The following screenshot illustrates this concept, with the result
of the
Dimensionality
function presented as the second expression column and
color-coded for easier understanding:
