Working With Iterating Functions In DAX

I’ve seen some confusion with iterating functions in DAX whenever I work with Enterprise DNA members in the forum. So we will do a review of what can be achieved using these functions and why you should use them. You may watch the full video of this tutorial at the bottom of this blog.

As you get more into DAX formulas, you will use these iterating functions a lot more than you think. They’re much more versatile than simple aggregation functions like SUM or AVERAGE.

I have some examples here, and I want to work through the logic in each of these examples so that you can get a very good understanding of what actually happens when logic is run via these formulas.

The key thing about iterating functions is that they iterate through something.

Table of Contents

Simple Iteration Using SUMX

In this case, I want to sum up something but want to iterate through every single row in this Sales table.

And then at every single row, I want to run this specific logic here.

Let’s quickly jump to our very simple Sales table, where I want to run some logic at every single row. And then I want to multiply Quantity by Price.

The price doesn’t exist in this table. And for this particular model, it is actually in the Product table.

The next thing I need to do is to reach back to my Product table and I can do that with RELATED. When we are calculating quantity times the relevant price, we do this for every single row. This is exactly what is happening inside of this formula.

These are the key concepts to remember when we start doing more advanced things.

An Alternative Approach To Getting Sales Using SUMX

In this example, I have SUMX iterate through the Sales table again. So we run that logic in every single row and calculate up the quantity times price at every single row and then aggregate it up once all the iterating is done.

You’ll see here that this result is exactly the same as the results in the first example (SUMX Tutorial 2) or the Total Sales.

You can place a whole variety of different things in here – it can be a measure and you can branch out with an iterating function. You don’t have to reference specific columns to actually get the calculations correctly.

As you can see here, all I’ve done is to place a measure (Total Sales) here and I’m getting exactly the same result.

SUMX And Logical Functions

I’m using SUMX here, but it is also relevant for AVERAGEX, MINX, and MAXX. They are all very similar things.

Another key thing to remember with these iterating functions is that they run logic at every single row, and it saves the result into memory.

Once all of those values are saved into memory, the overarching calculation is done – like a SUM or an AVERAGE.

Back to our example, you can see here that the amount in this column is slightly less than the previous columns.

With the SUMX iterating function, I want to iterate through every single row in the Sales table. And in every single row, I want to calculate if that particular sale is greater than $2,000. If it is, I want to calculate the Total Sales for that row. But if the total of the sale was less than $2000, then I want to return zero. So I only want to calculate the higher-value sales.

Some people will suggest that I could just put the logic inside a calculated column. But we don’t want to do that because there is a much better way to run this logic inside of DAX rather than putting it physically inside a column.

Iterating Through Tables

This is where iterating functions are seriously amazing. Not only can you iterate through a physical table like we did before, but you can also iterate through some other virtual table that you input as a parameter.

We’re going to think that every single result here has a specific context. In this particular case, we have the customer context.

In this context, I want to iterate through every single location and want to see if a particular customer has reached sales above $5,000.

If it does, I want to calculate up the Total Sales. But if it does not, I want it to return zero.

So in this particular case, I am calculating up the high-value locations that this customer is buying at and the total sales that they are making in those locations.

What VALUES is doing here is it is creating a virtual table. So SUMX is not even working over anything physical here. It is actually working over something that is totally virtual.

Simple Iteration Using MINX

This is similar to the previous examples but instead of SUMX, we’re going to run some other type of in aggregation once we iterate through every single row.

In this particular case, we’re writing through every single location for each customer and working out the total sales for each of those locations.

Then we want to return the very minimum amount of the location where they purchased.

Simple Iteration Using MAXX

I’m also doing a similar thing here just to show you the versatility of iterating functions. I’m going through every single location looking at their sales but then returning the maximum of those location sales, which is why we are seeing higher numbers here.

Conclusion

The key thing that I wanted to touch upon in this article is the range of options that you have inside an iterating function versus a simple sum or an average.

Once you start digging into more complex calculations, you’ll see how essential these particular functions are to return values at a context level.

There’s so much versatility and so many ways that you can run these calculations using a simple formula if you just know how to manipulate the parameters.

If you enjoyed learning about these iterating functions, you can browse through these links to get more information on this topic:

Key Concepts For Iterating Functions In Power BI

Iterating Logic Through Virtual Tables – Advanced DAX Concepts In Power BI

Enjoy working through this one.

Sam