Skip to main content

Supported aggregation functions with DirectQuery

This table lists aggregation functions existing in ActivePivot and their support level with DirectQuery.
Note that the support can depend on whether the aggregation function is applied on simple scalar numbers (such as integers or floating points) or on vectors (where the numbers are grouped into arrays).

The following notations are used:

SymbolMeaning
tickfully supported
pluspartially supported
minusnot (yet) supported
crosswill not be supported
Function NameScalarVectorizedDocumentationAdditional DirectQuery notes
ApproxCountDistincttickcrossApproximate count of distinct contributions.Being an approximation, values can differ between databases. Not supported by MS SQL 2017
AverageticktickAverage of numbers.
CountticktickCount contributions.
DistinctCountpluscrossCount of distinct contributions.Partially supported in BigQuery, Clickhouse and Snowflake.
Unsupported with Synapse.
GrossSumticktickSum aggregation of the absolute value of numbers.
LongticktickSum aggregation of positive numbers.
MaxtickcrossMaximum of numbers.
MintickcrossMinimum of numbers.
MaxHistorycrosscrossMaximum of numbers with history.
MinHistorycrosscrossMinimum of numbers with history.
MultiplypluscrossMultiplication of numbers.As it is not a standard aggregation in databases, it is emulated by DirectQuery and can result in slightly off results.
Population StdminusminusPopulation standard deviation
Population VarianceminusminusPopulation variance
Sample StdminusminusSample standard deviation
Sample VarianceminusminusSample variance
SingleValuecrosscrossWith all values equals, select only one.It can be emulated with aggregation functions Min or Max
ShortticktickSum aggregation of negative numbers.
SquareSumtickcrossSum aggregation of the square of numbers.
SumticktickSum of numbers.