DAX Formulas Every Business User Should Know

DAX (Data Analysis Expressions) is the formula language that transforms Power BI from a simple charting tool into a full-fledged analytical engine. While Power BI's drag-and-drop interface handles basic aggregations, real business intelligence requires DAX. Year-over-year comparisons, rolling averages, rankings, what-if scenarios, and complex KPIs all depend on DAX formulas.

The challenge is that DAX looks deceptively simple but behaves in ways that surprise even experienced Excel users. The concepts of filter context, row context, and context transition are unique to DAX and have no direct equivalent in Excel or SQL. This guide focuses on the 20 most practical DAX formulas for business users, with real-world examples you can adapt immediately.

---

Key Takeaways

• CALCULATE is the single most important DAX function --- it modifies the filter context and is used in over 80 percent of non-trivial measures
• Time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) require a proper date table marked as such in the model
• VAR/RETURN syntax makes complex measures readable and avoids redundant calculations
• Iterator functions (SUMX, AVERAGEX, RANKX) evaluate expressions row by row, while aggregators (SUM, AVERAGE) operate on columns
• Context transition occurs when a calculated column or iterator calls a measure, converting row context into filter context
• SWITCH(TRUE(), ...) replaces nested IF statements and is far more readable
• ALL, ALLEXCEPT, and REMOVEFILTERS control which filters are active --- but be cautious with RLS implications

---

Foundation: Filter Context and Row Context

Before diving into formulas, understanding these two concepts is essential. Every confusion in DAX traces back to filter context and row context.

Filter Context

Filter context is the set of filters applied to a calculation. It comes from slicers, visual filters, page filters, report filters, and RLS. When you place a measure in a matrix visual with "Year" on rows and "Region" on columns, each cell has a unique filter context: (Year = 2025, Region = North America), (Year = 2025, Region = EMEA), and so on.

Every DAX aggregation function (SUM, AVERAGE, COUNT, MIN, MAX) evaluates within the current filter context. SUM(Sales[Revenue]) in a cell filtered to 2025 + North America only sums revenue rows matching both conditions.

Row Context

Row context exists inside calculated columns and iterator functions. It means "the current row." In a calculated column on the Sales table, Sales[Revenue] * Sales[Quantity] evaluates for each row individually.

Context Transition

When an iterator function (like SUMX) calls a measure, the row context is automatically converted into filter context. This is called context transition. It is powerful but can be a source of performance issues if misused.

-- Row context exists here (iterating over Products)
Revenue Per Product =
SUMX(
    Products,
    [Total Revenue]  -- This measure is evaluated in filter context
                     -- where the current product's ID filters the Sales table
)

Understanding these concepts makes every DAX formula intuitive. Without them, DAX formulas feel like black boxes.

---

The 20 Essential Formulas

1. CALCULATE — Modify Filter Context

CALCULATE is the most important DAX function. It evaluates an expression in a modified filter context.

Syntax: CALCULATE(expression, filter1, filter2, ...)

Example: Revenue from large orders only

Large Order Revenue =
CALCULATE(
    SUM(Sales[Revenue]),
    Sales[OrderTotal] > 10000
)

Example: Revenue from a specific product category

Electronics Revenue =
CALCULATE(
    SUM(Sales[Revenue]),
    Products[Category] = "Electronics"
)

CALCULATE replaces the existing filter on the specified column. If a slicer already filters Category to "Clothing," the Electronics Revenue measure ignores that slicer and shows Electronics revenue because CALCULATE overrides the category filter.

2. FILTER — Row-Level Filtering

FILTER returns a table filtered by a condition. It is often used inside CALCULATE for complex filtering that cannot be expressed as a simple column comparison.

Syntax: FILTER(table, condition)

Example: Revenue from customers with more than 5 orders

Revenue From Repeat Customers =
CALCULATE(
    SUM(Sales[Revenue]),
    FILTER(
        Customers,
        CALCULATE(COUNTROWS(Sales)) > 5
    )
)

Important: FILTER iterates row by row, making it slower than simple CALCULATE filters on large tables. Use it only when a direct column filter is insufficient.

3. ALL — Remove All Filters

ALL removes all filters from a table or column, returning the complete unfiltered table. It is used to calculate totals, percentages of total, and ratios.

Syntax: ALL(table) or ALL(table[column])

Example: Revenue as percentage of total

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

When a slicer filters to "North America," SUM(Sales[Revenue]) returns North America's revenue. CALCULATE(SUM(Sales[Revenue]), ALL(Sales)) removes all filters and returns global revenue. The division gives the percentage.

4. ALLEXCEPT — Remove All Filters Except Specified

ALLEXCEPT removes all filters from a table except the specified columns. This is useful when you want to preserve some filters (like year) while removing others (like product category).

Syntax: ALLEXCEPT(table, column1, column2, ...)

Example: Revenue share within the current year

Revenue % of Year Total =
DIVIDE(
    SUM(Sales[Revenue]),
    CALCULATE(
        SUM(Sales[Revenue]),
        ALLEXCEPT(Sales, DateTable[Year])
    )
)

This shows each product or region's share of revenue within the selected year, not across all time.

5. TOTALYTD — Year-to-Date

TOTALYTD calculates a running total from the start of the year to the current date in context.

Syntax: TOTALYTD(expression, dates[date_column], [filter], [year_end_date])

Example: Year-to-date revenue

Revenue YTD =
TOTALYTD(SUM(Sales[Revenue]), DateTable[Date])

With fiscal year ending June 30:

Revenue Fiscal YTD =
TOTALYTD(SUM(Sales[Revenue]), DateTable[Date], "6/30")

Prerequisite: The DateTable must be marked as a date table in the model (Table Tools, then Mark as Date Table).

6. SAMEPERIODLASTYEAR — Year-Over-Year

SAMEPERIODLASTYEAR shifts the date context back by exactly one year.

Syntax: SAMEPERIODLASTYEAR(dates[date_column])

Example: Revenue versus last year

Revenue PY =
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(DateTable[Date]))

Revenue YoY Growth =
VAR CurrentRevenue = SUM(Sales[Revenue])
VAR PriorRevenue = [Revenue PY]
RETURN
DIVIDE(CurrentRevenue - PriorRevenue, PriorRevenue, 0)

In a monthly matrix, each row shows the current month's revenue and the same month from the prior year, along with the growth percentage.

7. DATEADD — Flexible Time Shifts

DATEADD shifts dates by any interval: days, months, quarters, or years. It is more flexible than SAMEPERIODLASTYEAR.

Syntax: DATEADD(dates[date_column], intervals, interval_type)

Example: Revenue 3 months ago

Revenue 3 Months Ago =
CALCULATE(
    SUM(Sales[Revenue]),
    DATEADD(DateTable[Date], -3, MONTH)
)

Example: Rolling 12-month revenue

Revenue Rolling 12M =
CALCULATE(
    SUM(Sales[Revenue]),
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)

8. RANKX — Dynamic Rankings

RANKX assigns a ranking to each item based on an expression.

Syntax: RANKX(table, expression, [value], [order], [ties])

Example: Rank products by revenue

Product Revenue Rank =
RANKX(
    ALL(Products[ProductName]),
    [Total Revenue],
    ,
    DESC,
    DENSE
)

The ALL(Products[ProductName]) provides the full list of products to rank against, regardless of slicer filters on products. DESC means highest revenue gets rank 1. DENSE means tied values get the same rank.

Example: Top N filter

Top 10 Products Revenue =
CALCULATE(
    [Total Revenue],
    FILTER(
        ALL(Products[ProductName]),
        RANKX(ALL(Products[ProductName]), [Total Revenue], , DESC) <= 10
    )
)

9. SWITCH — Conditional Logic

SWITCH replaces nested IF statements and is far more readable.

Syntax: SWITCH(expression, value1, result1, value2, result2, ..., else_result)

Example: Customer segment classification

Customer Segment =
SWITCH(
    TRUE(),
    [Total Revenue] > 100000, "Enterprise",
    [Total Revenue] > 25000, "Mid-Market",
    [Total Revenue] > 5000, "SMB",
    "Startup"
)

Example: Dynamic measure selection

Selected Measure =
SWITCH(
    SELECTEDVALUE(MeasureSelector[Measure]),
    "Revenue", [Total Revenue],
    "Orders", [Order Count],
    "AOV", [Average Order Value],
    "Margin", [Gross Margin %],
    [Total Revenue]
)

This pattern works with a slicer that lets users choose which metric to display in a chart.

10. VAR / RETURN — Variables

Variables store intermediate results, making formulas readable and avoiding redundant calculations. A variable is evaluated once and reused.

Syntax: VAR name = expression RETURN final_expression

Example: Complex KPI with variables

Customer Health Score =
VAR Revenue = [Total Revenue]
VAR OrderCount = [Order Count]
VAR DaysSinceLastOrder =
    DATEDIFF(MAX(Sales[OrderDate]), TODAY(), DAY)
VAR RevenueScore =
    SWITCH(TRUE(), Revenue > 50000, 3, Revenue > 10000, 2, 1)
VAR FrequencyScore =
    SWITCH(TRUE(), OrderCount > 20, 3, OrderCount > 5, 2, 1)
VAR RecencyScore =
    SWITCH(TRUE(), DaysSinceLastOrder < 30, 3, DaysSinceLastOrder < 90, 2, 1)
RETURN
    RevenueScore + FrequencyScore + RecencyScore

Without variables, this formula would repeat the same calculations multiple times, making it unreadable and slower.

11. DIVIDE — Safe Division

DIVIDE handles division by zero gracefully, returning a specified alternate result instead of an error.

Syntax: DIVIDE(numerator, denominator, [alternate_result])

Example:

Conversion Rate =
DIVIDE([Closed Won Deals], [Total Opportunities], 0)

Always use DIVIDE instead of the / operator in measures. The / operator returns an error when dividing by zero, breaking your visuals.

12. DISTINCTCOUNT — Count Unique Values

DISTINCTCOUNT counts the number of unique values in a column.

Syntax: DISTINCTCOUNT(column)

Example: Active customers in the period

Active Customers =
DISTINCTCOUNT(Sales[CustomerID])

This counts unique customers who made at least one purchase in the current filter context (selected month, quarter, etc.).

13. COUNTROWS — Count Rows in a Table

COUNTROWS counts the number of rows in a table, optionally filtered.

Syntax: COUNTROWS(table)

Example: Orders count

Order Count =
COUNTROWS(Sales)

Cancelled Orders =
CALCULATE(
    COUNTROWS(Sales),
    Sales[Status] = "Cancelled"
)

14. SUMX — Row-by-Row Sum

SUMX is an iterator that evaluates an expression for each row and sums the results. It is essential when you need to multiply columns before summing.

Syntax: SUMX(table, expression)

Example: Weighted average calculation

Weighted Average Price =
DIVIDE(
    SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
    SUM(Sales[Quantity])
)

SUM cannot multiply two columns. You need SUMX to evaluate Quantity times UnitPrice for each row and then sum the results.

15. AVERAGEX — Row-by-Row Average

AVERAGEX is the iterator version of AVERAGE.

Syntax: AVERAGEX(table, expression)

Example: Average days to close

Average Days to Close =
AVERAGEX(
    Opportunities,
    DATEDIFF(Opportunities[CreatedDate], Opportunities[ClosedDate], DAY)
)

16. MAXX and MINX — Iterator Min/Max

MAXX and MINX find the maximum or minimum value of an expression evaluated row by row.

Example: Latest order date per customer

Most Recent Order =
MAXX(Sales, Sales[OrderDate])

Oldest Unpaid Invoice =
MINX(
    FILTER(Invoices, Invoices[PaymentStatus] = "Unpaid"),
    Invoices[InvoiceDate]
)

17. LOOKUPVALUE — VLOOKUP Equivalent

LOOKUPVALUE retrieves a value from a table based on one or more search criteria. It is the DAX equivalent of Excel's VLOOKUP.

Syntax: LOOKUPVALUE(result_column, search_column, search_value, ...)

Example:

Customer Region =
LOOKUPVALUE(
    Customers[Region],
    Customers[CustomerID], Sales[CustomerID]
)

Note: LOOKUPVALUE is typically used in calculated columns, not measures. In measures, RELATED (for many-to-one) or RELATEDTABLE (for one-to-many) are preferred.

18. SELECTEDVALUE — Get the Current Slicer Value

SELECTEDVALUE returns the value of a column when exactly one value is in the filter context. If multiple values are selected, it returns the alternate result.

Syntax: SELECTEDVALUE(column, [alternate_result])

Example: Dynamic title

Chart Title =
"Revenue for " & SELECTEDVALUE(DateTable[Year], "All Years")

19. ISBLANK — Check for Empty Values

ISBLANK tests whether a value is blank (null/empty).

Example: Orders without an assigned salesperson

Unassigned Orders =
CALCULATE(
    COUNTROWS(Sales),
    ISBLANK(Sales[SalespersonID])
)

20. CONCATENATEX — Concatenate Values from a Table

CONCATENATEX iterates over a table and concatenates values into a single string.

Syntax: CONCATENATEX(table, expression, [delimiter], [order_by], [order])

Example: List of products purchased by a customer

Products Purchased =
CONCATENATEX(
    VALUES(Products[ProductName]),
    Products[ProductName],
    ", ",
    Products[ProductName], ASC
)

In a customer detail table, this shows "Product A, Product B, Product C" for each customer.

---

Time Intelligence Deep Dive

Time intelligence is the most common reason business users need DAX. Power BI's time intelligence functions require a dedicated date table. Here is a comprehensive set of time measures.

Prerequisites: The Date Table

Your model must have a date table with:

• A continuous date column (no gaps) covering your entire data range
• The table marked as a date table (Table Tools, then Mark as Date Table)
• A relationship from the date table's date column to each fact table's date column

Common Time Intelligence Measures

-- Month-to-Date
Revenue MTD =
TOTALMTD(SUM(Sales[Revenue]), DateTable[Date])

-- Quarter-to-Date
Revenue QTD =
TOTALQTD(SUM(Sales[Revenue]), DateTable[Date])

-- Year-to-Date
Revenue YTD =
TOTALYTD(SUM(Sales[Revenue]), DateTable[Date])

-- Previous Month
Revenue PM =
CALCULATE(SUM(Sales[Revenue]), PREVIOUSMONTH(DateTable[Date]))

-- Previous Quarter
Revenue PQ =
CALCULATE(SUM(Sales[Revenue]), PREVIOUSQUARTER(DateTable[Date]))

-- Previous Year
Revenue PY =
CALCULATE(SUM(Sales[Revenue]), PREVIOUSYEAR(DateTable[Date]))

-- Month-over-Month Growth
Revenue MoM % =
VAR Current = SUM(Sales[Revenue])
VAR Prior = [Revenue PM]
RETURN DIVIDE(Current - Prior, Prior, 0)

-- Year-over-Year Growth
Revenue YoY % =
VAR Current = SUM(Sales[Revenue])
VAR Prior = [Revenue PY]
RETURN DIVIDE(Current - Prior, Prior, 0)

-- Rolling 3-Month Average
Revenue 3M Avg =
AVERAGEX(
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -3, MONTH),
    CALCULATE(SUM(Sales[Revenue]))
)

-- Cumulative Total (Running Sum)
Revenue Cumulative =
CALCULATE(
    SUM(Sales[Revenue]),
    FILTER(
        ALL(DateTable[Date]),
        DateTable[Date] <= MAX(DateTable[Date])
    )
)

Fiscal Year Time Intelligence

If your fiscal year does not align with the calendar year, use the optional year_end_date parameter:

-- Fiscal Year ending March 31
Revenue Fiscal YTD =
TOTALYTD(SUM(Sales[Revenue]), DateTable[Date], "3/31")

-- Or use DATESYTD for more control
Revenue Fiscal YTD v2 =
CALCULATE(
    SUM(Sales[Revenue]),
    DATESYTD(DateTable[Date], "3/31")
)

---

Context Transition: The Hidden Engine

Context transition is the process where row context (from a calculated column or iterator) is converted into filter context when a measure is called. This is the most powerful and most misunderstood concept in DAX.

How It Works

Consider this measure:

Total Revenue = SUM(Sales[Revenue])

And this calculated column on the Products table:

Product Revenue = [Total Revenue]

In the calculated column, there is row context (the current product row). When [Total Revenue] is called, context transition converts the row context into a filter: CALCULATE(SUM(Sales[Revenue]), Products[ProductID] = <current row's ProductID>). The result is revenue for that specific product.

Performance Implications

Context transition adds an implicit CALCULATE around the measure call. For small tables, this is negligible. For large tables with complex measures, it can be expensive. Avoid calling complex measures inside iterators over large tables.

Inefficient:

-- Iterates over every customer, calling a complex measure each time
Customer Profitability =
SUMX(
    Customers,
    [Revenue] - [COGS] - [Allocated Overhead]
)

Better:

-- Pre-compute the components and use simple arithmetic
Customer Profitability =
SUM(Sales[Revenue]) - SUM(Sales[COGS]) -
DIVIDE(SUM(Sales[Revenue]), [Total Revenue]) * [Total Overhead]

---

Practical Business Scenarios

Scenario 1: ABC Analysis (Pareto Classification)

Classify products into A (top 80% of revenue), B (next 15%), and C (bottom 5%):

ABC Class =
VAR CurrentProduct = SELECTEDVALUE(Products[ProductName])
VAR AllProducts =
    ADDCOLUMNS(
        ALL(Products[ProductName]),
        "@Revenue", [Total Revenue]
    )
VAR Sorted = TOPN(COUNTROWS(AllProducts), AllProducts, [@Revenue], DESC)
VAR TotalRev = SUMX(AllProducts, [@Revenue])
VAR CumulativeRev =
    SUMX(FILTER(Sorted, [@Revenue] >= [Total Revenue]), [@Revenue])
VAR CumulativePct = DIVIDE(CumulativeRev, TotalRev)
RETURN
    SWITCH(TRUE(),
        CumulativePct <= 0.8, "A",
        CumulativePct <= 0.95, "B",
        "C"
    )

Scenario 2: Cohort Retention Analysis

Track how many customers from each acquisition cohort remain active over time:

Cohort Retention Rate =
VAR CohortMonth = SELECTEDVALUE(DateTable[YearMonth])
VAR CohortCustomers =
    CALCULATE(
        DISTINCTCOUNT(Sales[CustomerID]),
        FILTER(
            ALL(Sales),
            FORMAT(Sales[FirstPurchaseDate], "YYYY-MM") = CohortMonth
        )
    )
VAR ActiveCustomers =
    CALCULATE(
        DISTINCTCOUNT(Sales[CustomerID]),
        FILTER(
            ALL(Sales),
            FORMAT(Sales[FirstPurchaseDate], "YYYY-MM") = CohortMonth
        )
    )
RETURN
    DIVIDE(ActiveCustomers, CohortCustomers, 0)

Scenario 3: Moving Annual Total (MAT)

A moving annual total smooths seasonality by summing the last 12 complete months:

Revenue MAT =
CALCULATE(
    SUM(Sales[Revenue]),
    DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -12, MONTH)
)

Revenue MAT Growth =
VAR Current = [Revenue MAT]
VAR Prior =
    CALCULATE(
        [Revenue MAT],
        DATEADD(DateTable[Date], -12, MONTH)
    )
RETURN
    DIVIDE(Current - Prior, Prior, 0)

---

Iterator vs Aggregator: When to Use Each

Aggregator Functions

SUM, AVERAGE, COUNT, MIN, MAX operate on a single column. They are fast because the storage engine can resolve them directly from compressed column data.

Use when: You need a simple aggregation of one column.

Iterator Functions

SUMX, AVERAGEX, COUNTX, MINX, MAXX, RANKX evaluate an expression row by row. They are more flexible but slower because the formula engine processes each row.

Use when:

• You need to multiply columns before aggregating (SUMX for weighted calculations)
• You need to apply a condition per row (COUNTX with IF)
• You need to rank items (RANKX)
• The calculation depends on values from related tables that need row-level evaluation

Performance rule of thumb: If the same result can be achieved with an aggregator, use the aggregator. Only use iterators when the row-by-row evaluation is necessary.

---

Debugging DAX

Common Error Messages

Performance Analyzer

Power BI Desktop's Performance Analyzer (View, then Performance Analyzer) shows the DAX query and execution time for each visual. Use it to:

1. Identify slow visuals (over 1 second)
2. Copy the DAX query to DAX Studio for detailed analysis
3. Compare before/after execution times when optimizing measures

DAX Studio

DAX Studio is a free external tool that provides detailed query analysis. It shows:

• Storage engine queries (fast, column-store scans)
• Formula engine queries (slower, row-by-row evaluation)
• Materialization sizes (data shuffled between engines)

If a measure generates many formula engine queries, it likely uses too many iterators or complex FILTER expressions. Refactor to push more work to the storage engine.

For Power BI training that covers DAX from fundamentals through advanced optimization, ECOSIRE offers hands-on workshops tailored to your specific datasets and business questions.

---

FAQ

What is the difference between a measure and a calculated column?

A measure is evaluated at query time within the current filter context. It does not add data to the table --- it computes a value dynamically. A calculated column is evaluated at data refresh time and adds a physical column to the table. Use measures for aggregations and KPIs that change based on slicers and filters. Use calculated columns for row-level classifications or values that need to be used in slicers, filters, or relationships. Measures are almost always preferred because they do not increase the model size.

Why does my YTD measure show incorrect values?

The most common cause is that your date table is not properly configured. Verify three things: (1) the date table has no gaps --- every date from the start of your data to the present must be included, (2) the table is marked as a date table in Power BI (Table Tools, then Mark as Date Table), and (3) the relationship between the date table and your fact table is active and correctly mapped. Also check that your date column does not include a time component that prevents exact matching.

When should I use CALCULATE versus FILTER?

Use CALCULATE when you can express the filter as a simple column comparison (e.g., Products[Category] = "Electronics"). CALCULATE converts this to an efficient filter that the storage engine can optimize. Use FILTER when you need complex row-by-row conditions that reference multiple columns or call measures (e.g., filtering customers where CALCULATE(COUNTROWS(Sales)) > 5). FILTER is an iterator and is slower, so prefer CALCULATE's simple syntax when possible.

How do I create a running total or cumulative sum in DAX?

Use CALCULATE with a filter that includes all dates up to the current date: Revenue Cumulative = CALCULATE(SUM(Sales[Revenue]), FILTER(ALL(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))). This removes the existing date filter (ALL) and replaces it with a filter that includes all dates from the beginning up to the maximum date in the current context. In a monthly matrix, each row shows the cumulative total through that month.

Can I use DAX to write data back to the database?

No. DAX is a read-only query language. It can calculate, filter, and transform data for display, but it cannot insert, update, or delete data in the source database. For write-back scenarios, use Power Apps embedded in Power BI, or use Power Automate flows triggered by Power BI alerts. Some third-party tools (Acterys, Writeback Manager) add write-back capabilities through custom visuals.