We discussed how the DAX engine is optimised for working with columns, not rows. However, there are many situations where that isn't going to be enough. For those situations, we need something called an iterator.
There are times when you have to process things in a row-by-row manner. And as a result of that, the performance for iterators is worse than functions that take a columnar approach. SUMX, which is an iterator, is going to be a less performant operation than just SUM.
That being said, don't be afraid to use iterators because they're impossible to avoid when you're working with the DAX engine. You should only be cautious if you're forced to start nesting them or if you have to use them all over the place.
One of the biggest benefits they provide is they allow for the use of multiple-column expressions. If you want to avoid cluttering your data model with calculated columns, you're going to need to use an iterator.
While iterators aren't all the same, many of them do follow the same pattern. The first and most important thing is that the first parameter is a table. An iterator needs to know what it's iterating through. You can't make any assumptions about that. You have to tell it explicitly, 'here's the table you're going to loop through'.
The second parameter is an expression, so it takes in a table, says, 'here's what we're going to loop though', and then it takes in the expression and says, 'for each row, evaluate this expression with a row context on just this row, nothing else, just this row'.
For example, the FILTER function takes in a table, it takes in an expression that it evaluates on each row, and then the FILTER function returns a table that's been filtered down.
Many of the iterators end in the letter 'X' (CONCATINATEX, SUMX, AVERAGEX, etc.) Let's say we want to take the average of gross sales, but we don't want to create a calculated column because that'll take up more RAM, and it might clutter up our data model. Well, here's an example of average gross sales using an iterator:
Average Gross Sales: AVERAGEX(Category, [Quantity] * [Price])On the left is the AVERAGEX iterator, and that's going to do practically the same thing as average, but it's going to iterate. The first parameter is 'Category', and that is a table. I'm passing a table and saying for every row, calculate 'Quantity * Price' and then average it together. It's going to go row by row through the Category table, and, for each row, it's going to multiply quantity and price and then it's going to take the average of those calculated values.
To show this in action, the below screenshot shows what happens when I try to find the average of a calculation of two columns. An error appears as AVERAGE only accepts one column reference as an argument.
But if I use AVERAGEX, and use the Sales table as the first parameter (the table to do the calculation on) then I can use two columns as arguments to do the calculation.
