DAX Fundamentals

What is DAX?

DAX stands for Data Analysis Expressions. It is the formula language used in Power BI (and also in SQL Server Analysis Services and Excel Power Pivot) to create custom calculations — from simple sums to complex business logic.

If you've used Excel formulas, DAX will feel somewhat familiar. Both use functions, operators, and references. However, DAX operates on tables and columns rather than individual cells, and it introduces powerful concepts like filter context and row context that don't exist in Excel.

Where DAX Is Used

Product	DAX Support
Power BI Desktop	Full support — measures, calculated columns, calculated tables
Power BI Service	Measures can be created via the web interface (limited)
SQL Server Analysis Services (SSAS)	Full support in Tabular models
Excel Power Pivot	Full support
Azure Analysis Services	Full support

DAX vs Excel Formulas

Feature	Excel Formulas	DAX
Reference style	Cell references (A1, B2:B10)	Column references (Table[Column])
Operates on	Individual cells or ranges	Entire columns and tables
Recalculation	When referenced cells change	When filters change or data refreshes
Context	No concept of evaluation context	Row context and filter context are fundamental
Functions	~500 functions	~350 functions (many overlap with Excel)
Return type	Single value	Single value (measures) or entire table (table functions)

DAX Syntax

Column References

Columns are always referenced using the format 'Table Name'[Column Name]. If the table name has no spaces, the quotes are optional:

// With quotes (always works)
'Fact Sales'[Total Amount]

// Without quotes (only when table name has no spaces)
FactSales[TotalAmount]

// Best practice: always include the table name for clarity
DimProduct[ProductName]

Measure References

Measures are referenced using just square brackets: [Measure Name]. You can optionally include the table name where the measure is defined:

// Measure reference (short form)
[Total Revenue]

// Measure reference with table (explicit form)
'Sales Measures'[Total Revenue]

Operators

Category	Operator	Description	Example
Arithmetic	`+`	Addition	`[Price] + [Tax]`
	`-`	Subtraction	`[Revenue] - [Cost]`
	`*`	Multiplication	`[Qty] * [UnitPrice]`
	`/`	Division	`[Revenue] / [Units]`
	`^`	Exponentiation	`[Value] ^ 2`
Comparison	`=`	Equals	`[Country] = "India"`
	`<>`	Not equal	`[Status] <> "Cancelled"`
	`>`	Greater than	`[Amount] > 1000`
	`>=`	Greater than or equal	`[Age] >= 18`
	`<`	Less than	`[Discount] < 0.5`
	`<=`	Less than or equal	`[Stock] <= 10`
Text	`&`	Concatenation	`[First] & " " & [Last]`
Logical	`&&`	Logical AND	`[Age] >= 18 && [Age] <= 65`
	`\|\|`	Logical OR	`[Dept] = "Sales" \|\| [Dept] = "Marketing"`
	`NOT`	Logical NOT	`NOT [IsActive]`
Parentheses	`( )`	Grouping	`([Price] - [Cost]) * [Qty]`

Comments

// Single-line comment — everything after // is ignored

/*
   Multi-line comment
   Useful for longer explanations
   or temporarily disabling parts of a formula
*/

Total Revenue =
// This measure calculates total revenue
SUM(FactSales[TotalAmount])  // Sum all sales amounts

Line Breaks and Formatting

DAX formulas can span multiple lines. Good formatting makes complex formulas readable:

// Hard to read
Profit Margin = DIVIDE(SUM(FactSales[TotalAmount])-SUM(FactSales[TotalCost]),SUM(FactSales[TotalAmount]),0)

// Easy to read
Profit Margin =
DIVIDE(
    SUM(FactSales[TotalAmount]) - SUM(FactSales[TotalCost]),
    SUM(FactSales[TotalAmount]),
    0
)

Calculated Columns

A calculated column is a new column you add to an existing table. Its value is computed for each row during data refresh and stored in the model.

Creating a Calculated Column

Switch to Data View (the table icon in the left sidebar)
Select the table where you want to add the column
Go to Table tools → New Column
Type your DAX expression in the formula bar

Row Context

Calculated columns evaluate in row context. This means the formula has access to the values of each row in the table, and it computes a result for every single row.

Think of it like an Excel formula in a table column — it calculates for each row independently.

Examples of Calculated Columns

Example 1: Full Name

Full Name = Employees[FirstName] & " " & Employees[LastName]

FirstName	LastName	Full Name
Alice	Smith	Alice Smith
Bob	Kumar	Bob Kumar
Charlie	Patel	Charlie Patel

Example 2: Age Calculation

Age = DATEDIFF(Employees[BirthDate], TODAY(), YEAR)

EmployeeName	BirthDate	Age
Alice Smith	1990-05-15	35
Bob Kumar	1985-11-22	40
Charlie Patel	1998-03-08	28

Example 3: Categorization (Price Tier)

Price Tier =
SWITCH(
    TRUE(),
    Products[ListPrice] < 50, "Budget",
    Products[ListPrice] < 200, "Mid-Range",
    Products[ListPrice] < 500, "Premium",
    "Luxury"
)

ProductName	ListPrice	Price Tier
Basic Widget	29.99	Budget
Pro Widget	149.99	Mid-Range
Ultra Widget	399.99	Premium
Enterprise Widget	999.99	Luxury

Example 4: Profit Calculation (Row-Level)

Line Profit = FactSales[TotalAmount] - FactSales[TotalCost]

Example 5: Conditional Column

Is High Value =
IF(
    FactSales[TotalAmount] > 500,
    "High Value",
    "Standard"
)

When to Use Calculated Columns

The result will be used as a slicer, filter, axis, or legend in a visual
You need to group or categorize rows (e.g., age groups, price tiers)
You need the column for a relationship between tables
The calculation depends on row-level values from the same table

When NOT to Use Calculated Columns

For aggregations (SUM, AVERAGE, COUNT) — use a measure instead
When the value should change based on filters — use a measure
When the column would just duplicate values from a related table — use a relationship instead

Measures

A measure is a DAX formula that calculates a value dynamically at query time. Unlike calculated columns, measures are not stored row-by-row — they are computed on the fly based on the current filter context.

Creating a Measure

Select a table in the Data pane (ideally your Measures table)
Go to Home → New Measure
Type your DAX expression in the formula bar

Total Revenue = SUM(FactSales[TotalAmount])

Implicit vs Explicit Measures

Type	Description	Example
Implicit	Created automatically when you drag a numeric column into a visual	Dragging TotalAmount into a visual — Power BI auto-sums it
Explicit	Created by writing a DAX formula	`Total Revenue = SUM(FactSales[TotalAmount])`

Why Explicit Measures Are Preferred

Reason	Explanation
Control	You define exactly how the aggregation works
Reusability	One measure can be used across multiple visuals consistently
Complex logic	You can add conditions, time intelligence, and formatting
Documentation	Named measures with descriptions are self-documenting
Referenceability	Other measures can reference explicit measures; they cannot reference implicit ones
Format control	You set the format once on the measure definition

Measure Examples

// Simple aggregation
Total Revenue = SUM(FactSales[TotalAmount])

// Safe division
Average Order Value =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    DISTINCTCOUNT(FactSales[OrderID]),
    0
)

// Conditional aggregation
High Value Sales =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FactSales[TotalAmount] > 500
)

// Percentage
Revenue Share =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    CALCULATE(SUM(FactSales[TotalAmount]), ALL(FactSales))
)

Aggregation Functions

These are the most commonly used DAX functions. Each takes a column and returns a single aggregated value.

SUM

Adds up all values in a column.

Total Revenue = SUM(FactSales[TotalAmount])

AVERAGE

Returns the arithmetic mean of all values in a column.

Avg Sale Amount = AVERAGE(FactSales[TotalAmount])

MIN and MAX

Return the smallest or largest value in a column.

Lowest Price = MIN(Products[ListPrice])
Highest Price = MAX(Products[ListPrice])

COUNT

Counts the number of non-blank numeric values in a column.

Orders With Amount = COUNT(FactSales[TotalAmount])

COUNTA

Counts the number of non-blank values in a column (works with any data type, including text).

Products With Names = COUNTA(Products[ProductName])

COUNTBLANK

Counts the number of blank values in a column.

Missing Emails = COUNTBLANK(Customers[Email])

COUNTROWS

Counts the number of rows in a table (or table expression).

Total Transactions = COUNTROWS(FactSales)

DISTINCTCOUNT

Counts the number of unique, non-blank values in a column.

Unique Customers = DISTINCTCOUNT(FactSales[CustomerKey])

Aggregation Functions Summary

Function	Counts/Aggregates	Blank Handling	Data Types
`SUM`	Adds numeric values	Ignores blanks	Numeric only
`AVERAGE`	Arithmetic mean	Ignores blanks	Numeric only
`MIN`	Smallest value	Ignores blanks	Numeric, Date, Text
`MAX`	Largest value	Ignores blanks	Numeric, Date, Text
`COUNT`	Count of non-blank values	Ignores blanks	Numeric only
`COUNTA`	Count of non-blank values	Ignores blanks	Any type
`COUNTBLANK`	Count of blank values	Counts blanks specifically	Any type
`COUNTROWS`	Count of rows	Counts all rows including blanks	Table
`DISTINCTCOUNT`	Count of unique values	Ignores blanks	Any type

The X Functions (Iterators)

Iterator functions (also called "X functions") perform row-by-row calculations before aggregating. They iterate over a table, evaluate an expression for each row, and then aggregate the results.

How Iterators Differ from Simple Aggregators

// Simple aggregator: SUM of a stored column
Total Revenue v1 = SUM(FactSales[TotalAmount])

// Iterator: Calculate per-row, then SUM
Total Revenue v2 = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])

If the TotalAmount column already exists, both return the same result. But SUMX is essential when you need to compute a value per row that doesn't exist as a stored column.

SUMX

Iterates over a table, evaluates an expression for each row, and sums the results.

// Syntax: SUMX(Table, Expression)

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

// Weighted average price
Weighted Avg Price =
DIVIDE(
    SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice]),
    SUM(FactSales[Quantity])
)

AVERAGEX

Iterates over a table, evaluates an expression for each row, and returns the average.

// Average revenue per product (across all transactions for that product)
Avg Product Revenue =
AVERAGEX(
    DimProduct,
    CALCULATE(SUM(FactSales[TotalAmount]))
)

MINX and MAXX

Iterate and return the minimum or maximum of the evaluated expression.

// Cheapest line item amount in current filter
Smallest Line Amount = MINX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])

// Most expensive product's list price
Max Product Price = MAXX(DimProduct, DimProduct[ListPrice])

COUNTX

Iterates over a table and counts rows where the expression is non-blank.

// Count products that have at least one sale
Products Sold =
COUNTX(
    DimProduct,
    IF(
        CALCULATE(COUNTROWS(FactSales)) > 0,
        1,
        BLANK()
    )
)

RANKX

Assigns a rank to each row based on an expression.

// Rank products by total revenue
Product Revenue Rank =
RANKX(
    ALL(DimProduct[ProductName]),
    CALCULATE(SUM(FactSales[TotalAmount])),
    ,
    DESC,
    DENSE
)

RANKX Parameters:

Parameter	Description
Table	The table to iterate over
Expression	The value to rank by
Value (optional)	The specific value to find rank for
Order	`ASC` (ascending) or `DESC` (descending, default)
Ties	`SKIP` (1,2,2,4) or `DENSE` (1,2,2,3)

When to Use Iterators vs Simple Aggregators

Scenario	Use
Sum a single existing column	`SUM(Table[Column])`
Sum a calculation across rows	`SUMX(Table, expression)`
Average of a column	`AVERAGE(Table[Column])`
Average of a measure per category	`AVERAGEX(CategoryTable, [Measure])`
Count rows	`COUNTROWS(Table)`
Count rows meeting a condition	`COUNTX(Table, IF(condition, 1, BLANK()))`

Logical Functions

Logical functions let you implement conditional logic in DAX.

IF

Returns one value if a condition is true, another if false.

// Syntax: IF(Condition, TrueResult, FalseResult)

Sales Status =
IF(
    [Total Revenue] > 100000,
    "Above Target",
    "Below Target"
)

// Nested IF
Performance Rating =
IF(
    [Total Revenue] > 200000, "Excellent",
    IF(
        [Total Revenue] > 100000, "Good",
        IF(
            [Total Revenue] > 50000, "Average",
            "Below Average"
        )
    )
)

SWITCH

A cleaner alternative to nested IF statements.

// Syntax: SWITCH(Expression, Value1, Result1, Value2, Result2, ..., DefaultResult)

Region Name =
SWITCH(
    Stores[RegionCode],
    "N", "North",
    "S", "South",
    "E", "East",
    "W", "West",
    "Unknown"
)

// SWITCH with TRUE() for range conditions
Grade =
SWITCH(
    TRUE(),
    [Score] >= 90, "A",
    [Score] >= 80, "B",
    [Score] >= 70, "C",
    [Score] >= 60, "D",
    "F"
)

AND, OR, NOT

// AND — both conditions must be true
Eligible = AND([Age] >= 18, [HasLicense] = TRUE())

// Equivalent using && operator
Eligible = [Age] >= 18 && [HasLicense] = TRUE()

// OR — at least one condition must be true
Discount Eligible = OR([IsMember] = TRUE(), [OrderAmount] > 500)

// Equivalent using || operator
Discount Eligible = [IsMember] = TRUE() || [OrderAmount] > 500

// NOT — reverses a boolean
Is Inactive = NOT(Employees[IsActive])

IFERROR

Returns an alternative value if the expression results in an error.

Safe Ratio = IFERROR([Revenue] / [Cost], 0)

Note: DIVIDE() is preferred over IFERROR with division because DIVIDE handles division by zero natively and is more performant.

ISBLANK and ISEMPTY

// ISBLANK — checks if a value is blank
Has Email = NOT(ISBLANK(Customers[Email]))

// ISEMPTY — checks if a table has no rows
Has Sales =
IF(
    ISEMPTY(FILTER(FactSales, FactSales[ProductKey] = Products[ProductKey])),
    "No Sales",
    "Has Sales"
)

Text Functions

Text functions manipulate string values.

Common Text Functions Reference

Function	Syntax	Description	Example	Result
`CONCATENATE`	`CONCATENATE(text1, text2)`	Joins two text strings	`CONCATENATE("Hello", " World")`	Hello World
`CONCATENATEX`	`CONCATENATEX(table, expr, delimiter)`	Joins text from all rows	`CONCATENATEX(Products, [Name], ", ")`	Widget, Gadget, Tool
`LEFT`	`LEFT(text, count)`	Returns leftmost characters	`LEFT("Power BI", 5)`	Power
`RIGHT`	`RIGHT(text, count)`	Returns rightmost characters	`RIGHT("Power BI", 2)`	BI
`MID`	`MID(text, start, count)`	Returns middle characters	`MID("Power BI", 7, 2)`	BI
`LEN`	`LEN(text)`	Returns length of text	`LEN("Power BI")`	8
`UPPER`	`UPPER(text)`	Converts to uppercase	`UPPER("power bi")`	POWER BI
`LOWER`	`LOWER(text)`	Converts to lowercase	`LOWER("Power BI")`	power bi
`PROPER`	`PROPER(text)`	Capitalizes first letter of each word	`PROPER("power bi desktop")`	Power Bi Desktop
`TRIM`	`TRIM(text)`	Removes leading/trailing spaces	`TRIM(" hello ")`	hello
`SUBSTITUTE`	`SUBSTITUTE(text, old, new)`	Replaces occurrences of text	`SUBSTITUTE("2026-01-15", "-", "/")`	2026/01/15
`SEARCH`	`SEARCH(find, within)`	Finds position (case-insensitive)	`SEARCH("bi", "Power BI")`	7
`FIND`	`FIND(find, within)`	Finds position (case-sensitive)	`FIND("BI", "Power BI")`	7

FORMAT Function

FORMAT converts a value to text using a format string.

// Format numbers
Formatted Revenue = FORMAT([Total Revenue], "$#,##0.00")
// Result: $1,234,567.89

// Format percentages
Formatted Growth = FORMAT([YoY Growth], "0.0%")
// Result: 12.5%

// Format dates
Formatted Date = FORMAT([OrderDate], "DD-MMM-YYYY")
// Result: 15-Jan-2026

// Common format strings
Month Name = FORMAT([Date], "MMMM")       // January
Short Month = FORMAT([Date], "MMM")        // Jan
Day Name = FORMAT([Date], "dddd")          // Thursday
Year Month = FORMAT([Date], "YYYY-MM")     // 2026-01

CONCATENATEX — Aggregating Text

CONCATENATEX is an iterator that concatenates text values from all rows of a table.

// List all product names for the current filter context, separated by commas
Product List =
CONCATENATEX(
    DimProduct,
    DimProduct[ProductName],
    ", ",
    DimProduct[ProductName], ASC
)
// Result: "Gadget A, Widget B, Widget C"

Date Functions

Date functions are essential for working with temporal data.

Common Date Functions

Function	Description	Example	Result
`TODAY()`	Current date	`TODAY()`	2026-03-24
`NOW()`	Current date and time	`NOW()`	2026-03-24 14:30:00
`YEAR(date)`	Extracts the year	`YEAR(DATE(2026,3,15))`	2026
`MONTH(date)`	Extracts the month	`MONTH(DATE(2026,3,15))`	3
`DAY(date)`	Extracts the day	`DAY(DATE(2026,3,15))`	15
`DATE(y, m, d)`	Creates a date value	`DATE(2026, 3, 15)`	2026-03-15
`DATEDIFF(start, end, interval)`	Difference between dates	`DATEDIFF(DATE(2025,1,1), DATE(2026,3,15), MONTH)`	14
`EOMONTH(date, months)`	End of month, offset by N months	`EOMONTH(DATE(2026,3,15), 0)`	2026-03-31
`WEEKDAY(date, type)`	Day of week (1-7)	`WEEKDAY(DATE(2026,3,15), 2)`	7 (Sunday)
`WEEKNUM(date, type)`	Week number of the year	`WEEKNUM(DATE(2026,3,15), 2)`	11

DATEDIFF Intervals

// DATEDIFF supports these intervals:
// SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

Days Since Hire =
DATEDIFF(Employees[HireDate], TODAY(), DAY)

Months of Service =
DATEDIFF(Employees[HireDate], TODAY(), MONTH)

Years of Service =
DATEDIFF(Employees[HireDate], TODAY(), YEAR)

Practical Date Examples

// Is this year's data?
Is Current Year = YEAR(FactSales[OrderDate]) = YEAR(TODAY())

// First day of current month
First of Month = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

// Last day of previous month
Last Day Prev Month = EOMONTH(TODAY(), -1)

// Quarter number
Quarter = QUARTER(FactSales[OrderDate])
// Or manually: INT((MONTH(FactSales[OrderDate]) - 1) / 3) + 1

Math Functions

Common Math Functions Reference

Function	Description	Example	Result
`ROUND(number, digits)`	Rounds to specified digits	`ROUND(3.14159, 2)`	3.14
`ROUNDUP(number, digits)`	Rounds up	`ROUNDUP(3.141, 2)`	3.15
`ROUNDDOWN(number, digits)`	Rounds down	`ROUNDDOWN(3.149, 2)`	3.14
`INT(number)`	Rounds down to nearest integer	`INT(3.9)`	3
`ABS(number)`	Absolute value	`ABS(-42)`	42
`MOD(number, divisor)`	Remainder after division	`MOD(17, 5)`	2
`POWER(base, exponent)`	Raises to a power	`POWER(2, 10)`	1024
`SQRT(number)`	Square root	`SQRT(144)`	12

DIVIDE — Safe Division

DIVIDE is the recommended way to perform division in DAX because it handles division by zero gracefully.

// Syntax: DIVIDE(Numerator, Denominator, AlternateResult)

// Without DIVIDE (risky — error if denominator is 0)
Margin = [Revenue] / [Cost]

// With DIVIDE (safe — returns 0 if Cost is 0)
Margin = DIVIDE([Revenue], [Cost], 0)

// With DIVIDE (returns BLANK if denominator is 0)
Margin = DIVIDE([Revenue], [Cost])
// When no third argument, DIVIDE returns BLANK() on division by zero

Why DIVIDE is better than the / operator:

Feature	`/` Operator	`DIVIDE` Function
Division by zero	Returns error	Returns `BLANK()` or alternate value
Performance	Same	Same
Readability	Inline	Explicit
Recommended?	Only when you're certain the denominator is never 0	Yes — always use for safety

Evaluation Context

Evaluation context is the single most important concept in DAX. If you understand evaluation context, you understand DAX. If you don't, every complex formula will be a mystery.

There are two types of evaluation context:

Row Context

Row context means DAX evaluates the formula one row at a time, with access to the values in that specific row.

When does row context exist?

In a calculated column — each row gets its own calculation
Inside an iterator function (SUMX, AVERAGEX, FILTER, etc.) — each row is evaluated independently

// Calculated column — row context is automatic
Line Total = FactSales[Quantity] * FactSales[UnitPrice]
// Row 1: 2 * 29.99 = 59.98
// Row 2: 5 * 149.99 = 749.95
// Row 3: 1 * 499.99 = 499.99

Filter Context

Filter context is the set of filters that are active when a formula is evaluated. Filters come from:

Slicers on the report page
Visual axes (rows and columns of a matrix or chart categories)
Page-level, report-level, and visual-level filters
CALCULATE function modifying the context

// This measure responds to filter context
Total Revenue = SUM(FactSales[TotalAmount])

// In a matrix visual with Year on rows:
// 2024: Total Revenue = $1,200,000 (filter context: Year = 2024)
// 2025: Total Revenue = $1,500,000 (filter context: Year = 2025)
// 2026: Total Revenue = $800,000  (filter context: Year = 2026)
// Total: Total Revenue = $3,500,000 (no year filter)

How Context Affects Results — A Critical Example

Consider this expression: SUM(FactSales[TotalAmount])

As a calculated column (row context):

FactSales table:
┌────────┬─────────────┬────────────────┐
│ SaleID │ TotalAmount │ SUM_Column     │
├────────┼─────────────┼────────────────┤
│ 1      │ 100         │ 1,000 (WRONG!) │
│ 2      │ 200         │ 1,000 (WRONG!) │
│ 3      │ 300         │ 1,000 (WRONG!) │
│ 4      │ 400         │ 1,000 (WRONG!) │
└────────┴─────────────┴────────────────┘

Every row shows 1,000 because SUM ignores row context — it sums the entire column for every row. This is almost never what you want.

As a measure (filter context):

Matrix visual with Product Category on rows:
┌──────────────┬───────────────┐
│ Category     │ Total Revenue │
├──────────────┼───────────────┤
│ Electronics  │ 600           │
│ Clothing     │ 250           │
│ Food         │ 150           │
│ Total        │ 1,000         │
└──────────────┴───────────────┘

Each row in the visual has a different filter context (filtered by Category), so the measure returns the correct subtotal.

Context Summary

Property	Row Context	Filter Context
What it does	Provides access to current row values	Filters the data before calculation
Created by	Calculated columns, iterator functions	Slicers, visual axes, CALCULATE
Example	`FactSales[Qty] * FactSales[Price]` = row-level multiplication	`SUM(FactSales[Amount])` = sum within current filters
Common in	Calculated columns, SUMX/FILTER expressions	Measures

CALCULATE

CALCULATE is the most important function in DAX. It evaluates an expression in a modified filter context. It lets you override, add, or remove filters.

Syntax

CALCULATE(
    Expression,
    Filter1,
    Filter2,
    ...
)

Expression: The calculation to evaluate (usually an aggregation)
Filters: One or more filter arguments that modify the context

How CALCULATE Works

Takes the current filter context
Applies the filter arguments (adding, replacing, or removing filters)
Evaluates the expression in this modified context

Basic Examples

// Total Revenue for the "Electronics" category only
Electronics Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics"
)

// Revenue from 2025 only
Revenue 2025 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimDate[Year] = 2025
)

// Revenue for Electronics in 2025
Electronics Revenue 2025 =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics",
    DimDate[Year] = 2025
)
// Multiple filters are combined with AND logic

CALCULATE with Table Functions

Filters in CALCULATE can be simple column filters or table expressions:

// Using a simple column filter
High Value Orders =
CALCULATE(
    COUNTROWS(FactSales),
    FactSales[TotalAmount] > 1000
)

// Using FILTER for complex conditions
Complex Filter Sales =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(
        DimProduct,
        DimProduct[Category] = "Electronics"
        && DimProduct[ListPrice] > 100
    )
)

CALCULATE — Key Behaviors

Behavior	Description
Adds new filters	If the filter is on a column not currently filtered, it adds the filter
Overrides existing filters	If the filter is on a column that IS currently filtered, it replaces that filter
Multiple filters = AND	Multiple filter arguments are combined with AND logic
Context transition	When used inside row context (iterators, calc columns), CALCULATE converts row context to filter context

Filter Functions with CALCULATE

These functions are used inside CALCULATE to manipulate the filter context.

ALL

Removes all filters from a table or column.

// Revenue as % of grand total
Revenue % of Total =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        ALL(FactSales)
    )
)

// Remove filter from a specific column only
Revenue % of Category =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        ALL(DimProduct[ProductName])
    )
)

ALLEXCEPT

Removes all filters from a table except the specified columns.

// Revenue % within each Category
// (removes all product filters except Category)
Revenue % Within Category =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        ALLEXCEPT(DimProduct, DimProduct[Category])
    )
)

ALLSELECTED

Returns the values visible in the current visual context (respects slicers and page filters, but removes visual-level filters like row/column headers in a matrix).

// Revenue as % of visible total (slicer-aware)
Revenue % of Selected =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        ALLSELECTED(FactSales)
    )
)

REMOVEFILTERS

An alias for ALL when used inside CALCULATE. More readable for its intent.

Total Revenue Unfiltered =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    REMOVEFILTERS()
)

KEEPFILTERS

Prevents CALCULATE from overriding existing filters. Instead, the new filter is intersected (AND) with the existing filter.

// Without KEEPFILTERS — overrides existing color filter
Red Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Color] = "Red"
)
// If a slicer selects "Blue", this still shows Red revenue

// With KEEPFILTERS — intersects with existing filter
Red Revenue Intersected =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    KEEPFILTERS(DimProduct[Color] = "Red")
)
// If a slicer selects "Blue", this shows BLANK
// (intersection of Red and Blue = nothing)

VALUES and HASONEVALUE

// VALUES returns distinct values of a column in the current filter context
// HASONEVALUE returns TRUE if there's exactly one value

Dynamic Label =
IF(
    HASONEVALUE(DimProduct[Category]),
    "Revenue for " & VALUES(DimProduct[Category]),
    "Revenue for All Categories"
)

Filter Functions Summary

Function	Purpose	Used Inside CALCULATE?
`ALL`	Removes all filters from a table/column	Yes
`ALLEXCEPT`	Removes all filters except specified columns	Yes
`ALLSELECTED`	Respects slicers but removes visual filters	Yes
`REMOVEFILTERS`	Same as ALL (more readable alias)	Yes
`KEEPFILTERS`	Intersects instead of overrides	Yes
`VALUES`	Returns distinct values in current context	Sometimes
`HASONEVALUE`	Checks if exactly one value in context	No (used in IF)
`SELECTEDVALUE`	Returns the single selected value or alternate	No (used directly)

Variables (VAR / RETURN)

Variables make DAX formulas more readable, maintainable, and performant.

Syntax

Measure Name =
VAR VariableName1 = Expression1
VAR VariableName2 = Expression2
RETURN
    FinalExpression

Benefits of Variables

Benefit	Description
Readability	Break complex formulas into named, understandable steps
Performance	A variable is evaluated once and cached; referencing it multiple times doesn't recalculate
Debugging	You can return any variable to inspect its value
No side effects	Variables are immutable — once set, they don't change

Example: Without Variables (Hard to Read)

Profit Margin =
DIVIDE(
    SUM(FactSales[TotalAmount]) - SUM(FactSales[TotalCost]),
    SUM(FactSales[TotalAmount]),
    0
)

Example: With Variables (Clear and Efficient)

Profit Margin =
VAR TotalRevenue = SUM(FactSales[TotalAmount])
VAR TotalCost = SUM(FactSales[TotalCost])
VAR Profit = TotalRevenue - TotalCost
RETURN
    DIVIDE(Profit, TotalRevenue, 0)

Multi-Step Calculation Example

Sales Performance =
VAR CurrentSales = SUM(FactSales[TotalAmount])
VAR PriorYearSales =
    CALCULATE(
        SUM(FactSales[TotalAmount]),
        SAMEPERIODLASTYEAR(DimDate[Date])
    )
VAR YoYChange = CurrentSales - PriorYearSales
VAR YoYPct = DIVIDE(YoYChange, PriorYearSales, 0)
RETURN
    IF(
        ISBLANK(PriorYearSales),
        BLANK(),
        YoYPct
    )

Debugging with Variables

To debug, temporarily change the RETURN to return an intermediate variable:

// Original formula
My Measure =
VAR Step1 = SUM(FactSales[TotalAmount])
VAR Step2 = CALCULATE(Step1, ALL(DimProduct))
VAR Step3 = DIVIDE(Step1, Step2, 0)
RETURN
    Step3

// Debugging — return Step2 to check its value
My Measure =
VAR Step1 = SUM(FactSales[TotalAmount])
VAR Step2 = CALCULATE(Step1, ALL(DimProduct))
VAR Step3 = DIVIDE(Step1, Step2, 0)
RETURN
    Step2  -- Changed to inspect intermediate value

Formatting Measures

Setting Format in Properties

Select a measure in the Data pane
In the Measure tools tab, use the format dropdown or enter a custom format string
Common presets: Currency, Percentage, Whole Number, Decimal Number

Using FORMAT Function

The FORMAT function converts a value to a formatted text string. Use it sparingly — it returns text, which means the value can no longer be used in numeric calculations or conditional formatting.

// Format as currency text
Revenue Text = FORMAT([Total Revenue], "$#,##0.00")

// Format as percentage text
Growth Text = FORMAT([YoY Growth], "0.0%")

// Format with conditional text
Revenue Display =
FORMAT([Total Revenue], "#,##0") & " (" & FORMAT([YoY Growth], "+0.0%;-0.0%") & ")"
// Result: "1,234,567 (+12.5%)"

Common Format Strings

Format String	Example Output	Use For
`"$#,##0"`	$1,234	Currency, no decimals
`"$#,##0.00"`	$1,234.56	Currency, 2 decimals
`"#,##0"`	1,234	Whole numbers with commas
`"0.00"`	1234.56	Decimal, no commas
`"0.0%"`	12.5%	Percentage, 1 decimal
`"0%"`	13%	Percentage, no decimals
`"+0.0%;-0.0%"`	+12.5% / -3.2%	Signed percentage
`"DD-MMM-YYYY"`	15-Jan-2026	Date
`"YYYY-MM-DD"`	2026-01-15	ISO date

Best practice: Set the format on the measure's Properties rather than using the FORMAT function. Properties formatting keeps the value numeric (sortable, usable in conditional formatting) while still displaying nicely.

Common Beginner Mistakes

Mistake 1: Using a Calculated Column When You Need a Measure

Problem:

// Calculated column — WRONG
Revenue Total = SUM(FactSales[TotalAmount])
// Every row shows the same grand total

Fix:

// Measure — CORRECT
Total Revenue = SUM(FactSales[TotalAmount])
// Responds to filter context, shows correct subtotals

Rule: If the expression contains an aggregation function, it should be a measure.

Mistake 2: Not Understanding Filter Context

Problem: A measure shows the grand total everywhere instead of filtered subtotals.

Cause: The column used in the filter or axis is not connected to the fact table through a relationship.

Fix: Verify that all dimension columns used in visuals have proper relationships to the fact table.

Mistake 3: Dividing Without DIVIDE

Problem:

Margin = [Revenue] / [Cost]
// Crashes when Cost = 0

Fix:

Margin = DIVIDE([Revenue], [Cost], 0)
// Returns 0 when Cost = 0

Mistake 4: Using FILTER Instead of a Simple Column Filter

Problem:

// Unnecessarily complex and slow
Electronics Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    FILTER(ALL(DimProduct), DimProduct[Category] = "Electronics")
)

Fix:

// Simple, fast, correct
Electronics Revenue =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    DimProduct[Category] = "Electronics"
)

Use FILTER only when you need complex multi-column conditions that cannot be expressed as simple column predicates.

Mistake 5: Circular Dependencies

Problem: "A circular dependency was detected" error.

Cause: Calculated column A references calculated column B, which references calculated column A. Or a measure references itself.

Fix: Restructure the logic so there are no circular references. Use variables within a single formula instead of chaining calculated columns.

Mistake 6: Ignoring BLANK() Values

Problem: Measures return unexpected results when data contains blanks.

Fix: Use IF(ISBLANK(...), alternateValue, expression) or handle blanks in your DAX logic.

Practice Exercises

Exercise 1: Basic Measures

Create the following measures for a retail sales model (FactSales with columns: OrderID, ProductKey, CustomerKey, DateKey, Quantity, UnitPrice, TotalAmount, TotalCost):

Total Revenue: Sum of TotalAmount
Total Cost: Sum of TotalCost
Total Profit: Revenue minus Cost
Profit Margin %: Profit as a percentage of Revenue
Total Orders: Distinct count of OrderID
Average Order Value: Revenue divided by number of orders

Exercise 2: Calculated Columns

Create these calculated columns on the Products table (columns: ProductID, ProductName, ListPrice, Category, SubCategory):

Price Range: "Under $50", "$50-$200", "$200-$500", "Over $500"
Name Length: Number of characters in ProductName
Category-Product: Category and ProductName joined with " - "

Exercise 3: CALCULATE Practice

Write measures that use CALCULATE:

Online Revenue: Total revenue where SalesChannel = "Online"
Revenue Last Year: Total revenue for the same period last year (use SAMEPERIODLASTYEAR)
Revenue All Products: Total revenue ignoring any product filters (use ALL)
Product Revenue Share: Current product's revenue as a % of total revenue

Exercise 4: Variables

Rewrite this measure using VAR/RETURN to make it readable:

KPI =
IF(DIVIDE(SUM(FactSales[TotalAmount])-CALCULATE(SUM(FactSales[TotalAmount]),SAMEPERIODLASTYEAR(DimDate[Date])),CALCULATE(SUM(FactSales[TotalAmount]),SAMEPERIODLASTYEAR(DimDate[Date])),0)>0.1,"Above Target",IF(DIVIDE(SUM(FactSales[TotalAmount])-CALCULATE(SUM(FactSales[TotalAmount]),SAMEPERIODLASTYEAR(DimDate[Date])),CALCULATE(SUM(FactSales[TotalAmount]),SAMEPERIODLASTYEAR(DimDate[Date])),0)>0,"Growing","Declining")

Exercise 5: Iterator Functions

Create these measures using iterator (X) functions:

Weighted Average Price: SUMX(FactSales, Quantity * UnitPrice) / SUM(Quantity)
Products Above Average: Count of products whose total revenue is above the overall average product revenue
Top Product Revenue: Revenue of the highest-selling product (use MAXX)

Exercise 6: Text Functions

Create a measure that:

Shows "Showing data for [Category]" if a single category is selected
Shows "Showing data for [N] categories" if multiple categories are selected
Shows "Showing all categories" if no filter is applied

Hint: Use HASONEVALUE, VALUES, COUNTROWS, and CONCATENATE.

Summary

In this chapter, you learned the fundamentals of DAX:

DAX syntax: Column references use Table[Column], operators include arithmetic, comparison, text, and logical types
Calculated columns evaluate in row context — they have access to each row's values and are stored in the model
Measures evaluate in filter context — they respond to slicers, visual axes, and filters, and are computed at query time
Aggregation functions (SUM, AVERAGE, COUNT, DISTINCTCOUNT, etc.) summarize column values
Iterator functions (SUMX, AVERAGEX, COUNTX, RANKX) iterate row by row, evaluate an expression, then aggregate
Logical functions (IF, SWITCH, AND, OR) implement conditional logic
Text functions (CONCATENATE, FORMAT, LEFT, RIGHT) manipulate strings
Date functions (TODAY, DATEDIFF, YEAR, MONTH, EOMONTH) work with temporal data
Evaluation context is the most important concept: row context gives access to row values; filter context filters data before calculation
CALCULATE is the most important function: it evaluates an expression in a modified filter context
Filter functions (ALL, ALLEXCEPT, ALLSELECTED, KEEPFILTERS) control how CALCULATE modifies context
Variables (VAR/RETURN) improve readability, performance, and debuggability
Always prefer explicit measures over implicit ones, and use DIVIDE instead of the / operator

In the next chapter, you'll learn Advanced DAX — time intelligence, context transition, table functions, and sophisticated patterns for real-world business analytics.