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.