Day 6: DAX — Introduction

DAX (Data Analysis Expressions) is the formula language used in Power BI (and Power Pivot, Analysis Services) to create calculated columns, measures, and tables. You need DAX because most business questions cannot be answered by simply aggregating a column — they require conditional logic, time intelligence, ratios, rankings, or calculations that depend on the filter context. Examples: Year-over-Year growth, running totals, profit margin %, top N customers. DAX is what separates a basic Power BI report from a powerful analytical tool.

A Measure is a DAX formula that is evaluated dynamically based on the current filter context of a visual — it recalculates every time a slicer, filter, or interaction changes. Measures do not add a column to your table and do not consume model storage. Use measures for all aggregations and KPIs. A Calculated Column is a DAX formula evaluated row by row at data refresh time, adding a permanent new column to the table. It consumes model storage proportional to row count. Use calculated columns only for values that are truly row-level attributes needed for filtering or grouping — not for aggregations.

Total Revenue = SUM(Sales[Revenue]) This is the most basic DAX measure. SUM adds all values in the Revenue column of the Sales table, respecting whatever filter context is active — so if a visual filters by Region = "North", Total Revenue shows only North's revenue automatically. Never hard-code totals — always use SUM so the measure responds to filters.

CALCULATE is the most important and most used DAX function. It evaluates an expression in a modified filter context. Syntax: CALCULATE(expression, filter1, filter2, ...). Example: North Revenue = CALCULATE(SUM(Sales[Revenue]), Sales[Region] = "North") calculates total revenue but only for the North region, regardless of what other filters are active. CALCULATE can add filters, remove filters (using ALL), or replace existing filters. It is the foundation of almost all advanced DAX calculations.

Filter context is the set of filters that are active when a DAX expression is evaluated. It comes from: slicers, visual filters, row context in a table/matrix, cross-filtering from other visuals, and explicit filters in CALCULATE. For example: if a bar chart shows Revenue by Region, each bar evaluates Total Revenue with a filter context of {Region = "North"}, {Region = "South"} etc. Understanding filter context is essential for writing correct DAX — most DAX bugs are filter context errors where a measure does not respond to filters as expected.