DAX Fundamentals

Your first DAX measures

Simple explanation

Think of a spreadsheet formula. In Excel, you type =SUM(B2:B100) to add up a column. DAX works the same way — but instead of cell ranges, you reference column names: SUM(Sales[Revenue]).

The magic of DAX is filter context. If a slicer is set to “North Region”, SUM(Sales[Revenue]) automatically sums only North Region revenue. You write the formula once, and it adapts to whatever the user filters.

This module covers the essential aggregation functions and introduces the VAR/RETURN pattern that makes complex measures readable.

Essential aggregation functions

Function	What It Does	Example
`SUM(column)`	Adds all values in a column	`Total Revenue = SUM(Sales[Revenue])`
`AVERAGE(column)`	Calculates the mean	`Avg Order Value = AVERAGE(Sales[OrderTotal])`
`COUNT(column)`	Counts non-blank values	`Order Count = COUNT(Sales[OrderID])`
`COUNTROWS(table)`	Counts rows in a table	`Transaction Count = COUNTROWS(Sales)`
`DISTINCTCOUNT(column)`	Counts unique values	`Unique Customers = DISTINCTCOUNT(Sales[CustomerID])`
`MIN(column)`	Finds the smallest value	`First Sale = MIN(Sales[OrderDate])`
`MAX(column)`	Finds the largest value	`Latest Sale = MAX(Sales[OrderDate])`

Riley at Coastal Fresh (🛒) creates her first set of measures:

Total Revenue = SUM(Sales[Revenue])
Total Cost = SUM(Sales[Cost])
Profit = [Total Revenue] - [Total Cost]
Profit Margin = DIVIDE([Profit], [Total Revenue])

Notice how Profit references other measures — DAX measures can build on each other. And DIVIDE handles division by zero gracefully (returns BLANK instead of an error).

Exam tip: DIVIDE vs division operator

Always use DIVIDE(numerator, denominator) instead of numerator / denominator.

100 / 0 → Error
DIVIDE(100, 0) → BLANK (safe)
DIVIDE(100, 0, 0) → 0 (custom fallback)

The exam tests this. Any measure that divides should use DIVIDE.

Statistical functions

Function	What It Does
`MEDIAN(column)`	Middle value when sorted
`STDEV.P(column)`	Standard deviation (population)
`STDEV.S(column)`	Standard deviation (sample)
`PERCENTILE.INC(column, percentile)`	Value at a given percentile
`RANKX(table, expression)`	Ranks rows by an expression

Nadia at Prism Agency (📊) uses statistical measures for campaign analysis:

Median CPC = MEDIAN(Campaigns[CostPerClick])
Top 10% Threshold = PERCENTILE.INC(Campaigns[Revenue], 0.9)

Variables: the VAR/RETURN pattern

Variables make complex DAX readable and efficient. Define values with VAR, return the result with RETURN.

Profit Margin % = 
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalRevenue - TotalCost
RETURN
    DIVIDE(Profit, TotalRevenue)

Why variables matter:

Readability: Each step has a clear name
Performance: A variable is calculated once, even if referenced multiple times
Debugging: You can change RETURN to return any variable to check intermediate values

Kenji at Apex Manufacturing (🏭) writes a production efficiency measure:

Production Efficiency = 
VAR ActualOutput = SUM(Production[UnitsProduced])
VAR PlannedOutput = SUM(Production[TargetUnits])
VAR Efficiency = DIVIDE(ActualOutput, PlannedOutput)
RETURN
    Efficiency

Best practice: always use variables for multi-step measures

Even for simple two-step calculations, variables improve readability. Compare:

Without variables:

Margin = DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue]))

With variables:

Margin = 
VAR Rev = SUM(Sales[Revenue])
VAR Cost = SUM(Sales[Cost])
RETURN DIVIDE(Rev - Cost, Rev)

The second version is easier to read, debug, and modify. It also performs better because SUM(Sales[Revenue]) is only calculated once.

Understanding filter context

The same measure returns different values depending on the filter context:

Visual Context	Total Revenue Result
No filters	Sum of ALL revenue
Slicer: Region = “North”	Sum of North region revenue only
Matrix row: Product = “Avocado”	Sum of Avocado revenue only
Both: North + Avocado	Sum of Avocado revenue in North region

Dr. Ethan at Bayview Medical (🏥) creates Total Appointments = COUNTROWS(Appointments). When a doctor’s name is on the slicer, it shows that doctor’s appointments. When a department is selected, it shows that department’s count. Same measure — different context.

This is why measures are powerful: you write once, and the filter context handles the rest.

Knowledge check

Question

What's the difference between COUNT and COUNTROWS?

Click or press Enter to reveal answer

Answer

COUNT counts non-blank values in a specific column. COUNTROWS counts all rows in a table (regardless of blank values). COUNTROWS is generally preferred for counting records.

Click to flip back

Question

Why use DIVIDE instead of the / operator?

Click or press Enter to reveal answer

Answer

DIVIDE handles division by zero gracefully — returning BLANK (or a custom value) instead of throwing an error. Always use DIVIDE in measures that divide.

Click to flip back

Question

What does the VAR/RETURN pattern do?

Click or press Enter to reveal answer

Answer

VAR defines named variables that are calculated once. RETURN specifies the final result. Variables improve readability, performance (calculated once even if referenced multiple times), and debugging.

Click to flip back

Question

What is DISTINCTCOUNT?

Click or press Enter to reveal answer

Answer

Counts the number of unique (distinct) values in a column. Example: DISTINCTCOUNT(Sales[CustomerID]) returns how many unique customers made purchases.

Click to flip back

Knowledge Check

Riley creates `Total Revenue = SUM(Sales[Revenue])`. She places it in a matrix visual with Regions as rows. North shows $50K, South shows $30K, but the total shows $120K (which is correct — there are also East and West). What's happening?

Knowledge Check

What does `DIVIDE(100, 0)` return in DAX?

Next up: CALCULATE and Filter Context — the most powerful (and most tested) DAX function.