DAX fundamentals — measures vs calculated columns — Power BI Module 4

DAX (Data Analysis Expressions) is the language of Power BI calculations. It looks like Excel formulas and behaves like SQL underneath. The learning curve is steep because DAX has a concept that has no obvious counterpart in either — context. Once context clicks, DAX becomes powerful. Until it does, DAX feels like trial-and-error.

Measure vs calculated column

Measure: stored as a formula. Evaluated when a visual asks for it, in the visual's filter context. Doesn't add data to the model. Example: Total Revenue = SUM(Sales[Amount]).
Calculated column: evaluated row-by-row at refresh time. Adds a column to the table. Example: Margin = Sales[Revenue] - Sales[Cost]. Takes up memory.
When to use which: Almost always a measure. Calculated columns only when you need a per-row classification that other things will GROUP BY or slice on.

Row context vs filter context

The two contexts

Row context: 'I am on this row of this table.' Created by calculated columns and by iterator functions (SUMX, AVERAGEX, FILTER). Lets you reference [Column] and get this row's value. Filter context: 'These rows are visible to me.' Created by visual filters, slicers, page filters, and CALCULATE modifications. SUM([Amount]) sums only the rows in the current filter context. Most DAX bugs are confusing one for the other.

The five most-written measures

text

// 1. Total
Total Sales = SUM(Sales[Amount])

// 2. Count of distinct
Customers = DISTINCTCOUNT(Sales[CustomerID])

// 3. Average per row, with iterator
Avg order size = AVERAGEX(Sales, Sales[Amount])

// 4. Ratio with safe division
Margin % = DIVIDE([Total Profit], [Total Sales])

// 5. Conditional sum
Enterprise sales = CALCULATE([Total Sales], Customer[Tier] = "Enterprise")

Master these five and you have the DAX for ~70% of analyst work. DIVIDE is preferred over '/' because it handles divide-by-zero gracefully.

SUM vs SUMX

SUM aggregates a column directly: SUM(Sales[Amount]). SUMX iterates a table and evaluates an expression for each row: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]). The latter is needed when the value isn't already a stored column. Same pattern for AVERAGE/AVERAGEX, COUNT/COUNTX, MIN/MINX, MAX/MAXX. The X functions are slower but more flexible.

Exercise

Build a Power BI report with a Sales fact and a Customer dimension. Write these five measures: Total Sales, Customer Count, Average Order, Margin %, Enterprise Sales. Drag each onto a card visual. Add a Region slicer — do all five measures respond correctly to the slicer? If one doesn't, investigate why.