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

parameter.

■
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-

02-Calculate-demo.xlsx.

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] ) )