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 Returns an estimated count of the unique values in a column. This function invokes a corresponding aggregation operation in the data source, optimized for query performance but with slightly reduced accuracy. You can use APPROXIMATEDISTINCTCOUNT with the following data sources: Azure SQL, Azure SQL Data Warehouse, BigQuery, Databricks, and Snowflake. Note that this function requires DirectQuery mode. Import mode and dual storage mode are not supported.
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: Mar 13, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

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.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

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.