If you are creating some analysis inside of Power BI and you’re utilizing DAX measures, then having a superior understanding of how ITERATING functions work is very important.
This is what this particular post will showcase to you - understanding how important these functions are.
To be clear when I mentioned iterating functions, I'm referring to the functions that have an X at the end.
The main difference between these and the alternative aggregating functions is that iterating functions evaluate logic at every single row of a table that you place inside the function.
These functions can be much more powerful than a simple aggregation function like SUM or AVERAGE. Mainly because you can implement some very advanced logic inside of these that run evaluations you specify at each and every single row.
What I want to focus on today is the two main iterating functions that I believe are the best. They include SUMX and AVERAGEX.
In this first tutorial, I run through AVERAGEX.
I run through in detail what you need to understand about the type of tables you can place inside this function. Not only can you put a physical table, but you can also place in these virtual tables (which by itself is a fascinating topic).
Here is where you can get really advanced inside of Power BI. What I mean here is you can manipulate virtual tables inside of iterating functions so you’re only iterating through a certain subset or consolidated version of your data.
I cover this in detail and it’s really well worth understanding how this works if you're not totally across it already.
In this next unique example, I’m going to show you how you can utilize SUMX within your other formulas.
This is not a simple tutorial but I chose this particular one because I thought that if you can see and understand how this works then you will truly know how iterating functions work inside of Power BI.
I decided to cover relatively short and sweet topics in this post mainly because this deserves some true focus on some of the most important DAX functions that you can learn and utilize inside your Power BI reports and models.