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

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

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

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

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

## Evaluation Context In DAX Calculations

In this tutorial, we'll learn about evaluation context in DAX. Evaluation or initial context is the...

## Icons In Power BI | DAX, UNICHAR, UNICODE & Custom Images

Icons in Power BI are used to communicate meaning and add more context. You can use them as an...

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...

## Format Data In Power BI: Addressing Irregular Data Formats

In today's blog post, we'll discuss Problem of the Week #6. I'll show you how to format data in Power...

## Power BI P&L Statements: Challenges And Solutions

Profit and Loss Statements are often challenging to create in Power BI, especially if you're working...