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.
Answers before you ask.
DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services Tabular, and Power Pivot, used to define measures, calculated columns, and calculated tables. It expresses the calculations that turn a data model into analytics — from simple sums to complex context-dependent logic evaluated at query time.
Its filter context model. In DAX, a calculation's result depends on the filters in force where it is evaluated — the surrounding report context — rather than on explicit row references. This context-driven evaluation is unlike SQL's set operations or Excel's cell references, and it is the concept that most distinguishes DAX and most often confuses newcomers.
Because the same expression can return very different results depending on the filter context it runs in, and functions like CALCULATE deliberately modify that context. Writing DAX that is not just functional but correct and performant depends on controlling context — knowing what filters apply and how to change them. Mastery of context is what separates competent DAX from expert DAX.
Calculated columns, computed at load and stored per row, and calculated tables, generated by expression within the model. Measures remain its most important use — dynamic, context-responsive aggregations — but DAX also builds static columns and derived tables. Knowing which construct fits which need is part of using the language well.