Power BI DAX Functions: The Complete List With 40+ Examples, Sample Data and Fixes

Power BI DAX functions are the built-in formulas you use to write measures, calculated columns and calculated tables in Power BI. There are more than 250 of them, grouped into categories like aggregation, filter, time intelligence, logical and text. You only need about 40 to build almost any report, and CALCULATE is the one that matters most.

This guide is for analysts and report builders who know their way around Power BI Desktop but still copy DAX from forums without being sure why it works. Every example below runs on the same tiny sales model, with the expected result written next to it, so you can rebuild it and check your own numbers.

In this guide

• What are Power BI DAX functions?
• How to use Power BI DAX: your first measure
• How to write Power BI DAX: 7 syntax rules
• Power BI DAX functions list by category
• Power BI DAX functions with examples (40+)
• Power BI DAX examples: one sales model, 12 measures
• Row context vs filter context
• Power BI DAX query: test functions in DAX query view
• Power BI when to use DAX (and when Power Query wins)
• Common DAX function mistakes and fixes
• Next steps: tutorial path and documentation
• FAQ

What are Power BI DAX functions?

DAX stands for Data Analysis Expressions. It is the formula language of Power BI, Analysis Services and Power Pivot in Excel. A DAX function takes inputs (a column, a table, a number, another expression) and returns either a single value or a table.

That second part is what confuses people coming from Excel. Some DAX functions return a table, not a number. FILTER, ALL, VALUES and DATESYTD all return tables. You cannot drop them straight into a card visual; you pass them into another function, usually CALCULATE or an iterator like SUMX.

Where you use DAX functions

You write DAX in four places in Power BI Desktop:

• Measures: calculated on the fly for whatever filters the visual applies. This is where 90% of your DAX should live.
• Calculated columns: calculated once per row at refresh and stored in the model.
• Calculated tables: whole tables built from a DAX expression, such as a date table.
• DAX queries: written in DAX query view to test and explore, starting with EVALUATE.

Microsoft keeps the full list in the DAX function reference on Microsoft Learn. That page is the dictionary. This guide is the phrasebook: the functions you will actually say every day, and how they fit together.

How to use Power BI DAX: your first measure in 5 steps

If you have never written a measure, start here. It takes two minutes and every other example in this guide follows the same pattern.

1. Open your report in Power BI Desktop and go to Report view or Table view.
2. In the Data pane, right-click the table that should hold the measure (for example Sales) and choose New measure.
3. In the formula bar, type a name, an equals sign and the expression: Total Quantity = SUM(Sales[Quantity]).
4. Press Enter. The measure appears in the Data pane with a calculator icon.
5. Drag it into a card or table visual. Add Product[Category] to the same table and watch the number split by category.

That last step is the whole idea of DAX. You wrote one formula, and Power BI evaluated it once per category, because each row of the visual applies a different filter. Keep that picture in mind; it explains almost every function below.

How to write Power BI DAX: 7 syntax rules

Most DAX errors are not logic errors. They are syntax slips that the formula bar reports with a vague message. These seven rules prevent most of them.

1. Columns are always Table[Column]. Write Sales[Quantity], not just Quantity. Table names with spaces go in single quotes: 'Date'[Year].
2. Measures are referenced with brackets only. Write [Total Sales], without the table name. This makes it easy to tell measures and columns apart when you read a formula.
3. Text uses straight double quotes. "Computers" works; curly quotes pasted from a blog or Word do not.
4. Use VAR and RETURN for anything longer than one line. Variables are calculated once, make formulas readable and are easy to debug.
5. Use DIVIDE instead of the / operator. DIVIDE returns blank (or an alternate value you choose) instead of an error when the denominator is zero.
6. Separate arguments with commas (some regional settings use semicolons; Power BI Desktop follows your DAX separator setting).
7. Comment your logic with // for one line or /* ... */ for a block.

Avg Order Value =
VAR SalesAmount = [Total Sales]      // one value, calculated once
VAR OrderCount = [Orders]
RETURN
    DIVIDE(SalesAmount, OrderCount)  // blank instead of an error when 0

Power BI DAX functions list by category

Here is the full Power BI DAX functions list, grouped the way Microsoft Learn groups it, with what each category is for and the functions worth learning first. You do not need to memorise all 250+. Learn the starred ones in each row and look up the rest when a problem calls for them.

If you only learn one row, make it Filter. CALCULATE plus a filter argument solves more business questions than any other combination in DAX.

Power BI DAX functions with examples (40+)

This is the Power BI DAX functions list with examples you can paste straight into Power BI Desktop. All of them use the sample model described in the next section: a Sales table, a Product table and a Date table. Results are shown in comments so you can check your work.

Aggregation functions: SUM, COUNTROWS, DISTINCTCOUNT

Aggregations reduce a column to one number for the current filter context. They are the building blocks of every other measure.

Total Quantity  = SUM(Sales[Quantity])              // 40
Avg Unit Price  = AVERAGE(Sales[UnitPrice])         // 351.67
Smallest Order  = MIN(Sales[Quantity])              // 1
Largest Order   = MAX(Sales[Quantity])              // 20
Priced Lines    = COUNT(Sales[UnitPrice])           // 6 (non-blank numbers)
Orders          = COUNTROWS(Sales)                  // 6
Customers       = DISTINCTCOUNT(Sales[Customer])    // 4

Prefer COUNTROWS over COUNT when you mean "how many rows". It does not depend on a column being non-blank and reads more clearly.

Iterator functions: SUMX, AVERAGEX, MAXX, CONCATENATEX

Functions ending in X walk through a table row by row, evaluate an expression on each row, then aggregate the results. Use them whenever the math has to happen per row before you add it up.

Total Sales        = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])           // 4,640
Avg Sales per Cust = AVERAGEX(VALUES(Sales[Customer]), [Total Sales])        // 1,160
Biggest Line       = MAXX(Sales, Sales[Quantity] * Sales[UnitPrice])         // 1,600
Products Sold      = CONCATENATEX(VALUES(Product[Product]), Product[Product], ", ")

SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]) would give 40 x 2,110 = 84,400, which is nonsense. SUMX multiplies first, then adds. That one difference causes more wrong totals than anything else in DAX.

Power BI DAX CALCULATE and filter functions

CALCULATE evaluates an expression in a modified filter context. Its first argument is the measure; every argument after it adds, replaces or removes filters. The CALCULATE documentation lists every modifier it accepts.

Computers Sales   = CALCULATE([Total Sales], Product[Category] = "Computers")   // 3,890
Kept Filter Sales = CALCULATE([Total Sales], KEEPFILTERS(Product[Category] = "Computers"))
All Product Sales = CALCULATE([Total Sales], ALL(Product))                     // 4,640 on every row
Category Total    = CALCULATE([Total Sales], ALLEXCEPT(Product, Product[Category]))
Grand Total       = CALCULATE([Total Sales], REMOVEFILTERS())                   // 4,640
Big Orders Sales  = CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] >= 10))  // 750
Category Count    = COUNTROWS(VALUES(Product[Category]))                     // 2
Selected Category = SELECTEDVALUE(Product[Category], "All categories")

The difference between the first two lines shows up in a table split by category. Computers Sales shows 3,890 on every row, including the Accessories row, because the filter replaces the category filter. Kept Filter Sales shows 3,890 on Computers and blank on Accessories, because KEEPFILTERS intersects with it. We go deeper on this in our Power BI DAX filter guide on the Power BI tutorials page.

Power BI DAX IF, SWITCH and logical functions

Logical functions return different results depending on a condition. They work in both measures and calculated columns.

Target Status = IF([Total Sales] > 1000, "Above target", "Below target")

Sales Band =
SWITCH(
    TRUE(),
    [Total Sales] >= 2000, "High",      // Laptop: 2,450
    [Total Sales] >= 1000, "Medium",    // Monitor: 1,440
    "Low"                               // Mouse: 750
)

Sales or Zero = COALESCE([Total Sales], 0)

Use SWITCH(TRUE(), ...) instead of nested IFs once you have three or more branches. Conditions are checked top to bottom, so put the strictest first. COALESCE returns the first non-blank value, which is the cleanest way to show 0 instead of blank. Our Power BI DAX IF post has 20+ more patterns.

Math functions: DIVIDE and ROUND

Avg Order Value  = DIVIDE([Total Sales], [Orders])         // 773.33
Avg Order Rounded = ROUND([Avg Order Value], 0)           // 773
Sales Share      = DIVIDE([Total Sales], [Grand Total])   // Computers: 83.8%

Format the result as a percentage in the Measure tools ribbon rather than multiplying by 100. The number stays a true ratio, so other measures can reuse it.

Relationship functions: RELATED, USERELATIONSHIP, CROSSFILTER

Relationship functions let a formula reach across the model. RELATED goes from the many side to the one side (from Sales to Product); RELATEDTABLE goes the other way.

// calculated column on Sales
Category = RELATED(Product[Category])

// calculated column on Product
Order Lines = COUNTROWS(RELATEDTABLE(Sales))              // Laptop: 2

// calculated column on Sales, when no relationship exists
Category Lookup = LOOKUPVALUE(Product[Category], Product[ProductKey], Sales[ProductKey])

// measure: use an inactive relationship (needs Sales[ShipDate] linked to Date, inactive)
Sales by Ship Date = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]))

// measure: count products sold under the current filters
Products in Context =
CALCULATE(
    DISTINCTCOUNT(Product[ProductKey]),
    CROSSFILTER(Sales[ProductKey], Product[ProductKey], BOTH)
)

USERELATIONSHIP only works inside CALCULATE (or CALCULATETABLE). Several top-ranking tutorials show it on its own, which will not run.

Time intelligence functions: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD

Time intelligence compares periods: year to date, same period last year, previous month, rolling three months. Every one of these needs a date table with one row per day and no gaps, marked as a date table.

Sales YTD        = TOTALYTD([Total Sales], 'Date'[Date])                          // Feb 2026: 2,190
Sales YTD (alt)  = CALCULATE([Total Sales], DATESYTD('Date'[Date]))
Sales LY         = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))      // 2026 row: 2,450
Sales PM         = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH))      // Feb 2026: 850
Sales Last 3M    = CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
YoY %            = DIVIDE([Total Sales] - [Sales LY], [Sales LY])                  // 2026: -10.6%

Date and time functions: DATEDIFF, TODAY, EOMONTH, CALENDAR

// calculated table: a simple date table
Date = CALENDAR(DATE(2025, 1, 1), DATE(2026, 12, 31))

// calculated columns on Sales
Days Since Order = DATEDIFF(Sales[OrderDate], TODAY(), DAY)
Month End        = EOMONTH(Sales[OrderDate], 0)

TODAY() in a calculated column is only refreshed when the model refreshes. If you need "days since" to move every day without a refresh, put it in a measure instead.

Text functions: FORMAT, LEFT, UPPER, CONTAINSSTRING

Order Label  = Sales[Customer] & " | " & FORMAT(Sales[OrderDate], "mmm yyyy")   // Asha | Jan 2025
Product Code = UPPER(LEFT(Product[Product], 3))                                  // LAP
Is Laptop    = CONTAINSSTRING(Product[Product], "lap")                           // TRUE for Laptop

FORMAT returns text, so a formatted number will sort alphabetically and break totals. Use it for labels, not for values you plan to calculate with.

Information functions: ISBLANK, HASONEVALUE, ISINSCOPE

Sales Single Product = IF(HASONEVALUE(Product[Product]), [Total Sales])   // blank on the total row
No Sales Flag        = IF(ISBLANK([Total Sales]), "No sales", "Has sales")
Row Level Label      = IF(ISINSCOPE(Product[Product]), "Product", "Category or total")

ISINSCOPE is the right tool for matrix visuals with a hierarchy: it tells you which level of the hierarchy the current cell belongs to.

Table and ranking functions: TOPN, RANKX, OFFSET

Top 2 Product Sales =
CALCULATE([Total Sales], TOPN(2, ALL(Product[Product]), [Total Sales], DESC))  // 3,890

Product Rank = RANKX(ALL(Product[Product]), [Total Sales], , DESC, DENSE)       // Laptop: 1

Sales Prev Year (window) =
CALCULATE(
    [Total Sales],
    OFFSET(-1, ALLSELECTED('Date'[Year]), ORDERBY('Date'[Year]))
)                                                                             // 2026 row: 2,450

OFFSET is one of the newer window functions. Unlike SAMEPERIODLASTYEAR, it works on any sorted column, not only dates, so you can use it for "previous product in the list" or "previous fiscal week" patterns.

Power BI DAX examples: one sales model, 12 measures

Most DAX tutorials show formulas without data, so you cannot tell if your result is right. Here is the exact model behind every number in this guide. Build it in two minutes with Home > Enter data, then create relationships from Sales[ProductKey] to Product[ProductKey] and from Sales[OrderDate] to 'Date'[Date].

Sample data

The Product table has three rows: 1 Laptop (Computers), 2 Mouse (Accessories), 3 Monitor (Computers). The Date table is the CALENDAR table from the date section, with Year and Month columns added: Year = YEAR('Date'[Date]) and Month = FORMAT('Date'[Date], "mmm").

The 12 measures and what they should return

Total Sales     = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])   // 4,640
Total Quantity  = SUM(Sales[Quantity])                              // 40
Orders          = COUNTROWS(Sales)                                  // 6
Customers       = DISTINCTCOUNT(Sales[Customer])                    // 4
Avg Order Value = DIVIDE([Total Sales], [Orders])                   // 773.33
Computers Sales = CALCULATE([Total Sales], Product[Category] = "Computers")   // 3,890
% of All Sales  = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Product)))
Sales LY        = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY %           = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Sales YTD       = TOTALYTD([Total Sales], 'Date'[Date])
Product Rank    = RANKX(ALL(Product[Product]), [Total Sales], , DESC, DENSE)
Sales Band      = SWITCH(TRUE(), [Total Sales] >= 2000, "High", [Total Sales] >= 1000, "Medium", "Low")

Check your results

Put Product[Category] in a table visual with Total Sales and % of All Sales. You should see Computers 3,890 (83.8%) and Accessories 750 (16.2%). Swap Category for 'Date'[Year] and add Sales LY and YoY %: 2025 shows 2,450 with a blank LY, and 2026 shows 2,190, LY 2,450 and YoY -10.6%.

If your numbers differ, the cause is nearly always a missing relationship or a date table that is not marked. Fix those before you touch the formulas. Once this works, give it a clean look in seconds with the Power BI theme builder, so your practice report looks like something you would show a manager.

Row context vs filter context: why functions behave differently

Every DAX function runs inside a context. Understanding the two kinds is the moment DAX stops feeling random.

Filter context

Filter context is the set of filters coming from the visual, slicers, page filters and CALCULATE. In a table split by category, the Computers row has a filter Product[Category] = "Computers". SUM, COUNTROWS and every measure only see the rows that pass those filters. CALCULATE is the only function that can change filter context directly.

Row context

Row context means "the current row". It exists in calculated columns and inside iterators like SUMX and FILTER. In row context you can write Sales[Quantity] * Sales[UnitPrice] and DAX knows which row you mean. A plain measure has no row context, which is why = Sales[Quantity] in a measure gives an error.

Context transition

When you call a measure inside an iterator, CALCULATE (hidden inside every measure) turns the current row into a filter. That is called context transition. It is why AVERAGEX(VALUES(Sales[Customer]), [Total Sales]) works: for each customer, [Total Sales] is calculated for that customer only. Asha 2,200, Ben 750, Chen 850, Dana 840, average 1,160.

Power BI DAX query: test functions in DAX query view

You do not need a visual to test a function. Power BI Desktop has a DAX query view (the fourth icon on the left rail) where you run a Power BI DAX query and see the result as a table. It is the fastest way to debug a measure.

EVALUATE
SUMMARIZECOLUMNS(
    Product[Category],
    "Sales", [Total Sales],
    "Orders", [Orders]
)
ORDER BY [Sales] DESC

// Result:
// Computers    3890   4
// Accessories   750   2

You can also define a measure only for the query, test it, and then click Update model once you are happy with it:

DEFINE
    MEASURE Sales[Big Order Sales] =
        CALCULATE([Total Sales], Sales[Quantity] >= 10)
EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Year],
    "Big order sales", [Big Order Sales]
)

// 2025   250
// 2026   500

Table functions for queries: ADDCOLUMNS and SELECTCOLUMNS

EVALUATE
ADDCOLUMNS(
    VALUES(Product[Product]),
    "Sales", [Total Sales],
    "Rank", [Product Rank]
)

EVALUATE
SELECTCOLUMNS(Sales, "Who", Sales[Customer], "Qty", Sales[Quantity])

Any function that returns a table can sit after EVALUATE. Use this to look inside FILTER, VALUES or DATESYTD and see exactly which rows they return.

Power BI when to use DAX (and when Power Query wins)

DAX is not the only formula language in Power BI. Power Query uses M to load and shape data before it reaches the model. Picking the wrong one makes models slow and hard to maintain.

The rule I use: if the result depends on what the user filters, it is DAX. If it is a fixed property of a row, do it in Power Query. Splitting a full name, fixing data types, merging a lookup table or removing duplicates all belong in Power Query. Totals, ratios, ranks, year-over-year and anything that should react to a slicer belong in DAX measures.

Power BI DAX vs Power Query at a glance

When people search Power BI DAX vs Power Query, the honest answer is that you need both. A clean model from Power Query makes every DAX function shorter.

Common DAX function mistakes and fixes

These are the errors I see most in real reports, including in several of the tutorials that rank for this topic.

How to debug a function step by step

1. Copy the formula into DAX query view and wrap the inner part in EVALUATE to see the table it returns.
2. Break the formula into VAR steps and RETURN each variable on its own until one gives an unexpected value.
3. Check the model view: is the relationship active, single direction, on the right columns?
4. Test with a single category or year in a slicer so you can verify the number by hand against the sample data.

Next steps: Power BI DAX tutorial path and documentation

You now have the functions that cover most reports. If you want a Power BI DAX tutorial path, learn them in this order: aggregations, DIVIDE and VAR, CALCULATE with simple filters, iterators, time intelligence, then ranking and window functions. Rebuild the sample model above at each step.

For the full Power BI DAX documentation, bookmark the Microsoft Learn function reference; every function page has syntax, return type and remarks about edge cases. For Power BI DAX practice online free, the best option is still Power BI Desktop itself, which costs nothing, plus the Enter data model from this guide. Change the numbers, predict the result, then check it.

Once your measures work, the next thing your report needs is a consistent look. Start from one of our Power BI templates or generate a theme in the theme builder, and spend your time on Power BI DAX functions instead of fiddling with colours. More deep dives on FILTER, IF and FORMAT are on our tutorials page.

FAQ

What are the most important DAX functions in Power BI?

CALCULATE, SUM, SUMX, DIVIDE, COUNTROWS, DISTINCTCOUNT, FILTER, ALL, IF, SWITCH, RELATED and TOTALYTD cover most real reports. Learn CALCULATE first, because almost every business question is a measure under a different filter.

How many DAX functions are there in Power BI?

Microsoft Learn documents more than 250 DAX functions across about 15 categories. Most analysts use 30 to 50 of them regularly, and new ones such as window and INFO functions are added from time to time.

What is the difference between a DAX function and a measure?

A function is a built-in building block like SUM or CALCULATE. A measure is a named formula you create that uses one or more functions, such as Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]).

What is the difference between SUM and SUMX in DAX?

SUM adds up one column. SUMX evaluates an expression for each row of a table and then adds the results, so use SUMX whenever you need to multiply or combine columns per row before totalling.

Is DAX hard to learn?

The syntax is easy if you know Excel formulas. The hard part is filter context and context transition; once you understand how CALCULATE changes filters, most functions become predictable.

Is DAX the same as Excel formulas?

Many function names match Excel, such as SUM, IF and LEFT, but DAX works on whole columns and tables instead of cells. There are no cell references in DAX, and results change with the filters in each visual.

Where can I find the official Power BI DAX documentation?

The DAX function reference on Microsoft Learn lists every function with syntax, parameters, return values and examples. DAX query view in Power BI Desktop is the best place to try them out.

Can I practice DAX online for free?

Yes. Power BI Desktop is a free download for Windows, and you can build a practice model with Enter data in minutes. Microsoft Learn also has free DAX learning modules with sample files.