Understanding how a feature/function works in a tool/software is as integral as understanding how to use that function.
Let’s go through what iterative functions are in PowerBI
DAX is evaluated in one of the following ways
Row context — calculated columns
Filter context — measures
Iterative functions allow you to create a measure while employing the row context because, by default, your DAX measure uses the Filter context.
So, what is the filter context?
What is row context?
We now understand the context in which DAX works. Remember, DAX measure work by default with the filter context and always return a scalar value (i.e., a single value and not a list of values unless it involves some table functions).
But there are some situations where you will need to run some other calculations prior. For instance, in our sample data, sales and profit were provided, and I’m interested in the cost of production. Here’s how an excel user will go about it.
In PowerBI, your thought is to create a new column right and then create a total cost measure from that column, i.e., create a new cost column.
Then create your total cost measure.
Well, you are not wrong to do that. It would still work, but what you just did might affect the performance of your report. It’s always advisable to stay away from calculated columns as much as possible.
Say your data have about 2 million rows. What you’ve just done is created another 2 million data points that you could have avoided. How do you then avoid calculated columns? Use Iterative functions. Iterative functions are aggregation functions with an “X” added at the end, e.g., SUMX, COUNTX etc.
Creating a new column would have been avoided using the formula below.
I’ve achieved a similar result with the iterative function without creating a new column.
Let’s see how SUMX works in Excel.
That’s all on Iterative functions. Cheers mate