top of page
Search
    • 2 min read

DAX Iterators: The X-Factors



To a beginner, one can find some similarities between the Excel functions and DAX. And the primary reason is that both Excel and DAX are functional languages.


The concepts of statements, loops, and jumps do not exist in Excel functions and DAX. So, aggregator functions like SUM, MIN, MAX, AVERAGE have similar structures or arguments in Excel and DAX.

The concept of iterators is new for Excel users. Consider the following example:












To calculate the sales amount for the year, we implement the following steps:

  • Add a column – Sales Amount

  • Apply the formula [Unit Sold]*[Unit Price]

  • Apply aggregate function SUM on Sales Amount

With DAX iterators, we can perform the same operation in a single step. As the name suggests, a DAX iterator performs a calculation on each row of the table, aggregating the result to return the single value requested.

 Sales Amount = 
  SUMX(
    dtSales,
     dtSales[Unit Price] * dtSales[Unit Sold]
  )

In simple English, using DAX, we are saying:

· Go to the dtSales table

· Start evaluating each row of the dtSales table

· Apply the formula [Unit Price] * [Unit Sold] in each row

· Aggregate the value of the previous step

We can calculate the average instead of a sum by following similar steps. 


 Avg Sales Amount = 
  AVERAGEX(
    dtSales,
      dtSales[Unit Price] * dtSales[Unit Sold]
  )

DAX first calculates the sales amount for each row by applying the expression [Unit Price] * [Unit Sold], and then calculate the average of the output.


The above steps are happening without making any physical change (read adding a column) in the table. Hence, the calculation is quicker and uses less memory. The flip side is a calculation in iterators that are not visual. We do not see the columns or tables it creates for the calculation; it exists only for the lifetime of the calculation.


A few common iterators in DAX are SUMX, AVERAGEX, MEDIANX, MINX, MAXX, COUNTX, COUNTAX.


All of them have the same arguments: (Table, Expression)


Iterators in DAX follow below-mentioned steps:

  • Evaluates the first parameter in the existing context(read filter contexts) to determine the rows to scan

  • Creates a new row context for each row of the table evaluated in the previous step

  • Iterates the table and evaluates the second parameter in the existing evaluation context, including the newly created row context.

  • And last aggregates the values computed during the previous step.


For details on Filter and Row contexts in DAX, follow the article


We can add filters and conditions in the iterators to further define the calculation:


 Avg Sales (15 or more units) = 
  AVERAGEX(
  //Filter sales table for Unit Sold >= 15
   FILTER(
     dtSales,
      dtSales[Unit Sold] >= 15
     ),
  dtSales[Unit Sold] * dtSales[Unit Price]
  )











In simple English, we are saying DAX to:

  • Apply filter on the dtSales table where Units Sold >= 15

  • On that filtered table, insert a temporary column and apply the formula [Unit Price] * [Unit Sold]

  • Calculate the average of the temporary column


bottom of page