Iterating Formulas Deep Dive Power BI

Working With Iterating Functions In DAX

No comments

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

Simple Iteration Using SUMX

iterating functions

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

iterating functions

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

iterating functions

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.

iterating_functions_dd_05

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

iterating_functions_dd_07

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

iterating_functions_dd_08

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.

iterating_functions_dd_09

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.

iterating_functions_dd_10

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

iterating_functions_dd_11

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.

iterating_functions_dd_12

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

iterating_functions_dd_13

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.

iterating_functions_dd_14

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

iterating_functions_dd_16

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.

iterating_functions_dd_17

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.

iterating_functions_dd_18

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

iterating_functions_dd_19

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.

iterating_functions_dd_20

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.

iterating_functions_dd_21

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

Simple Iteration Using MAXX

iterating_functions_dd_22

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

membership banner 3

***** Related Links *****
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

***** Related Course Modules*****
Mastering DAX Calculations
Solving 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

For more iterating functions support queries to review see here…..

Enterprise DNA Events

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.