Aggregation Functions

Question 1

SUM(column)

Answer 1

Adds all the numbers in a column.

Question 2

AVERAGEX(table,expression)

Answer 2

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.

Question 3

COUNTBLANK(column)

Answer 3

Counts the number of blank cells in a column.

Question 4

DISTINCTCOUNT(column)

Answer 4

Counts the number of distinct values in a column.

Question 5

DISTINCTCOUNTNOBLANK (column)

Answer 5

Counts the number of distinct values in a column.

Question 6

COUNTA(column)

Answer 6

Counts the number of rows (records) in the specified column. Excludes blanks.

Question 7

COUNT(column)

Answer 7

Counts the number of rows (records) in the specified column. Excludes blanks. Works better with columns having numbers.

Question 8

COUNTX(table,expression)

Answer 8

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.

Question 9

MAXX(table,expression)

Answer 9

Evaluates an expression for each row of a table and return the largest value.

Question 10

APPROXIMATEDISTINCTCOUNT(columnName)

Answer 10

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.

Question 11

AVERAGE(column)

Answer 11

Returns the average (arithmetic mean) of all the numbers in a column.

Question 12

AVERAGEA(column)

Answer 12

Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.

Question 13

MAX(column)

Answer 13

Returns the largest value in a column, or between two scalar expressions.

Question 14

MAXA(column)

Answer 14

Returns the largest value in a column. Handles text and non-numeric values.

Question 15

PRODUCTX(table, expression)

Answer 15

Returns the product of an expression evaluated for each row in a table.

Question 16

PRODUCT(column)

Answer 16

Returns the product of the numbers in a column.

Question 17

MIN(column)

Answer 17

Returns the smallest value in a column, or between two scalar expressions.

Question 18

MINA(column)

Answer 18

Returns the smallest value in a column. Handles text and non-numeric values.

Question 19

MINX(table, expression)

Answer 19

Returns the smallest value that results from evaluating an expression for each row of a table.

Question 20

SUMX(table, expression)

Answer 20

Returns the sum of an expression evaluated for each row in a table.

Question 21

COUNTAX(table, expression)

Answer 21

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.

Question 22

COUNTROWS(table)

Answer 22

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.