Power BI DAX Functions: Complete Beginner's Guide

DAX (Data Analysis Expressions) is the formula language at the heart of Power BI, Power Pivot, Analysis Services Tabular, and the semantic models behind Microsoft Fabric. It is what turns a passive data model into a calculation engine — defining measures, computed columns, dynamic filters, and the arithmetic that drives every dashboard interaction.

This guide covers DAX from first principles: what it is, how it differs from Excel, when to use measures versus calculated columns, the essential functions every report builder needs, and the filter-context concept that separates beginners from competent DAX authors.

What Is DAX?

DAX is a functional, expression-based language designed for analytical calculations over tabular data models. It was introduced in 2009 with PowerPivot for Excel and now powers every Microsoft tabular engine: Power BI semantic models, Analysis Services Tabular, Power BI Premium / Fabric capacities, and Power Pivot.

A DAX expression always evaluates to a single value, a scalar — never a table for visual purposes (although intermediate steps can produce tables). It is read-only: DAX queries data, it does not modify it.

DAX has roughly 250 built-in functions covering aggregation, filtering, time intelligence, ranking, string manipulation, conditional logic, and table-valued operations. The vast majority of real reports use a few dozen of them.

DAX vs Excel Formulas

DAX looks superficially like Excel — same function-name style, same operators. The differences are significant.

• No cells. Excel formulas reference cells; DAX references columns, tables, and measures. There is no concept of "row 5" except inside a row context.
• Tabular thinking. Every operation is over an entire column or table at once. SUM(Sales[Amount]) sums the entire column, respecting current filters.
• Measures are reusable. A measure defined once is used in any visual, any report. There is no copy-paste of formulas across cells.
• Filter context everywhere. A measure does not specify which rows to aggregate; the visual provides the filter. The same measure shown in a slicer-filtered card returns a different number than the same measure in an unfiltered card.

Engineers coming from SQL find DAX more familiar than Excel users do — measures resemble SELECT-list expressions over an implicit GROUP BY. Excel users tend to over-rely on calculated columns at first; SQL users tend to under-use row context.

Calculated Columns vs Measures

The single most important DAX decision is column-or-measure. The wrong choice is the most common cause of slow models, broken filters, and confusing results.

Calculated columns are computed during data refresh and stored on disk as part of the table. They are visible row-by-row, can be used in slicers, and behave like ordinary columns. Use them for row-level categorizations: a price tier label, a fiscal year derived from a date, a flag for high-value customers.

Measures are computed at query time, in response to the visual asking for them. They cannot be placed in slicers (they have no individual row values), but they evaluate under the visual's full filter context — meaning the same measure produces different values in different visuals automatically. Use measures for aggregations, KPIs, ratios, and anything that needs to respect filters and slicers.

Rule of thumb: default to measures. If you cannot do it in a measure, then consider a calculated column. If you cannot do it in a calculated column, push the work into Power Query (M) before the data lands in the model.

Essential DAX Functions

The list below is everything most reports need. Master these and you can write 90% of production DAX.

Aggregation

SUM(Sales[Amount])
AVERAGE(Sales[UnitPrice])
COUNTROWS(Sales)
DISTINCTCOUNT(Sales[CustomerID])
MIN(Sales[OrderDate]) / MAX(Sales[OrderDate])

SUMX, AVERAGEX, etc. are the iterator versions. They compute an expression row-by-row over a table and aggregate the result. Use them when the calculation depends on multiple columns: SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]).

CALCULATE

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

Sales 2024 :=
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Year] = 2024
)

Anywhere this measure is placed, it returns 2024 sales — overriding any year filter on the visual. Filters can be added, removed (REMOVEFILTERS, ALL), or transformed inside CALCULATE.

FILTER

FILTER returns a table containing rows that satisfy a condition. Often used as an argument to CALCULATE when the filter logic is more complex than a simple equality:

High Value Sales :=
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 10000)
)

RELATED and RELATEDTABLE

Used to traverse relationships in calculated columns. RELATED follows a many-to-one relationship; RELATEDTABLE follows a one-to-many.

Customer Region := RELATED(Customer[Region])

IF, SWITCH, IFERROR

Conditional logic. SWITCH(TRUE(), ...) is the idiomatic way to write multi-branch conditionals.

VAR and RETURN

Variables make complex DAX readable and faster — DAX reuses computed values within a single expression.

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

DIVIDE

Always use DIVIDE(numerator, denominator) instead of /. DIVIDE handles divide-by-zero gracefully (returns BLANK by default) and is faster.

Time Intelligence

DAX time intelligence functions require a properly marked date table — a contiguous list of dates marked as the model's date table. Once you have one, time-based comparisons are trivial.

// Same period last year
Sales LY :=
CALCULATE(
    [Sales],
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Year-to-date
Sales YTD :=
TOTALYTD([Sales], 'Date'[Date])

// Rolling 12 months
Sales R12M :=
CALCULATE(
    [Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
)

Other essentials: DATEADD (shift periods), DATESYTD, DATESQTD, DATESMTD, PARALLELPERIOD, FIRSTDATE / LASTDATE.

Filter Context Explained

Filter context is the breakthrough concept. Every DAX measure is evaluated under a filter context — the set of filters currently applied. The filter context comes from:

• Slicers on the page
• The row, column, or filter wells of the visual
• Page-level and report-level filters
• Any CALCULATE modifier in the measure itself

The same measure SUM(Sales[Amount]) placed in a card with no filter shows total sales; placed in a matrix with rows by Region, it shows sales per region; placed inside a year slicer, it shows sales for the selected year. The measure does not "know" any of this — DAX simply evaluates the formula under whatever filters apply.

Row context is a different beast. It exists inside iterator functions (SUMX, FILTER) and calculated columns. Row context is "this current row"; filter context is "these are the filters from the outside world." CALCULATE converts row context into filter context — a transition that is the cause of more confused beginners than any other DAX behavior.

Common DAX Patterns

Year-over-Year %

Sales YoY % :=
DIVIDE(
    [Sales] - [Sales LY],
    [Sales LY]
)

Running Total

Sales Running Total :=
CALCULATE(
    [Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

% of Total

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

Top N Customers

Sales Top 10 Customers :=
CALCULATE(
    [Sales],
    TOPN(10, ALL(Customer[CustomerID]), [Sales])
)

Last Refresh Date

Last Refresh := MAX(Sales[OrderDate])

Performance Tips

• Prefer measures to calculated columns. Calculated columns inflate model size and refresh time. A model with 200 columns of which 100 are calculated has poor performance and is hard to maintain.
• Push transformations to Power Query. If a column can be computed in M during refresh, do it there — it does not consume runtime DAX engine resources.
• Use VAR aggressively. Variables are evaluated once and reused. Re-typing a sub-expression makes DAX recompute it.
• Avoid bidirectional cross-filtering. It is rarely needed and almost always a performance hit. Solve relationship problems with proper modeling instead.
• Star-schema your model. Snowflake schemas and many-to-many relationships are correct in some cases but slow in many. A clean star schema is the foundation of fast Power BI.
• Use DAX Studio. Free, open-source, indispensable. Run server timings on slow queries and find the bottleneck.
• Test in Tabular Editor. The community-maintained editor is faster for measure development than Power BI Desktop and lets you write Best Practice Analyzer rules to catch common mistakes.

Resources

The DAX learning curve is steep, but the resources are excellent:

• SQLBI / Marco Russo & Alberto Ferrari — the canonical DAX reference, deep articles, books, and online training.
• DAX Patterns (daxpatterns.com) — copy-paste-ready solutions for common reporting needs.
• Microsoft Learn — DAX Function Reference — official documentation for every function.
• DAX Studio — query and performance analysis tool.
• Tabular Editor — model authoring and Best Practice Analyzer.
• Dawiso glossary — see related guides on Power BI data modeling, row-level security, and Power Query.

DAX rewards investment. The first month is hard — filter context is genuinely subtle, and bad early habits stick. After that, the language is concise, expressive, and powerful enough to drive most enterprise analytical reporting Microsoft customers will ever need.