DAX HANDBOOK
3. Iterative functions

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 

Authors

Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Iterative functions have one special ability, and that is to form a “Virtual” row context for the table supplied as an argument.

Table vs Scalar Iterative Functions

The most used iterative functions are:

  • SUMX, MINX, MAXX, AVERAGEX, CONCATENATEX, COUNTX, RANKX
    • Pretty much any function ending with X
    • All those functions have 2 things in common, they form row context and return a scalar value
  • FILTER, ADDCOLUMNS, SUMMARIZE, GENERATE etc.
    • These functions create row context, but instead of scalar value, they return modified tables as result.

In this chapter, we will focus on the “X” group of iterators, while table iterators will be covered in the next chapter. 

Syntax


Almost all “X” type iterators have the same syntax which we will explain with the [SalesAmountCorrect] measure. We will again focus on the first row in a visual filtered by the Black color.
SalesAmountCorrect = SUMX(Sales,Sales[Price]*Sales[QTY])

This image has an empty alt attribute; its file name is DAX-Iterative-functions-1.jpg
  1. The original filter context is formed with the condition Sales[Color] = “Black”
    • Source table gets filtered to contain only sales of color “Black”
  2. After the table has been filtered by the original filter context, the calculation starts.
    SUMX accepts 2 arguments:
    • First argument (Sales), is the table upon which we wish to create row-by-row calculation (row context)
      • Sales table is not the whole table, but the one already filtered by the original filter context!
        o We can picture this as a filtered Sales table to which we add a “Virtual” column called X
    • The Second argument of SUMX function is the calculation that will be performed in the virtual X column, respecting row-by-row rules of iteration.
      • In our example, the calculation that should be performed row-by-row is Sales[Price]*Sales[QTY]
      • Notice that we didn’t need to use any aggregation (such as SUM) since the X part of the calculation operates in a row context, meaning Sales[Price] can only reference the current row of the iteration. This applies to all rows of the X column.
  3. Once the iteration finishes and the virtual X column computes the row-by-row calculation, the SUM part of the SUMX function starts. It sums up all the computed values of the virtual X column.

Explanation of X in Iterative Functions

The X part works the same for all X types of iterative functions. The only part that differs is the function prefix before X.

  • SUMX will return the sum of the virtual X column
  • MINX will return the min of the virtual X column
  • CONCATENATEX will return the string of X column values combined

Iterators that return scalar values are a great replacement for calculated columns with simple logic, such as column1 +-*/ column2, as well as any kind of Calculated Column of Value type. Calculated columns should still be used in case we wish to create Filter types of columns (e.g. the ones we wish to plot on visual as filters, since Measures itself cannot be used to create the original filter context).

Materials

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

COMMENTS

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

OUR SERVICES

Prefer live training or consultations?

Table of Content

Table of Content

GET LATEST BI NEWS TO YOUR INBOX

Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts

DAX HANDBOOK
7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more

DAX HANDBOOK
6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

DAX HANDBOOK
6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

DAX HANDBOOK
6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
0
Would love your thoughts, please comment.x
()
x
Scroll to Top