What's changed: Created PL-300 Chapter 2 (domain: Model the data): data model design (star schema, table/column properties, role-playing dimensions, cardinality/cross-filter direction, date table, calculated columns/tables), DAX calculations (measures vs calculated columns, CALCULATE, time intelligence, semi-additive, quick measures, calculation groups), and model performance optimization (remove unnecessary rows/columns, Performance Analyzer/DAX query view, reduce granularity).
2.2Create calculations with DAX
Understand measures vs calculated columns, aggregation measures, CALCULATE, time intelligence, statistical/semi-additive, quick measures, and calculation groups.
DAX is the language that adds calculations to a model. Start with measures vs calculated columns.
2.2.1Measures vs calculated columns
A calculated column computes and stores a per-row value in row context (increases model size; usable on slicers/axes); a measure computes aggregations dynamically in filter context (not stored; ideal for aggregating values). Aggregations (sum/average/ratio) should generally be measures.
2.2.2Aggregation and CALCULATE
Build basic aggregations with SUM/AVERAGE/COUNT. CALCULATE is DAX’s core function that modifies filter context to aggregate, expressing "sales under a condition" or "year-over-year" (e.g., CALCULATE(SUM(Sales[Amount]), Sales[Region]="East")). DAX also handles statistical functions (stdev/median) and semi-additive measures that cannot simply be summed (e.g., inventory balance).
2.2.3Time intelligence, quick measures, calculation groups
Time intelligence (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) concisely expresses period comparisons given a date table. Quick measures generate templated DAX from the GUI, helpful for learning. Calculation groups let you define calculation items once and reuse them across many measures (e.g., YTD/PY/YoY), preventing measure explosion (created in Tabular Editor, etc.).
Cues: "store per-row, use on axis" = calculated column. "aggregate dynamically" = measure. "aggregate by changing filter context" = CALCULATE. "YTD/same period last year" = time intelligence (needs date table). "reuse the same time calc across many measures" = calculation groups. "balance you cannot sum" = semi-additive measure.
Watch the mix-ups: (1) Aggregate with measures (not calculated columns). (2) CALCULATE differs from SUM etc. (it changes context). (3) Time intelligence needs a date table. (4) Calculation groups reuse calculation items, not a replacement for a single measure.
2.2.4Section summary
- Calculated column=per-row stored / measure=dynamic aggregation; aggregate via measures
- CALCULATE=modify filter context; time intelligence needs a date table
- Calculation groups reuse time calcs and prevent measure explosion
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. You want to store a category value per row for use on slicers/axes. Best?
Q2. You want to aggregate total sales dynamically per filters/slicers. Best?
Q3. You want to change filter context during aggregation, e.g., "sales limited to the East region". Best DAX function?
Q4. You want to compute YoY and YTD concisely. Which prerequisite-and-means is correct?
Q5. You want to reuse "YTD/PY/YoY" across many measures and limit the number of measures. Best?