I want to dive here into iterating functions within the DAX language in Power BI.
The reason why I want to spend some time showing you what you can do inside of iterating functions is because it’s such an important concept that you need to learn very well.
You might have only just completed some simple SUMs or potentially used the AVERAGE function, but it’s so key to understand how to use the iterating functions that have an X on the end also, for example; SUMX, AVERAGEX or COUNTX.
The reason being is because you can get far more advanced within an iterating function than you can with an aggregating function.
Iterating functions give you so much more flexibility in terms of the calculations that you can do at a row level versus aggregations which only allow you to aggregate up an entire column.
Now within iterating functions, you first need to input a table reference and then an expression, but certainly expand your mind in terms of what either of these can actually be.
Within iterating functions you can actually put inside any table or any derivative of a table…also any virtual table or reference to table.
That enables far more flexibility than you would ordinarily have if you just use an entire table like your transaction table to run some logic through.
The expression (or measure) you input can also be very advanced.
You could use advanced IF logic or SWITCH logic…basically anything that you can do at a row level inside a calculated column, you can actually put it in that expression part of an iterating function.
I don’t want to go into too much detail here because I run through everything in the tutorial.
I guess the key takeaway from this is that iterating functions are incredibly important to understand and learn well inside of Power BI.
If you do learn this well, then the opportunities to run internal calculations in memory via measures becomes really, really powerful.