This tutorial will teach you about different iterating functions and how to efficiently use them in your calculations.
I often discuss how calculated columns are not required when making some calculations. This is because of iterators.
Iterators or iterating functions can help you do a calculation without physically putting the results in the table.
This technique can help you save up on the memory needed to load your Power BI data model. In the next sections, I’ll show you how to optimize your calculations using iterators.
Calculating Total Sales Using An Iterator Function
To get started, create a new measure for Total Costs. Make sure to select the measure group where you want this new measure to land.
Press Shift and Enter to move down a line before you put the first iterating function, which is SUMX.
In the formula bar of Power BI, you can already see exactly what you need to put after the function as suggested by IntelliSense. For SUMX, you need to add a table after it.
The SUMX formula will run the logic at every single row of the given table. This is why iterators are associated with row context. Within the measure, iterators can turn the formula into a row context.
You will need to reference the Sales table after the SUMX function. To calculate the total costs, you have to multiply Order Quantity by Total Unit Cost.
We don’t need to reference the new column that was created at all. The Total Costs is a measure and I can bring it into my table to evaluate our total costs.
Now, drag the measure inside the table to see the results. Make sure that you selected an initial context from the City filter.
We can change the initial context of our calculation by clicking on the different regions that we want to have a look at.
The Total Costs works in a similar way in terms of the initial context. The initial context gets applied to the Sales table, but then within each of these individual results, we’re calculating the Order Quantity multiplied by the Total Unit Cost.
Behind the scenes in our data model, we have turned on our filter and we have context coming in from our Regions table and another context coming in from our Date table. These flow down to our Sales table, which is filtered by the iterating function SUMX.
Since the SUMX function evaluates every single row of the Sales table virtually, there’s no need for a physical column for the results.
After the initial context, SUMX gets the product of Order Quantity and Total Unit Cost for every single row. Lastly, it evaluates all the calculated results from all the rows.
If you noticed, the original Costs column was created through a calculated column. As I’ve said, it’s unnecessary since iterators can already do its work. You can delete it because it can take up unnecessary memory in your model.
Iterating formulas run evaluations at every single row, while aggregating formulas do not.
This is a simple example, but you can be more advanced here and write IF statements and SWITCH statements. Also, you don’t have to create a physical table; you can use a virtual one instead.
A lot of this information is covered in-depth in the Mastering DAX course, but this is just to show you the the beginnings of iterating functions and how to start using them when it’s appropriate.
If you feel the need to create a calculated column inside your fact table, I can almost guarantee that an iterating function will do the work for you.
Using Other DAX Iterator Functions
Now, I’ll show you another example of how iterators can do wonders on your calculation. This time, let’s work out the average cost.
Just copy the Total Costs formula and paste it into a new measure. You just have to change the name to Average Costs and then use AVERAGEX instead of SUMX.
The new formula runs a similar logic because it evaluates every single row of the Sales table. Additionally, you still need to get the product of Order Quantity and Total Unit Cost. The only difference here is instead of sum, the formula calculates the average.
Now, if you bring the Average Costs measure to the table, you can see how it compares to the Total Costs measure.
It’s amazing how you can run a similar logic just by changing the iterating function.
Additional Data Optimization Techniques
To optimize your table, you can delete redundant information like the Total Revenue column.
Since you can readily achieve the average costs, you won’t need the Total Revenue column in your table anymore. As long as you have the Unit Price and the Total Unit Cost columns, everything’s fine.
Now, you can create a new measure instead for Total Sales (Iteration) by using the SUMX function. You just have to reference the Sales table then get the product of Order Quantity and Unit Price.
After that, you can compare the results in the Total Sales and Total Sales (Iteration) columns. They both have the same results, right?
In terms of performance, there’s not much of a difference between using calculated columns and iterators. But when it comes to the data model, an iterator function can get rid of an entire column and save you hundreds of rows of data.
Additionally, you can delete redundant columns because iterators can calculate the necessary results virtually. This practice will make your table a lot thinner and your model a lot faster. Make sure you apply this optimization technique in your own calculations.
Conclusion
To sum up, an iterating function evaluates every single row while aggregators don’t.
The letter X on the end of the function makes it easier to identify iterators. Examples include the SUMX, AVERAGEX, MAXX, MINX functions and more.
Using iterating functions won’t create additional physical tables. This can help you save memory in Power BI.
The examples for SUMX and AVERAGEX that I have covered are simple scenarios. Later on, we’ll go into more advanced ones by adding IF statements and SWITCH statements.
All the best!
Sam
***** Related Links *****
DAX Functions In Power BI: Using Iterators
Iterating Functions In DAX Language – A Detailed Example
Working With Iterating Functions In DAX
***** Related Course Modules *****
Power BI Super Users Workshop
Mastering DAX Calculations
Advanced DAX Combinations
***** Related Support Forum Posts *****
Iterating Formula Or Row Context
What Can You Do With Iterating Formulas In Power BI
Iterating Functions Deep Dive – SUMX, AVERAGEX, MINX, MAXX
For more iterators support queries to review see here….
