DAX — Data Analysis Expressions — is the formula language used to define measures, calculated columns, and calculated tables in Microsoft’s tabular analytical engine: Power BI datasets, Azure Analysis Services, SQL Server Analysis Services Tabular mode, and Power Pivot in Excel. The concept that makes DAX fundamentally different from SQL and Excel formulas — and the concept that must be understood before writing any non-trivial DAX — is filter context: every DAX expression is evaluated within a context defined by the active filters in the report, the row being evaluated in a calculated column, or the explicit filters applied by DAX context manipulation functions. The same measure expression returns different values depending on the filter context in which it is evaluated — which is exactly what makes DAX powerful for interactive analytical reporting and counterintuitive for developers who expect expressions to produce deterministic, context-independent results.
How It Works
Two types of context govern DAX evaluation. Filter context is the set of filters active on the data model — the entities selected in a slicer, the periods in a visual’s axis, the report-level filter. Every measure computation begins by evaluating which rows of the fact table survive the current filter context, then applying the measure’s aggregation to those rows. Row context exists only in calculated columns and the row-iteration functions (SUMX, AVERAGEX, FILTER): it is the context of the current row being evaluated in the iteration.
The functions that manipulate filter context — CALCULATE(), ALL(), ALLEXCEPT(), KEEPFILTERS(), REMOVEFILTERS() — are the most powerful and most misused in DAX. CALCULATE() evaluates its first argument in a modified filter context defined by its subsequent arguments. A year-to-date measure, a prior-period comparison, a percentage-of-total calculation all require CALCULATE() to modify the filter context that DAX would apply by default.
Design Decisions and Trade-offs
The architectural decision with the greatest impact on DAX performance is model design: DAX measures execute against the VertiPaq in-memory engine’s columnar structure, and the engine’s performance depends heavily on the cardinality of the columns involved in the calculation, the relationship graph the measure must traverse, and whether the filter context can be resolved through predicate pushdown to the columnar store or requires materialisation. A poorly designed data model produces slow DAX regardless of how the measures are written; a well-designed model makes complex DAX fast.
Common Implementation Errors
The DAX implementation error that produces the most difficult-to-diagnose incorrect results in finance BI is incorrect use of ALL() in a percentage-of-total measure. The pattern % of Total Revenue = DIVIDE([Revenue], CALCULATE([Revenue], ALL(DimEntity))) is intended to express each entity’s revenue as a percentage of total group revenue. But when the report also has a period slicer, ALL(DimEntity) removes the entity filter while leaving the period filter intact — which is the correct behaviour. If the developer uses ALL() (without specifying the table) instead of ALL(DimEntity), it removes all filters from the model, including the period filter — computing the percentage of all-time total revenue rather than the current period’s total. The reports look plausible (percentages sum to 100%) but the denominator is wrong. Specifying the exact dimension to remove with ALL() is not stylistic; it is correct versus incorrect behaviour.
How Loop Wise Solutions Designs for This
We apply a DAX review standard to every measure in a finance BI model before deployment — specifically reviewing all CALCULATE() expressions for correct filter context management, testing each measure against the edge cases that reveal context manipulation errors (reports with multiple slicers, cross-filter dimensions, measures used as filter arguments in other measures), and documenting the intended behaviour of each complex measure so that future developers understand the filter context intent.
