DAX Iterators and Contexts

Context in DAX

• Context is a layer of filtering applied to calculations, often applied dynamically to produce a result specific to every value in a pivot table or visual.
• There are two types of context in DAX: Filter Context and Row Context.

Filter Context

• Filter context is a set of column-based filters applied to the underlying data for every DAX calculation.
• It is established to determine the most relevant set of column filters for a specific calculation.
• Filter context is defined using:
  • Row Selection
  • Column Selection
  • Report Filters
  • Slicers Selection
• Rows outside of the filter context are not considered for the computation.

Row Context

• Row context is defined by:
  • Calculated column definitions
  • Row Iteration functions (e.g., SUMX, AVERAGEX)
  • User formulas
• Row context is needed to evaluate column values and is referred to as "Row by Row Evaluation".
• It is defined automatically for each row and is used to iterate over rows.

Filter Context and Row Context

• Filter context filters tables, while row context iterates rows and is used to evaluate column values.
• Both are "evaluation contexts" that can be used together to perform complex calculations.

Iterators in DAX

• Iterators are functions that perform calculations on a row-by-row basis, processing each row of a table individually.
• Common iterator functions include SUMX, AVERAGEX, MINX, MAXX, and COUNTX.
• Iterators consist of two arguments: a table and an expression.
• Filters can be applied to the table to further refine the data set used in the calculation.

Iterator Functions

• AVERAGEX: Calculates the average of a set of expressions evaluated over a table.
• COUNTAX: Counts the number of values that result from evaluating an expression for each row of a table.
• COUNTX: Counts the number of values that result from evaluating an expression for each row of a table.
• GEOMEANX: Returns the geometric mean of an expression value in a table.
• MAXX: Returns the largest numeric value that results from evaluating an expression for each row of a table.
• MEDIANX: Returns the 50th percentile of an expression value in a table.
• MINX: Returns the smallest numeric value that results from evaluating an expression for each row of a table.
• PRODUCTX: Returns the product of an expression value in a table.
• RANKX: Returns the rank of an expression that is evaluated in the current context.
• SUMX: Returns the sum of an expression evaluated for each row in a table.

SUM versus SUMX

• SUM() is an aggregator function that adds up all the values in a single column after applying all filters.
• SUMX() is an iterator function that works through a table, row by row, to complete the evaluation after applying all filters.
• SUM() has no knowledge of rows, while SUMX() has awareness of rows and can reference the intersection of each row with any columns.

FILTER Function

• FILTER() returns a table that represents a subset of another table or expression.
• It can be used to reduce the number of rows in the table and use only specific data in calculations.

Context Transition in DAX

• Context Transition occurs when a Row Context is converted into a corresponding Filter Context.
• It typically happens when using iterator functions in combination with aggregation functions.
• The CALCULATE and CALCULATETABLE functions are commonly used to perform context transition explicitly.

Learn about iterators in DAX, their performance optimization, and the difference between filter context and row context. Understand how to use iterators efficiently with large data sets.