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?
Keep track of your progress
The full study guide is free to read. Sign up free to practice with the question bank, track what you have read, review your mistakes, and highlight passages.