Introduction
DAX, or Data Analysis Expressions, is Microsoft's proprietary formula language designed for analysis tools like Power BI, Excel Power Pivot, and Analysis Services. Unlike classic Excel, DAX shines in relational data models, enabling dynamic calculations based on table relationships.
Why master DAX in 2026? In a data-driven world, analysts who master DAX turn raw data into actionable insights, supercharging decision-making. Picture analyzing sales by region and period at a glance—no complex SQL queries needed. This beginner tutorial focuses on pure theory: core concepts, evaluation contexts, and best practices. No code here, just the solid foundation to use DAX intuitively.
With 15 years of experience, I'll guide you from basics to subtle nuances like a mentor. By the end, you'll think in DAX like a pro.
Prerequisites
- Power BI Desktop (free) or Excel 2016+ with Power Pivot enabled.
- Basic Excel skills: sums, averages, filters.
- A simple data model (e.g., Sales table + Calendar table).
- No programming required.
1. The Foundations: What is DAX?
DAX is a functional, declarative language, not procedural. You describe what to calculate, not how. Analogy: like saying 'calculate total sales' instead of manually iterating over rows.
Key elements:
- Formulas: Measures (dynamic aggregations) or Calculated columns (row-level values).
- Syntax:
Name = Function(Arguments). Real-world example: a measure for total sales uses an aggregation function on a column.
Excel differences: DAX handles relationships between tables. If 'Sales' links to 'Products', DAX automatically propagates filters. Case study: In a sales report, DAX recalculates in real time based on selected slicers.
2. The Data Model: The Foundation of Everything
DAX relies on a star schema: Fact table (e.g., Sales) at the center, Dimension tables (Customers, Products, Dates) around it.
Relationships: Unidirectional (1:N), like '1 Customer → Many Sales'. DAX follows these paths for filtering.
Real-world example: Sales table (DateID, Amount) linked to Calendar (DateID, Year, Month). A 'Sales by Year' measure aggregates via the Date → Sales relationship.
Checklist for a solid model:
- Unique keys on dimensions.
- No circular loops.
- Consistent granularity (e.g., daily sales).
Without a strong model, DAX delivers wrong results—it's 80% of success.
3. Measures vs Calculated Columns
Measures: Dynamic aggregated calculations, evaluated in the visual's context. Perfect for totals, averages. E.g., 'Total Sales = SUM(Sales[Amount])'—adapts to filters.
Calculated columns: Fixed values per row, stored in memory. For flags or row-level ratios. E.g., 'Category = IF(Products[Price] > 100, "Premium", "Standard")'.
When to choose?
| Criterion | Measure | Calculated Column |
|---|---|---|
| ----------- | --------- | ------------------- |
| Dynamic | ✅ | ❌ |
| Storage | Light | Heavier |
| Performance | Better | Slower on big data |
Example: Margin ratio per product → Column; Total margin → Measure. Newbie mistake: Using columns for everything bloats the model.
4. Evaluation Contexts: The Heart of DAX
DAX evaluates in 3 nested contexts:
- Query Context: Report filters (slicers, page filters).
- Filter Context: Column filters (e.g., for a table).
- Row Context: Current row (in iterators like SUMX).
Analogy: Query = big-picture zoom; Filter = table magnifying glass; Row = row-level microscope.
Real-world example: 'Sales by Region' measure → Query filters 'Region=Paris' applies Filter Context.
Iterators (X functions): Add Row Context. SUMX iterates rows, applies formula, then sums. Without: direct aggregation.
Master this for debugging: Use DAX Studio to visualize contexts.
5. Essential Functions and Time Intelligence
Basic aggregations: SUM, AVERAGE, COUNT, MIN/MAX—with filtered variants (SUMX for iteration).
Logic: IF, SWITCH, AND/OR—for conditions.
Time intelligence: TOTALYTD (year-to-date), SAMEPERIODLASTYEAR. Requires a marked Dates table.
Example: 'Sales YTD = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])'—cumulative from January to selection.
Learning framework:
- Master simple aggregations.
- Add iterators for row-level work.
- Time intelligence for trends.
These cover 90% of business use cases.
Best Practices
- Always use descriptive names: 'Sales incl. VAT France 2024' > 'Measure1'.
- Prefer measures over columns: Saves memory and boosts performance.
- Mark the Dates table: Essential for time intelligence (Mark as Date Table).
- Test iteratively: Create a simple card visual to validate each measure.
- Use variables:
VAR FilteredSales = CALCULATE(...) RETURN FilteredSales * 1.2—readable and performant.
Common Mistakes to Avoid
- Ignoring contexts: Measure without CALCULATE won't propagate external filters → wrong totals.
- Poorly related model: Bidirectional relationships cause double-counting (e.g., inflated totals).
- Unnecessary iterators: SUM instead of SUMX on simple columns = added complexity.
- Missing Dates table: Time intelligence returns blanks or errors.
Next Steps
Practice with DAX Studio (free) for debugging. Read 'The Definitive Guide to DAX' by Marco Russo.
Check out our Learni trainings on Power BI and DAX: hands-on workshops for pros.
Resources: Microsoft DAX documentation, Power BI community.