DAX Basics

DAX stands for Data Analysis Expressions. It is the formula language of Power Pivot — and of Power BI, Azure Analysis Services, and SQL Server Analysis Services. Learning DAX is one of the highest-value investments you can make as an Excel analyst. The basics are approachable if you understand one idea clearly: DAX formulas do not operate on cells. They operate on columns, tables, and filter contexts. Once that clicks, the most powerful function in all of DAX — CALCULATE — makes complete sense.

DAX Syntax — How It Differs From Excel Formulas

DAX formulas look similar to Excel formulas — they use function names, parentheses, and arguments. But the references work differently. Instead of cell references like A2 or B2:B100, DAX uses table and column references in the format TableName[ColumnName]. And instead of operating on a range of cells, most DAX functions operate on entire columns or filter-aware sets of rows.

Excel formula syntax vs DAX syntax:

Excel (cell references):
  =SUM(B2:B100)
  =AVERAGE(C2:C50)
  =IF(D2>1000, "Large", "Small")

DAX (table[column] references):
  Total Revenue := SUM(Sales[Revenue])
  Avg Revenue   := AVERAGE(Sales[Revenue])
  [measure]     := IF(SUM(Sales[Revenue]) > 1000, "Large", "Small")

Key differences:
  - DAX uses TableName[ColumnName] — not cell addresses
  - Measure names end with :=  (not just =)
  - Calculated column formulas use just [ColumnName] (same table implied)
  - DAX functions are evaluated against filter context, not a fixed range
  - There is no drag-to-fill — DAX applies to all rows automatically

The most practical thing to understand about DAX references: when you write SUM(Sales[Revenue]) in a measure, DAX does not sum all rows in the Revenue column unconditionally. It sums the rows that survive the current filter context — whatever region, time period, product, or segment is active in the PivotTable at that moment. The same formula returns different numbers in different cells of the PivotTable because the filter context changes cell by cell. This is the core of how DAX works.

CALCULATE — The Most Important DAX Function

CALCULATE is the function that makes DAX powerful. It evaluates an expression — any measure or aggregation — but first modifies the filter context. You pass in one or more filter conditions, and CALCULATE adds, replaces, or removes filters before running the expression. Every advanced DAX pattern — year-to-date, % of total, same period last year, running totals — is built on CALCULATE.

=CALCULATE(expression, filter1, [filter2], ...)

  expression  →  The measure or aggregation to evaluate
  filter1     →  A filter condition that modifies the context
  filter2+    →  Additional filter conditions (all applied together)

Examples:

North sales only — regardless of any PivotTable region filter:
North Revenue := CALCULATE(SUM(Sales[Revenue]), Customers[Region] = "North")

Sales of Electronics category only:
Electronics Revenue := CALCULATE([Total Revenue], Products[Category] = "Electronics")

Sales above £10,000 per order:
Large Orders Revenue := CALCULATE(SUM(Sales[Revenue]), Sales[Revenue] > 10000)

Combining two filters (AND logic — both must be true):
North Electronics := CALCULATE([Total Revenue],
  Customers[Region] = "North",
  Products[Category] = "Electronics"
)

CALCULATE — Same Expression, Different Context Each Time

Region (PivotTable row)	Total Revenue	North Revenue CALCULATE(…, Region="North")
North	£619,000	£619,000
East	£560,000	£619,000
West	£441,000	£619,000
Grand Total	£1,620,000	£619,000

North Revenue always shows North's total — CALCULATE overrides the Region filter in every row · useful for comparison benchmarks

ALL — Removing Filters

ALL is a filter function used inside CALCULATE to remove filters from a column or table. Its most common use is calculating percentages of total — you need the denominator to ignore the current filter so it always returns the grand total, while the numerator responds normally to the context.

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

  Numerator:    [Total Revenue] — responds to current filter context
  Denominator:  CALCULATE([Total Revenue], ALL(Sales))
                ALL(Sales) removes ALL filters from the Sales table
                so the denominator always returns the grand total

ALL on a single column — removes only that column's filter:
  CALCULATE([Total Revenue], ALL(Customers[Region]))
  Revenue is summed ignoring any Region filter — but Date, Product
  filters still apply

ALLEXCEPT — removes all filters except specified columns:
  CALCULATE([Total Revenue], ALLEXCEPT(Customers, Customers[Segment]))
  Clears all Customers filters EXCEPT Segment — so Segment still filters

% of Total — ALL Removes the Denominator's Filter

Region	Total Revenue	Grand Total CALCULATE(…,ALL(Sales))	% of Total
North	£619,000	£1,620,000	38.2%
East	£560,000	£1,620,000	34.6%
West	£441,000	£1,620,000	27.2%

Grand Total column is identical in every row — ALL(Sales) removed the Region filter so denominator is always the full total

FILTER — Adding a Table Filter Inside CALCULATE

FILTER is a table function that returns a filtered subset of a table. Inside CALCULATE, it lets you write more complex conditions than a simple column equals value — including conditions involving multiple columns, comparisons against measures, or logic that refers to related tables.

=FILTER(table, condition)
  Returns every row from table where condition is TRUE

Inside CALCULATE — revenue from orders over £5,000:
  High Value Revenue :=
    CALCULATE(
      SUM(Sales[Revenue]),
      FILTER(Sales, Sales[Revenue] > 5000)
    )

Inside CALCULATE — customers in North who are in the Corporate segment:
  North Corporate Revenue :=
    CALCULATE(
      [Total Revenue],
      FILTER(Customers,
        Customers[Region] = "North" &&
        Customers[Segment] = "Corporate"
      )
    )

Note — simple conditions use the shorthand directly in CALCULATE:
  CALCULATE([Total Revenue], Customers[Region] = "North")
  is equivalent to:
  CALCULATE([Total Revenue], FILTER(ALL(Customers), Customers[Region] = "North"))

Use FILTER when the condition is complex, involves row-level logic,
or needs to reference a measure rather than a column value.

High Value Revenue: only orders where individual Revenue > £5,000 are summed — changes with every PivotTable filter applied on top

A practical note on when to use FILTER vs direct column conditions in CALCULATE: for simple equality conditions like Region = "North", the shorthand directly in CALCULATE is cleaner and faster. Use FILTER when you need to test a condition against each row individually — especially when the condition involves a calculated value, a measure, or a comparison that cannot be expressed as a simple column filter.

COUNTROWS, DISTINCTCOUNT and HASONEVALUE

Beyond SUM and AVERAGE, three DAX functions that come up constantly in real reporting work are COUNTROWS, DISTINCTCOUNT, and HASONEVALUE. Each solves a specific counting or conditional pattern that would be difficult or impossible with standard Excel functions.

COUNTROWS — count the number of rows in a (filtered) table:
  Order Count    := COUNTROWS(Sales)
  North Orders   := CALCULATE(COUNTROWS(Sales), Customers[Region] = "North")

DISTINCTCOUNT — count unique values in a column:
  Unique Customers := DISTINCTCOUNT(Sales[CustomerID])
  Unique Products  := DISTINCTCOUNT(Sales[ProductID])
  (counts distinct values that appear in the filtered Sales rows)

HASONEVALUE — returns TRUE if a column is filtered to exactly one value:
  Selected Region :=
    IF(HASONEVALUE(Customers[Region]),
       VALUES(Customers[Region]),
       "All Regions"
    )
  Useful for dynamic titles and conditional measure behaviour —
  show a specific value when drilled down, a summary label otherwise.

VALUES — returns the unique values visible in the current context:
  When HASONEVALUE is TRUE, VALUES(column) returns that single value as text
  Used together they make measures that adapt to the selection state

Four Measures Working Together in a Summary Table

Region	Total Revenue	Order Count	Unique Customers	Avg per Order
North	£619,000	84	12	£7,369
East	£560,000	79	10	£7,089
West	£441,000	63	9	£7,000
Grand Total	£1,620,000	226	31	£7,168

Each measure evaluated independently per row context · Grand Total row shows model-wide aggregations — no extra formulas needed

DAX Operator Reference

Operator	Meaning	DAX example
=	Equal to	Region = "North"
<>	Not equal to	Category <> "Returns"
> < >= <=	Comparison	Revenue >= 10000
&&	AND (both true)	Region="North" && Year=2025
\|\|	OR (either true)	Region="North" \|\| Region="East"
&	Text concatenation	[First] & " " & [Last]
IN	Value in a list	Region IN {"North","East"}

💡 Teacher's Note

CALCULATE is to DAX what IF is to Excel — once you truly understand it, everything else falls into place. The mental model that helps most people: think of CALCULATE as a function that builds a new room. You step into that room with a modified version of reality — certain filters are added, removed, or replaced — and the expression inside evaluates in that new reality. The moment you step back out of the room, the original filter context resumes. That is why North Revenue always shows North's total even in the East row — inside the CALCULATE room, Region has been forcibly set to North. Outside the room, each row still sees its own region.

🟠 Practice

Q1. Write a DAX measure called "Electronics Revenue" that calculates Total Revenue for the Electronics category only, using CALCULATE.

Q2. You want a measure that shows % of total revenue — the current region's revenue divided by the grand total revenue across all regions. What goes in the denominator to make the grand total ignore the region filter?

Q3. What is the difference between COUNTROWS(Sales) and DISTINCTCOUNT(Sales[CustomerID])?

Next up — DAX Advanced, where we build on CALCULATE to tackle time intelligence patterns — year-to-date, same period last year, and rolling totals — along with RANKX, TOPN, and variables with VAR and RETURN for writing clean, readable measures.

← Previous Course Index Next →

Excel Course