Microsoft Office Tutorials and References
In Depth Information
Almost every PowerPivot data model needs to operate on aggregated data. DAX offers a set
of functions that aggregate the values of a column in a table and return a single value. We
call this group of functions statistical functions . For example, the expression
= SUM( Sales[Amount] )
calculates the sum of all the numbers in the Amount column of the Sales table. This expression
aggregates all the rows of the Sales table if it is used in a calculated column, but it considers
only the rows that are filtered by slicers, row, columns, and filter conditions in a pivot table
whenever it is used in a measure.
Note The syntax of aggregation functions in a DAX expression is identical to the one used by
Excel formulas. Moreover, in Excel you can specify a range of cells by using the same syntax
table[column] used also by DAX.
In Table A-1 of the Appendix, you can see the complete list of statistical functions available
in DAX. The main four aggregation functions (SUM, AVERAGE, MIN, and MAX) operate only
on numeric values. These functions are identical to the corresponding Excel functions both in
name and in behavior: any data that is not numeric is ignored in the operation. In PowerPivot,
these functions work only if the column passed as argument is of numeric or date type. In
Figure 3-16, you can see an example of measures defined by these statistical functions.
FIguRE 3-16 Different measures using statistical functions aggregate LineTotal.
As in Excel formulas, DAX offers an alternative syntax to these functions to make the calculation
on columns that can contain both numeric and non-numeric values, such as a text column.
That syntax simply adds the suffix A to the name of the function, just to get the same name
and behavior as Excel. However, these functions are useful only for columns containing
TRUE/FALSE values because TRUE is evaluated as 1 and FALSE as 0. Any value for a text