Microsoft Office Tutorials and References
In Depth Information
understanding the CALCuLATE Function
The CALCULATE function is the magic key for many DAX calculations. However, it is not very
intuitive, and for these reasons it deserves a section dedicated to it so that you can take the
time you need to discover the intricacies of this function.
First of all, let us review the syntax.
CALCULATE( <expression>, <filter1>, <filter2>… )
■ The result of CALCULATE is the evaluation of the expression represented by the first
■ The first parameter expression is evaluated in a context that is modified by the
subsequent filters passed as parameters. A key point is that these filters can both enlarge
or restrict the filter context.
Because the final result needs to be a value and not a table, the expression is usually an
aggregation function such as SUM, MIN, MAX, COUNTROWS, and so on. As we mentioned
in previous sections, an aggX function is required whenever the expression that has to be
aggregated contains more terms, such as when you want to multiply UnitPrice by Quantity
and you do not have a calculated column for that in the table.
Let us try to get a better understanding of how the filters affect the expression evaluation
by looking at some examples, which you can find also in the companion workbook CH06-
You start by using the Orders table (see Figure 6-1). If you put the sum of Quantity data in a
PivotTable and the Channel attribute on the rows, you get the result shown in Figure 6-29.
FIguRE 6-29 Quantity of orders grouped by Channel.
Now you might need to calculate a measure that is not affected by the selection of the Channel
attribute, or in other words, that always calculates the context for all the channels by eliminating
the Channel column from the filter context. So you define the QtyAllChannels measure in this way:
CALCULATE( SUM( Orders[Quantity] ), ALL( Orders[Channel] ) )