What is DAX in Power BI: A Practical Guide for Beginners (with Examples)

TL;DR: DAX (Data Analysis Expressions) is Power BI’s formula language for creating calculations and metrics. If you’re coming from Excel, it’ll feel familiar but with superpowers. This post teaches you the essentials: what it is, what it’s for, the 5 functions you need to master, and the mistakes we all make when starting out.

---

What is DAX (and What Does It Stand For)

DAX stands for Data Analysis Expressions. It’s the formula language Power BI uses to perform calculations on your data.

If you’ve used Excel, you already know the concept: you write a formula like =SUM(A1:A10) and get a result. DAX works the same way, but designed to work with relational data models and millions of rows.

Where DAX is used:

• Power BI (Desktop and Service)
• Power Pivot (the Excel add-in)
• SQL Server Analysis Services (SSAS) Tabular
• Azure Analysis Services

This post focuses on Power BI, but what you learn applies to all of them.

An Example to Get Started

In Excel you’d write:

=SUM(Sales[Amount])

In DAX you write:

Total Sales = SUM(Sales[Amount])

Similar, right? The difference is what happens underneath: DAX understands relationships between tables, responds to filters dynamically, and scales to millions of records.

---

DAX vs Power Query: The Most Common Confusion

This confuses everyone at first. Power BI has two languages:

	Power Query (M)	DAX
What for	Transform and clean data	Calculate metrics and KPIs
When it runs	When loading/refreshing data	When viewing the report
Where you use it	Power Query Editor	Measures and calculated columns
Excel analogy	Excel’s Power Query	Cell formulas

The Simple Rule

• Power Query: Everything you do BEFORE data reaches the model

  • Clean column names
  • Filter unnecessary rows
  • Combine tables (merge/append)
  • Change data types

• DAX: Everything you calculate AFTER data is in the model

  • Totals, averages, counts
  • Comparisons (vs last year, vs budget)
  • Percentages and ratios
  • Business KPIs

Practical example:

• You have a “Date” column in text format → Power Query to convert it to date
• You want to calculate last month’s sales → DAX to create the measure

---

Measures vs Calculated Columns: When to Use Each

In DAX you can create two types of calculations:

Measures

• Calculated dynamically based on filter context
• Don’t take up memory (calculated on the fly)
• Change according to report filters

Total Sales = SUM(Sales[Amount])

Calculated Columns

• Calculated once when data refreshes
• Add a physical column to the table
• Value is fixed per row

Margin = Sales[Amount] - Sales[Cost]

The Golden Rule

Use measures whenever you can. Only use calculated columns when:

• You need the value to filter or segment (slicers)
• The calculation depends on values from the same row
• You need to use the result in a relationship

Why? Measures:

• Consume less memory
• Respond to filters (more flexible)
• Are easier to maintain

---

The 5 DAX Functions You Need to Master

You don’t need to know all 200+ DAX functions. These 5 categories cover 80% of use cases:

1. Aggregations: SUM, AVERAGE, COUNT

The most basic ones. Sum, average, or count values.

Total Sales = SUM(Sales[Amount])

Average Price = AVERAGE(Products[Price])

Number of Orders = COUNT(Sales[OrderID])

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Tip: DISTINCTCOUNT counts unique values, very useful for counting customers, products, etc.

2. CALCULATE: The King of DAX

The most important function. It lets you modify the filter context of a calculation.

// Total sales (ignoring category filter)
Sales All Categories =
CALCULATE(
    SUM(Sales[Amount]),
    ALL(Products[Category])
)

// Sales only from 2025
Sales 2025 =
CALCULATE(
    SUM(Sales[Amount]),
    Calendar[Year] = 2025
)

What does CALCULATE do?

1. Takes an expression (e.g., SUM(Sales[Amount]))
2. Modifies active filters according to the arguments
3. Evaluates the expression with the new filters

It’s confusing at first. We’ll come back to this with examples later.

3. FILTER and ALL: Context Control

ALL removes filters:

// Percentage of total (ignores visual filters)
% of Total =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

FILTER applies custom conditions:

// Sales of premium products (price > 100)
Premium Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Products, Products[Price] > 100)
)

4. IF and SWITCH: Conditionals

IF for simple conditions:

Status =
IF(
    Sales[Amount] > 1000,
    "Large",
    "Small"
)

SWITCH for multiple conditions (cleaner than nested IFs):

Segment =
SWITCH(
    TRUE(),
    Sales[Amount] > 10000, "Enterprise",
    Sales[Amount] > 1000, "Medium",
    Sales[Amount] > 100, "Small",
    "Micro"
)

Tip: SWITCH(TRUE(), ...) is the most useful pattern. Evaluates conditions in order and returns the first TRUE.

5. RELATED and RELATEDTABLE: Navigation Between Tables

When you need data from a related table:

RELATED (many to one):

// In Sales table, get the product's category
Category = RELATED(Products[Category])

RELATEDTABLE (one to many):

// In Customers table, count their orders
Customer Orders = COUNTROWS(RELATEDTABLE(Sales))

---

Your First DAX Measure: 3 Practical Examples

Let’s create the most common measures step by step.

Example 1: Total Sales

Total Sales = SUM(Sales[Amount])

Simple. Sums all amounts. If there’s a date filter in the report, it only sums that period.

Example 2: Last Year’s Sales

Last Year Sales =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(Calendar[Date])
)

What it does:

1. Takes the [Total Sales] measure
2. Changes the date filter to the same period last year
3. Calculates

Requirement: You need a calendar table marked as “Date table” in Power BI.

Example 3: Percentage of Total

% of Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Sales)),
    0
)

What it does:

1. Divides filtered sales by total sales
2. ALL(Sales) removes all filters from the Sales table
3. The third argument (0) is the value if division by zero

Why DIVIDE and not ”/”?

// ❌ This errors if denominator is 0
Ratio = [Sales] / [Target]

// ✅ This returns 0 (or whatever you specify) if division by zero
Ratio = DIVIDE([Sales], [Target], 0)

---

Common Beginner Mistakes (and How to Avoid Them)

These are the mistakes we all make when starting out:

Mistake 1: Using Calculated Columns for Everything

❌ Bad:

// Calculated column in Sales table
Running Total =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(ALL(Sales), Sales[Date] <= EARLIER(Sales[Date]))
)

✅ Good: Use a measure

Running Total =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Calendar),
        Calendar[Date] <= MAX(Calendar[Date])
    )
)

Calculated columns are evaluated row by row when loading data. Slow and memory-consuming.

Mistake 2: Not Understanding Filter Context

Filter context is everything in DAX. It’s what determines which rows are included in the calculation.

Total Sales = SUM(Sales[Amount])

This measure returns different values depending on:

• The year selected in a slicer
• The product category on a chart axis
• Page or report filters

The mistake: Assuming a measure always returns the same value.

Mistake 3: Dividing Without DIVIDE()

// ❌ Runtime error if Target = 0
Achievement = [Sales] / [Target]

// ✅ Safe
Achievement = DIVIDE([Sales], [Target], 0)

Mistake 4: Ignoring Model Relationships

DAX works on a relational model. If your tables aren’t properly related, measures won’t work as expected.

Checklist:

• Is there a relationship between Sales and Products?
• Is the relationship on the correct side (1 to many)?
• Is it active?

Mistake 5: Overcomplicating When You Don’t Need To

❌ Overcomplicated:

Total =
CALCULATE(
    SUMX(
        FILTER(Sales, Sales[Amount] > 0),
        Sales[Amount]
    )
)

✅ Simple:

Total = SUM(Sales[Amount])

Start simple. Add complexity only when necessary.

---

DAX and Copilot: The 2026 Shortcut

If you have access to Power BI Copilot, you can generate DAX with natural language:

Prompt: "Create a measure that calculates sales from the same period last year"

Copilot generates:

Last Year Sales =
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Calendar[Date])
)

My advice: Use Copilot to speed up, but understand what it generates. If you don’t understand the code, you won’t be able to debug it when it fails.

Copilot is especially useful for:

• Explaining existing DAX (“what does this measure do?”)
• Generating the first draft of complex measures
• Learning new patterns

But it has limitations: it sometimes generates incorrect code, especially with variables and complex contexts. Always review.

---

Resources for Continued Learning

Free

DAX.do Online playground to test DAX without installing anything. Perfect for experimenting.

DAX Guide Complete reference of all DAX functions with examples. By SQLBI.

SQLBI Marco Russo and Alberto Ferrari are THE DAX reference. Their articles are gold.

Books

“The Definitive Guide to DAX” (Marco Russo, Alberto Ferrari) The book. 700+ pages. Not for total beginners, but it’s the bible when you want to go deeper.

YouTube Channels

• SQLBI - Technical videos from the book’s authors
• Guy in a Cube - More accessible, good tutorials
• Curbal - Ruth Pozuelo, excellent content

Cheat Sheet

The functions you’ll use most:

Function	What for
SUM	Sum values
AVERAGE	Average
COUNT / COUNTA	Count rows
DISTINCTCOUNT	Count unique values
CALCULATE	Modify filter context
FILTER	Filter tables
ALL	Remove filters
IF	Simple conditional
SWITCH	Multiple conditional
DIVIDE	Safe division
RELATED	Get value from related table
DATEADD	Shift dates
SAMEPERIODLASTYEAR	Same period last year
TOTALYTD	Year-to-date
FORMAT	Format values

---

Conclusion: Where to Start

If you’re starting with DAX:

1. Understand the difference between DAX and Power Query
2. Use measures, not calculated columns (with exceptions)
3. Master CALCULATE - it’s 50% of DAX
4. Practice with real examples from your work
5. Use DIVIDE() always for divisions

DAX has a learning curve, especially filter context. But once it “clicks”, it becomes an incredibly powerful tool.

My recommendation: don’t try to learn everything at once. Start with SUM, AVERAGE, COUNT. Then add CALCULATE. Then explore time intelligence. Step by step.

---