If you’ve ever opened Power BI or worked with an SSAS Tabular model and wondered what those formulas in the Measures pane actually do, this article is for you.

DAX (Data Analysis Expressions) is the formula language that powers calculations across Microsoft’s analytical stack. It allows you to define business logic such as revenue, profit margins, customer counts, and year-over-year growth.

At first glance, DAX can seem confusing. Measures return different values depending on where they’re used, functions like CALCULATE appear magical, and concepts such as filter context and row context are unlike anything most people have seen before.

The good news is that DAX becomes far simpler once you understand a few core concepts.

What You’ll Learn

By the end of this article, you’ll understand:

What makes DAX different from SQL
The difference between measures and calculated columns
How filter context affects every calculation
Why CALCULATE is the most important function in DAX
When to use iterators such as SUMX
How variables improve readability and performance

These foundations will help you understand most business reporting calculations in Power BI.

What Is DAX, and Why Does It Matter?

DAX is a functional language designed specifically for tabular data models.

The most common thing you’ll create in DAX is a measure.

A measure is a named calculation that is evaluated dynamically whenever a report is queried.

For example:

Total Sales = SUM(Sales[Amount])

Total Sales = SUM(Sales[Amount])

Unlike a SQL query that returns a fixed result set, a DAX measure recalculates every time the filter context changes.

If a user filters a report to:

January
Europe
Product Category = Bikes

then the measure returns sales for exactly those filters.

If the user changes any filter, the result changes as well. This dynamic behaviour is what makes DAX so powerful.

Measures vs Calculated Columns

Before learning the core concepts of DAX, it’s important to understand the distinction between measures and calculated columns. Many beginners confuse the two.

Calculated Columns

A calculated column is evaluated during data refresh.

For example:

Revenue = Sales[Quantity] * Sales[UnitPrice]

Revenue = Sales[Quantity] * Sales[UnitPrice]

The result is stored in the model for every row.

Quantity	Unit Price	Revenue
10	15	150
5	20	100

Because the values are physically stored, calculated columns increase model size. Don’t worry to much about this in the beginning. But as you grow more experienced, you will want to make sure your model is as small as possible.

Measures

Measures behave differently.

Total Sales = SUM(Sales[Amount])<br>

Total Sales = SUM(Sales[Amount])<br>

Measures are not stored in the model.

Instead, they are calculated whenever a report is queried.

The same measure might return:

January Sales
February Sales
Bike Sales
European Sales

depending on the current report filters.

Rule of Thumb

Use a calculated column when you need a value on every row.

Use a measure when you want to aggregate, summarise, or analyse data dynamically.

As a DAX developer, you’ll spend far more time creating measures than calculated columns. I personally create measures 99% of the time.

Filter Context: The Concept That Makes DAX Work

If there is one concept you should remember from this article, it is this:

Every DAX measure is evaluated inside a filter context.

Consider a simple measure:

Total Sales = SUM(Sales[Amount])<br>

Total Sales = SUM(Sales[Amount])<br>

At first glance, it appears to calculate one number.

But imagine a report containing:

A Product slicer
A Date slicer
A Region filter

Suppose the user selects:

Region = Europe
Product = Bikes
Year = 2025

Region = Europe
Product = Bikes
Year = 2025

DAX asks:

Which rows are visible under these filters?

Only those rows are included in the calculation. This means the exact same measure can produce hundreds of different results across a single report. Once filter context clicks, much of DAX suddenly makes sense.

A Useful Mental Model

Think of DAX calculations as a funnel:

Slicers
Filters
Relationships
Matrix Rows
Matrix Columns

↓

Filter Context

↓

DAX Measure

↓

Result

Every visual in Power BI creates its own filter context.

The measure doesn’t know whether it’s being displayed in:

A card
A chart
A matrix
A KPI

It only knows which rows are currently visible.

CALCULATE: The Most Important Function in DAX

Once you understand filter context, you’re ready for the most important DAX function:

CALCULATE()

CALCULATE()

The purpose of CALCULATE is simple:

Modify the filter context before evaluating an expression.

Its syntax is:

CALCULATE(<expression>,<filter1>,<filter2>)

CALCULATE(<expression>,<filter1>,<filter2>)

Example: Online Sales

Sales Online =CALCULATE(SUM(Sales[Amount]),Sales[Channel] = "Online")

Sales Online =CALCULATE(SUM(Sales[Amount]),Sales[Channel] = "Online")

This measure always evaluates sales using only rows where the channel is Online.

Example: Ignoring Product Filters

Sales All Products =
CALCULATE(
  SUM(Sales[Amount]),
  ALL(Product)
)

Sales All Products =
CALCULATE(
  SUM(Sales[Amount]),
  ALL(Product)
)

The ALL function removes product filters.

Even if a report is filtered to Bikes, this measure returns sales for all products.

This pattern is commonly used for percentage calculations:

% of Total Sales =
DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

% of Total Sales =
DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

Understanding how CALCULATE changes filter context is one of the biggest milestones in learning DAX.

Row Context: The Other Context in DAX

Filter context is only half the story.

DAX also has the concept of row context.

Row context exists whenever DAX evaluates data one row at a time.

Consider:

Revenue = Sales[Quantity] * Sales[UnitPrice]

Revenue = Sales[Quantity] * Sales[UnitPrice]

DAX must know which row it is currently evaluating.

That’s row context.

For example:

Quantity	Unit Price
10	15
5	20

DAX evaluates:

Row 1: 10 × 15 = 150
Row 2: 5 × 20 = 100

Filter Context vs Row Context

A simple way to remember the difference:

Filter Context asks:

Which rows are visible?

Row Context asks:

Which row am I currently evaluating?

This distinction becomes particularly important when working with iterator functions.

Iterators: SUMX, AVERAGEX, and Friends

Why SUM Isn’t Always Enough

A common beginner mistake is trying to write something like:

SUM(Sales[Quantity] * Sales[UnitPrice])

SUM(Sales[Quantity] * Sales[UnitPrice])

Unfortunately, this doesn’t work.

SUM can only aggregate a single column.

To perform row-by-row calculations before aggregation, DAX provides iterator functions.

The X Functions

The most common iterator functions are:

SUMX
AVERAGEX
MINX
MAXX
COUNTX

The “X” functions follow the same pattern:

Iterate through a table
Evaluate an expression for each row
Aggregate the results

SUMX in Practice

A classic business example is calculating revenue.

Total Revenue =
SUMX(
  Sales,
  Sales[Quantity] * Sales[UnitPrice]
)

Total Revenue =
SUMX(
  Sales,
  Sales[Quantity] * Sales[UnitPrice]
)

Here’s what happens:

DAX starts at the first row
Quantity is multiplied by Unit Price
The result is stored temporarily
The process repeats for every row
All results are summed together

This is why SUMX is one of the most important functions in DAX.

AVERAGEX Example

Avg Completed Order Value =
AVERAGEX(
  FILTER(Orders,Orders[Status] = "Completed"),
  Orders[OrderTotal]
)

Avg Completed Order Value =
AVERAGEX(
  FILTER(Orders,Orders[Status] = "Completed"),
  Orders[OrderTotal]
)

In this example:

FILTER returns only completed orders
AVERAGEX evaluates each row
The average value is returned

Variables: VAR and RETURN

As measures become larger, they can become difficult to read.
Variables solve this problem.

The syntax is:

Measure Name =

VAR VariableName = <expression>
RETURN <final expression>

Measure Name =

VAR VariableName = <expression>
RETURN <final expression>

A Practical Example

Here is an example without the use of variables:

DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

With variables:

% of Total Sales =

VAR CurrentSales =SUM(Sales[Amount])
VAR AllSales =
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
RETURN 
  DIVIDE(CurrentSales, AllSales)

% of Total Sales =

VAR CurrentSales =SUM(Sales[Amount])
VAR AllSales =
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
RETURN 
  DIVIDE(CurrentSales, AllSales)

This version is easier to understand, maintain, and debug. Don’t you agree?

Variables Capture Context

One important detail:

Variables are evaluated when they are defined, not when they are used.

This means a variable captures the filter context that exists at the point where the VAR statement executes.

For simple measures you won’t notice this often, but it’s an important concept when working with advanced DAX patterns.

Good Habits

Use variables to:

Create self-documenting measures
Avoid repeating logic
Improve readability
Simplify debugging

Instead of:

VAR X

VAR X

prefer:

VAR CurrentMonthSales

VAR CurrentMonthSales

Future-you will be grateful.

Common Beginner Mistakes

Learning DAX often means getting calculations that are technically valid but return unexpected results.

Here are some common pitfalls.

Mistake #1: Ignoring Filter Context

Beginners often expect a measure to return the same value everywhere.

Total Sales =

SUM(Sales[Amount])

Total Sales =

SUM(Sales[Amount])

This measure changes based on the current filters.

That’s normal.

Mistake #2: Using SUM Instead of SUMX

Incorrect:

SUM(Sales[Quantity] * Sales[UnitPrice])<br>

SUM(Sales[Quantity] * Sales[UnitPrice])<br>

Correct:

SUMX(Sales,Sales[Quantity] * Sales[UnitPrice])

SUMX(Sales,Sales[Quantity] * Sales[UnitPrice])

When a row-by-row calculation is required, use an iterator.

Mistake #3: Avoiding Variables

This:

DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

DIVIDE(
  SUM(Sales[Amount]),
  CALCULATE(
    SUM(Sales[Amount]),
    ALL(Product)
  )
)

works.

But this:

VAR CurrentSales =

SUM(Sales[Amount])
VAR TotalSales =

CALCULATE(
  SUM(Sales[Amount]),
  ALL(Product)
)
RETURN DIVIDE(CurrentSales, TotalSales)

VAR CurrentSales =

SUM(Sales[Amount])
VAR TotalSales =

CALCULATE(
  SUM(Sales[Amount]),
  ALL(Product)
)
RETURN DIVIDE(CurrentSales, TotalSales)

is significantly easier to maintain.

Putting It All Together

Let’s combine everything:

Revenue Per Active Customer =

VAR TotalRevenue =

SUMX(Sales,Sales[Quantity] * Sales[UnitPrice])

VAR ActiveCustomers =

CALCULATE(
  DISTINCTCOUNT(Sales[CustomerID]),
  Sales[Status] = "Active"
)

RETURN DIVIDE(TotalRevenue,ActiveCustomers)

Revenue Per Active Customer =

VAR TotalRevenue =

SUMX(Sales,Sales[Quantity] * Sales[UnitPrice])

VAR ActiveCustomers =

CALCULATE(
  DISTINCTCOUNT(Sales[CustomerID]),
  Sales[Status] = "Active"
)

RETURN DIVIDE(TotalRevenue,ActiveCustomers)

This measure demonstrates all three concepts:

SUMX creates row context
CALCULATE modifies filter context
VAR improves readability

These concepts aren’t separate topics—they work together constantly in real-world DAX development.

Key Takeaways

If you’re just starting with DAX, focus on mastering these four ideas before trying to memorise dozens of functions.

Measures are evaluated dynamically.
Filter context determines which rows are visible.
Iterators such as SUMX perform row-by-row calculations.
Variables make complex measures easier to understand.

Most DAX challenges eventually come back to one of these concepts.

Where to Go Next

Once you’re comfortable with the foundations covered in this article, I recommend learning:

Context Transition
Relationships and Filter Propagation
Time Intelligence Functions
CALCULATETABLE
Virtual Tables
Evaluation Context in Depth

Master those topics and you’ll be able to solve the vast majority of business reporting requirements you’ll encounter in Power BI.

If you found this useful, I write regularly about data engineering, analytics, and BI on this blog. Feel free to reach out or leave a comment below.

DAX for Beginners: Understanding Filter Context, CALCULATE, SUMX, and Variables

What You’ll Learn

What Is DAX, and Why Does It Matter?

Measures vs Calculated Columns

Calculated Columns

Measures

Rule of Thumb

Filter Context: The Concept That Makes DAX Work

A Useful Mental Model

CALCULATE: The Most Important Function in DAX

Example: Online Sales

Example: Ignoring Product Filters

Row Context: The Other Context in DAX

Filter Context vs Row Context

Iterators: SUMX, AVERAGEX, and Friends

Why SUM Isn’t Always Enough

The X Functions

SUMX in Practice

AVERAGEX Example

Variables: VAR and RETURN

A Practical Example

Variables Capture Context

Good Habits

Common Beginner Mistakes

Mistake #1: Ignoring Filter Context

Mistake #2: Using SUM Instead of SUMX

Mistake #3: Avoiding Variables

Putting It All Together

Key Takeaways

Where to Go Next

Leave a ReplyCancel Reply

TryHackMe: SOC Level 1 Path – Complete Walkthrough Overview

TryHackMe: TShark Challenge 1: Teamwork Walkthrough (SOC Level 1)

TryHackMe: Phishing Analysis Fundamentals Walkthrough (SOC Level 1)

TryHackMe: Snort Challenge – The Basics Walkthrough (SOC Level 1)

TryHackMe: Cyber Kill Chain Walkthrough (SOC Level 1)

What You’ll Learn

What Is DAX, and Why Does It Matter?

Measures vs Calculated Columns

Calculated Columns

Measures

Rule of Thumb

Filter Context: The Concept That Makes DAX Work

A Useful Mental Model

CALCULATE: The Most Important Function in DAX

Example: Online Sales

Example: Ignoring Product Filters

Row Context: The Other Context in DAX

Filter Context vs Row Context

Iterators: SUMX, AVERAGEX, and Friends

Why SUM Isn’t Always Enough

The X Functions

SUMX in Practice

AVERAGEX Example

Variables: VAR and RETURN

A Practical Example

Variables Capture Context

Good Habits

Common Beginner Mistakes

Mistake #1: Ignoring Filter Context

Mistake #2: Using SUM Instead of SUMX

Mistake #3: Avoiding Variables

Putting It All Together

Key Takeaways

Where to Go Next

Newsletter Updates

Related Posts

Leave a ReplyCancel Reply

Trending now