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.

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 functionsis that they iterate through something.

## 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 functionsis 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

SUMor anAVERAGE.

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

******* Learning Power BI? *******

**FREE COURSE – Ultimate Beginners Guide To Power BI**

**FREE COURSE – Ultimate Beginners Guide To DAX**

**FREE – 60 Page DAX Reference Guide Download**

**FREE – Power BI Resources**

**Enterprise DNA Membership**

**Enterprise DNA Online**

**Enterprise DNA Events**

******* Related Links *****Using Complex Logic Inside Iterating Functions – Advanced DAX How To Use Iterating Functions To Solve Total Issues In Power BI – DAX ConceptsAdvanced Logic Within DAX Iterating Functions â€“ Detailed Example**

******* Related Course Modules*****Mastering DAX CalculationsSolving Analytical Scenarios w/Power BI & DAX Unique Analytical Scenarios**

******* Related Support Forum Posts *****Combining Aggregating & Iterating Functions To Calculate Median**

Creating Plan / Actual For Year & Months

Calculating Average And Using DateMonth

Creating Plan / Actual For Year & Months

Calculating Average And Using DateMonth

For more iterating functions support queries to review see hereâ€¦..

For more iterating functions support queries to review see hereâ€¦..

## 1 comments on “Working With Iterating Functions In DAX”