Iterator functions

In addition to the usual functions for data aggregation DAX has a special class of functions that change context of the execution for computing operations, and yet we call them the “iterators”. Sometimes we also call them the “X” functions, because after the name of the aggregation operation (SUM, AVERAGE, COUNT) stands the letter “X”, which indicates that these are iterators (SUMX, AVERAGEX, COUNTX). More in the text that follows…

First of all, let’s say something about the context of the calculation. DAX knows two context calculation and display of reporting dimensions: the context filters and context of rows. The first context implies that the aggregation is performed over all the data, and by adding reporting dimensions they perform filtering. For example, if we add dimension KOL (Quantity) in field Values we’ll get Sum of KOL. Further if we add dimension Klasifikacija (Classification) in Rows we’ll get sum of quantities regarding given classification. By adding classification we have given a context to summarized values, i.e. we applied filter context. Row context means that calculation is being executed row by row, as we have added calculated column which is a sum of other two columns. Row context much resembles the way how traditionally Excel calculates values.

Iterator functions automatically change calculation context from filter context to row context. Since calculation is performed row by row they have some additional features compared to conventional aggregation functions. First, let’s show syntax of function SUMX:

SUMX(<table name>,<expression>)

First argument of this function is a name of a table, and second is expression. Expression can be also a name of a column; in that case result is no different of one we would get by using conventional SUM function. I.e. formulas:

SumaKolicina1:=SUM([KOL])

SumaKolicina2:=SUMX(Transakcije,[KOL])

are giving the same result. The advantage of aggregation functions is that expression can be also „an iterator“, which means that it is calculated row by row. For example, to enlarge quantity by 10%, row by row, we should write a formula:

SumaKolicina3:=SUMX(Transankcije,[KOL]*1.1)

In a similar manner, for averaging of data we can use AVERAGEX function and for counting COUNTX function. Look at following example:

ProsekKolicina:=AVERAGEX(Transakcije,[KOL])

BrojTransakcija:=COUNTX(Transakcije,[KOL])