📊 Power BI Formula Language

Master DAX
from Zero to Pro

Learn the Data Analysis Expressions language that powers every meaningful Power BI report — through guided lessons, live examples, and real formula patterns.

Start Learning View Cheatsheet
8
Learning Modules
50+
Formula Examples
12
Quiz Questions

Curriculum

Your Learning Path

A structured journey from fundamentals to advanced time intelligence — built the way data analysts actually learn DAX.

Module 01
🔑
DAX Fundamentals
Understand what DAX is, how it differs from Excel formulas, and when to use it in Power BI.
What is DAX? Syntax basics Data types Operators
Module 02
📐
Calculated Columns vs Measures
The most critical concept in DAX — when row context applies vs filter context, and why it matters.
Calc columns Measures Evaluation context Implicit measures
Module 03
🧮
Aggregation Functions
SUM, AVERAGE, COUNT, MIN, MAX — and their X-variants that iterate over tables row by row.
SUM / SUMX COUNT / COUNTA AVERAGE / AVERAGEX MINX / MAXX
Module 04
🔍
Filter Functions
CALCULATE is the most powerful function in DAX. Learn FILTER, ALL, ALLEXCEPT, and how to reshape filter context.
CALCULATE FILTER ALL / ALLEXCEPT REMOVEFILTERS
Module 05
🔗
Relationship & Lookup Functions
Navigate your data model with RELATED, RELATEDTABLE, LOOKUPVALUE and understand cross-filter direction.
RELATED RELATEDTABLE LOOKUPVALUE USERELATIONSHIP
Module 06
📅
Time Intelligence
The superpower of DAX — year-to-date, prior period comparisons, rolling averages, and date table setup.
TOTALYTD SAMEPERIODLASTYEAR DATEADD Date tables
Module 07
🏗️
Table Functions
SUMMARIZE, ADDCOLUMNS, CROSSJOIN, UNION — building virtual tables and using them inside measures.
SUMMARIZE ADDCOLUMNS VALUES / DISTINCT CROSSJOIN
Module 08
⚡
Advanced Patterns
Ranking, pareto analysis, dynamic segmentation, ABC classification, and performance optimization.
RANKX TOPN VAR / RETURN Optimization

Core Concepts

How DAX Thinks

Understanding these foundations unlocks everything else in DAX.

Filter Context

Filter context is the set of filters applied to a calculation. In Power BI, every visual — slicers, charts, row/column headers — applies filters to the data model before DAX evaluates any measure.

When you place a measure in a table, each row has a different filter context: the combination of column values in that row. Your measure is re-evaluated for each row's unique context.

  • Comes from visuals, slicers, and report filters
  • Propagates through relationships automatically
  • Can be modified with CALCULATE
  • Measures always execute inside filter context
filter_context.dax
-- This measure respects all filters from visuals
Total Sales =
  SUM( Sales[Amount] )

-- In a table showing Year/Month, each cell
-- sees only data for that period

Sales % of Total =
  VAR FilteredSales = SUM( Sales[Amount] )
  VAR AllSales =
    CALCULATE(
      SUM( Sales[Amount] ),
      ALL( Sales )
    )
  RETURN
    DIVIDE( FilteredSales, AllSales )

Row Context

Row context exists when DAX iterates through a table row by row — in calculated columns, and in X-iterator functions like SUMX, AVERAGEX, MAXX.

In row context, you can reference column values for the "current row" directly. Understanding the difference between row and filter context prevents most common DAX bugs.

  • Calculated columns always have row context
  • Measures do NOT have row context by default
  • SUMX, FILTER, AVERAGEX create row context
  • EARLIER() accesses the outer row context
row_context.dax
-- Calculated column: row context available
Sales[Profit] =
  Sales[Revenue] - Sales[Cost]

-- SUMX creates row context per row
Total Profit =
  SUMX(
    Sales,
    Sales[Revenue] - Sales[Cost]
  )

-- FILTER with row context for conditions
High Value Sales =
  CALCULATE(
    COUNT( Sales[OrderID] ),
    FILTER( Sales, Sales[Amount] > 1000 )
  )

CALCULATE — The Engine

CALCULATE is the most important function in all of DAX. It evaluates an expression in a modified filter context. You add, remove, or replace filters to change what data is included.

Every DAX measure implicitly wraps in a CALCULATE context. Understanding how it transforms filters is the key to unlocking advanced patterns.

  • First argument: the expression to evaluate
  • Additional arguments: filter modifiers
  • ALL(), ALLEXCEPT() remove filters
  • Column = value adds a new filter
calculate.dax
-- Override a filter: sales for 2024 only
Sales 2024 =
  CALCULATE(
    SUM( Sales[Amount] ),
    Dates[Year] = 2024
  )

-- Remove all filters: grand total
Total All Regions =
  CALCULATE(
    SUM( Sales[Amount] ),
    ALL( Geography[Region] )
  )

-- Keep only product filter, remove rest
Sales by Product Only =
  CALCULATE(
    SUM( Sales[Amount] ),
    ALLEXCEPT( Products, Products[Category] )
  )

Variables (VAR / RETURN)

Variables are one of the best practices in modern DAX. They let you store intermediate results, making formulas easier to read, debug, and often faster to execute since each variable is computed once.

Variables capture the current filter context at the point where they're defined — this is crucial to understand for complex calculations.

  • VAR stores a value or table expression
  • RETURN specifies the final output
  • Variables are lazy — only evaluated if used
  • Context is captured at VAR definition time
variables.dax
-- Clean, readable measure with VAR
Sales vs Target % =
  VAR ActualSales =
    SUM( Sales[Amount] )
  VAR TargetSales =
    SUM( Targets[Target] )
  VAR Variance =
    ActualSales - TargetSales
  RETURN
    IF(
      TargetSales = 0,
      BLANK(),
      DIVIDE( Variance, TargetSales )
    )

Time Intelligence

DAX has a rich set of time intelligence functions that make period-over-period analysis simple. The requirement: a proper Date table with continuous dates and a marked "date table" status in Power BI.

Once you have your date table set up, functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD write complex date math for you.

  • Always use a dedicated Date dimension table
  • Mark it as "Date Table" in Power BI
  • TOTALYTD resets at fiscal year start
  • DATEADD shifts by days, months, quarters, years
time_intelligence.dax
-- Year-to-date sales
Sales YTD =
  TOTALYTD(
    SUM( Sales[Amount] ),
    Dates[Date]
  )

-- Same period last year
Sales SPLY =
  CALCULATE(
    SUM( Sales[Amount] ),
    SAMEPERIODLASTYEAR( Dates[Date] )
  )

-- Year-over-year growth %
YoY Growth % =
  VAR CY = SUM( Sales[Amount] )
  VAR PY = [Sales SPLY]
  RETURN DIVIDE( CY - PY, PY )

Formula Patterns

Real-World Recipes

Copy-paste ready DAX patterns for the most common analytical problems.

Measure Running Total
Cumulative sum that keeps growing as you scroll through dates.
Running Total =
CALCULATE(
  SUM( Sales[Amount] ),
  FILTER(
    ALL( Dates[Date] ),
    Dates[Date] <= MAX( Dates[Date] )
  )
)
Filter Dynamic Ranking
Rank products by sales within the current filter context.
Product Rank =
RANKX(
  ALLSELECTED( Products[Name] ),
  SUM( Sales[Amount] ),
  ,
  DESC,
  DENSE
)
Time Rolling 3-Month Average
Smooth out seasonal fluctuations with a rolling window.
Rolling 3M Avg =
VAR Last3Months =
  DATESINPERIOD(
    Dates[Date],
    LASTDATE( Dates[Date] ),
    -3, MONTH
  )
RETURN
  CALCULATE(
    AVERAGEX( VALUES( Dates[Month] ),
      [Total Sales] ),
    Last3Months
  )
Segment ABC Classification
Classify customers into A/B/C tiers based on revenue contribution.
Customer Tier =
VAR Revenue = [Total Sales]
VAR Pct = [Sales % of Total]
RETURN
  SWITCH(
    TRUE(),
    Pct >= 0.7, "A — Top",
    Pct >= 0.9, "B — Mid",
    "C — Long Tail"
  )
Filter % of Parent
Show a subcategory's share of its parent category, dynamically.
% of Category =
VAR ProductSales =
  [Total Sales]
VAR CategorySales =
  CALCULATE(
    [Total Sales],
    ALLEXCEPT(
      Products,
      Products[Category]
    )
  )
RETURN
  DIVIDE( ProductSales, CategorySales )
Time MoM Change
Month-over-month change in absolute and percentage terms.
MoM Change % =
VAR Current = [Total Sales]
VAR PrevMonth =
  CALCULATE(
    [Total Sales],
    DATEADD(
      Dates[Date],
      -1, MONTH
    )
  )
RETURN
  DIVIDE( Current - PrevMonth, PrevMonth )

Knowledge Check

Test Yourself

12 questions to validate your DAX understanding across all modules.

Question 1 of 12  |  Score: 0

Quick Reference

DAX Function Cheatsheet

The most-used functions, organized by category. Bookmark this page.

Aggregation
SUM(col)Sum of column
SUMX(tbl, expr)Iterating sum
AVERAGE(col)Column average
AVERAGEX(tbl, expr)Iterating average
COUNT(col)Count numbers
COUNTA(col)Count non-blanks
COUNTROWS(tbl)Count table rows
DISTINCTCOUNT(col)Unique value count
MIN / MAXMin or max value
Filter & Context
CALCULATE(expr, ...)Modify filter ctx
FILTER(tbl, cond)Returns filtered table
ALL(tbl/col)Remove all filters
ALLEXCEPT(tbl, col)Keep one filter
ALLSELECTED()Respects slicers
KEEPFILTERS()Intersect filters
REMOVEFILTERS()Clear filters
HASONEVALUE(col)Single value check
SELECTEDVALUE(col)Get slicer value
Time Intelligence
TOTALYTD(expr, dates)Year-to-date
TOTALQTD(expr, dates)Quarter-to-date
TOTALMTD(expr, dates)Month-to-date
SAMEPERIODLASTYEARPrior year match
DATEADD(dates, n, iv)Shift date range
DATESINPERIOD()Rolling window
FIRSTDATE / LASTDATEPeriod boundaries
DATESYTD(dates)YTD date set
Table Functions
SUMMARIZE(tbl, ...)Group by table
ADDCOLUMNS(tbl, ...)Add virtual cols
VALUES(col)Unique values
DISTINCT(col)Distinct no blank
TOPN(n, tbl, expr)Top N rows
UNION(tbl1, tbl2)Combine tables
INTERSECT(tbl1, tbl2)Matching rows
EXCEPT(tbl1, tbl2)Rows not in tbl2
Logical & Conditional
IF(cond, t, f)Conditional value
SWITCH(expr, ...)Multi-branch if
IFERROR(expr, alt)Trap errors
ISBLANK(expr)Blank check
DIVIDE(n, d, alt)Safe division
AND / ORLogical operators
IN {list}List membership
BLANK()Null equivalent
Relationship & Lookup
RELATED(col)From related table
RELATEDTABLE(tbl)Related rows
LOOKUPVALUE()No-relationship lookup
USERELATIONSHIP()Activate inactive rel
CROSSFILTER()Change filter direction
TREATAS(tbl, col)Virtual relationship
EARLIER(col)Outer row context

Go Deeper

Next Steps & Resources

The best places to continue your DAX journey beyond this guide.

Reference
DAX.guide
The definitive DAX function reference. Every function documented with examples, syntax, and notes on gotchas.
→
Patterns
DAX Patterns
By Marco Russo & Alberto Ferrari — the authoritative catalog of reusable DAX formulas with full explanations.
→
Learning
SQLBI
Deep-dive articles, courses, and videos from the world's top DAX educators. Highly recommended for intermediate learners.
→
Official Docs
Microsoft DAX Docs
The official Microsoft reference with complete function listings, syntax definitions, and examples for every DAX function.
→
Community
Power BI Community
Ask questions and learn from the community. The forums are packed with solved problems that mirror real-world scenarios.
→
Video
Guy in a Cube
Practical Power BI and DAX tutorials on YouTube — great for visual learners and keeping up with the latest features.
→