Aggregation Functions


Aggregation functions return a scalar value applying an aggregation function to a column or to an expression evaluated by iterating a table expression.

Function Description
APPROXIMATEDISTINCTCOUNT Counts the approximate number of distinct values in a column. Currently this function is only available on a DirectQuery table from Azure SQL or Azure SQL Data Warehouse.
AVERAGE Returns the average (arithmetic mean) of all the numbers in a column.
AVERAGEA Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.
AVERAGEX Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
COUNT Counts the number of rows in the table where the specified column has a non-blank value.
COUNTA Counts the number of values in a column.
COUNTAX Counts the number of values which result from evaluating an expression for each row of a table.
COUNTBLANK Counts the number of blanks in a column.
COUNTROWS Counts the number of rows in a table.
COUNTX Counts the number of values which result from evaluating an expression for each row of a table.
DISTINCTCOUNT Counts the number of distinct values in a column.
DISTINCTCOUNTNOBLANK Counts the number of distinct values in a column.
MAX Returns the largest value in a column, or the larger value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
MAXA Returns the largest value in a column. Does not ignore logical values and text.
MAXX Returns the largest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order.
MIN Returns the smallest value in a column, or the smaller value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
MINA Returns the smallest value in a column. Does not ignore logical values and text.
MINX Returns the smallest value that results from evaluating an expression for each row of a table. Strings are compared according to alphabetical order.
PRODUCT Returns the product of given column reference.
PRODUCTX Returns the product of an expression values in a table.
SUM Adds all the numbers in a column.
SUMX Returns the sum of an expression evaluated for each row in a table.

Last update: Oct 27, 2020   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Imke Feldmann

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

Contribute

Want to improve the content of DISTINCTCOUNTNOBLANK? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.