Aggregation Functions Flashcards
SUM(column)
Adds all the numbers in a column.
AVERAGEX(table,expression)
Calculates the average (arithmetic mean) of a set of expressions evaluated over a table. The expression could be CALCULATE(SUM) of a single value column with filters.
COUNTBLANK(column)
Counts the number of blank cells in a column.
DISTINCTCOUNT(column)
Counts the number of distinct values in a column.
DISTINCTCOUNTNOBLANK (column)
Counts the number of distinct values in a column.
COUNTA(column)
Counts the number of rows (records) in the specified column. Excludes blanks.
COUNT(column)
Counts the number of rows (records) in the specified column. Excludes blanks. Works better with columns having numbers.
COUNTX(table,expression)
Counts the number of rows that contain a non-blank value or an expression that evaluates to a non-blank value, when evaluating an expression over a table.
MAXX(table,expression)
Evaluates an expression for each row of a table and return the largest value.
APPROXIMATEDISTINCTCOUNT(columnName)
Returns an estimated count of unique values in a column. This function invokes a corresponding aggregation operation in the data source, which is optimized for query performance, but with slightly reduced accuracy. This function can be used with the following data sources: Azure SQL, Azure SQL Data Warehouse, BigQuery, Databricks, and Snowflake. This function requires DirectQuery mode. Import mode and dual storage mode are not supported.
AVERAGE(column)
Returns the average (arithmetic mean) of all the numbers in a column.
AVERAGEA(column)
Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.
MAX(column)
Returns the largest value in a column, or between two scalar expressions.
MAXA(column)
Returns the largest value in a column. Handles text and non-numeric values.
PRODUCTX(table, expression)
Returns the product of an expression evaluated for each row in a table.
PRODUCT(column)
Returns the product of the numbers in a column.
MIN(column)
Returns the smallest value in a column, or between two scalar expressions.
MINA(column)
Returns the smallest value in a column. Handles text and non-numeric values.
MINX(table, expression)
Returns the smallest value that results from evaluating an expression for each row of a table.
SUMX(table, expression)
Returns the sum of an expression evaluated for each row in a table.
COUNTAX(table, expression)
The COUNTAX function counts non-blank results when evaluating the result of an expression over a table. That is, it works just like the COUNTA function, but is used to iterate through the rows in a table and count rows where the specified expressions results in a non-blank result.
COUNTROWS(table)
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.
