DAX Functions in Power BI With Examples: The 20 You'll Use Most

DAX (Data Analysis Expressions) is the formula language of Power BI, used to write measures, calculated columns and calculated tables. Microsoft's DAX reference lists hundreds of functions, but most real reports run on about twenty of them: aggregations like SUM and DISTINCTCOUNT, iterators like SUMX, filter functions like CALCULATE and REMOVEFILTERS, a few logical and text functions, and time intelligence like TOTALYTD. This post covers those twenty with examples you can paste into your own model.

I'm Gulab Chand Tejwani, Microsoft Certified Trainer. I've taught DAX to working analysts for years, and the pattern is always the same: people try to learn the function list alphabetically and get lost. It works much better to learn functions by the job they do.

What is a DAX function in Power BI?

A DAX function is a named, built-in formula, such as SUM or CALCULATE, that you call inside a DAX expression. You use DAX in three places:

• Measures, which are calculated at query time for whatever filters are on the visual. Total Sales by month, by region or for one product is one measure.
• Calculated columns, which are calculated once per row during refresh and stored in the model.
• Calculated tables, which build a whole table from an expression, a date table being the classic example.

Here's the model the examples use: a Sales fact table with one row per order line (OrderNumber, OrderDate, ShipDate, ProductKey, CustomerKey, Channel, Quantity, UnitPrice, SalesAmount), related to Product, Customer and a marked Date table. OrderDate has the active relationship to Date, and ShipDate an inactive one. A few examples also use a [Sales Target] measure from a separate targets table. If that layout is new to you, the CALCULATE explainer walks through filter context on the same kind of model.

Which aggregation functions should you learn first?

These collapse a column to one number for the current filter context.

Total Sales = SUM ( Sales[SalesAmount] )

Average Line Value = AVERAGE ( Sales[SalesAmount] )

Orders = DISTINCTCOUNT ( Sales[OrderNumber] )

Customers = DISTINCTCOUNT ( Sales[CustomerKey] )

Order Lines = COUNTROWS ( Sales )

Largest Line = MAX ( Sales[SalesAmount] )

1. SUM adds a numeric column.
2. AVERAGE is the arithmetic mean of a column. Because each row here is an order line, this is the average line value, not the average order value. For that, see AVERAGEX below.
3. DISTINCTCOUNT counts unique values. Use it for "how many orders" or "how many customers bought", where COUNT would count every order line.
4. COUNTROWS counts rows in a table. I prefer it to COUNT on a key column because it says what you mean.
5. MIN / MAX return the smallest or largest value.

When do you need iterator functions like SUMX?

When the number you want doesn't exist in a single column. Iterators (the functions ending in X) loop over a table row by row, evaluate an expression for each row, then aggregate the results.

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Avg Revenue per Customer =
AVERAGEX (
    VALUES ( Customer[CustomerKey] ),
    [Revenue]
)

Average Order Value =
AVERAGEX (
    VALUES ( Sales[OrderNumber] ),
    [Total Sales]
)

6. SUMX multiplies quantity by price on each row, then adds the results. SUM(Sales[Quantity]) * SUM(Sales[UnitPrice]) gives the wrong answer, because it multiplies two totals.
7. AVERAGEX averages an expression over a table. Here it averages revenue per customer, and then sales per order, which is what "average order value" should mean when each row is an order line.

A rule I give every batch: if you're about to add a calculated column only so you can SUM it, try an iterator in a measure first. Your model stays smaller.

How do CALCULATE and the filter functions work?

This is the group that makes DAX powerful, and the one people struggle with.

Sales Online = CALCULATE ( [Total Sales], Sales[Channel] = "Online" )

% of All Sales =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )
)

Big Lines = CALCULATE ( [Order Lines], FILTER ( Sales, Sales[SalesAmount] > 50000 ) )

Category Share =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Product[Category] ) ) )

8. CALCULATE evaluates an expression in a modified filter context. Add filters, replace them or remove them.
9. FILTER returns a table of rows that meet a condition. Use it inside CALCULATE when a simple column = value filter isn't enough.
10. ALL / REMOVEFILTERS ignore filters on a table or column. REMOVEFILTERS reads more clearly when that's all you're doing.
11. ALLSELECTED keeps the user's slicer selections but ignores the filter from the visual's own rows. It's useful for "share of what I'm looking at".
12. DIVIDE is safe division that returns blank (or an alternative) instead of an error when the denominator is zero.

CALCULATE deserves its own post, and it has one: DAX CALCULATE explained with examples.

Function	What it does	Typical use
REMOVEFILTERS	Removes filters from a table or column	Percentage of grand total
ALL	Same as REMOVEFILTERS inside CALCULATE; can also return a table	Older code, or when you need the table
ALLEXCEPT	Removes all filters except the listed columns	Total for the category while ignoring sub-category
ALLSELECTED	Keeps outer slicer selections, ignores the visual's rows	Share of the visible total
KEEPFILTERS	Adds a filter without overwriting the existing one	Intersecting with what's already selected

Which logical and text functions come up most?

Sales Band =
SWITCH (
    TRUE (),
    [Total Sales] >= 1000000, "High",
    [Total Sales] >= 250000, "Medium",
    "Low"
)

Target Met = IF ( [Total Sales] >= [Sales Target], "Yes", "No" )

-- Calculated column on Customer
Customer Label = CONCATENATE ( Customer[FirstName], " " & Customer[LastName] )

Sales (formatted) = FORMAT ( [Total Sales], "₹#,##0" )

13. IF returns one value when a condition is true and another when it's false.
14. SWITCH replaces nested IFs. The SWITCH(TRUE(), ...) pattern checks conditions in order and is far easier to read.
15. CONCATENATE / & join text. The & operator handles more than two pieces without nesting.
16. FORMAT turns a number into text with a format string. Use it sparingly, because the result is text: it won't sort numerically and it can't be summed.

How do RELATED and USERELATIONSHIP work?

-- Calculated column on Sales
Product Category = RELATED ( Product[Category] )

Sales by Ship Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )

17. RELATED fetches a value from the "one" side of a relationship into a row on the "many" side. It works in calculated columns and inside iterators.
18. USERELATIONSHIP switches on an inactive relationship for one calculation. The classic case is a Sales table with both OrderDate and ShipDate pointing at one Date table.

Which time intelligence functions matter most?

Time intelligence needs a proper date table marked as a date table, with one row per day and no gaps.

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

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

19. TOTALYTD gives a running total from the start of the year. For an Indian April to March financial year, pass the year-end date. The clearest way is CALCULATE([Total Sales], DATESYTD('Date'[Date], "31/3")); with TOTALYTD itself, the year-end date is the fourth argument, after a filter: TOTALYTD([Total Sales], 'Date'[Date], ALL('Date'), "31/3"). The date string is read in your model's locale: "31/3" works in a day-first locale such as English (India), while a month-first locale such as English (US) needs "3/31".
20. SAMEPERIODLASTYEAR shifts the dates in context back one year, for last-year comparisons.

What DAX mistakes do beginners make?

• Calculated columns for totals. A column like Sales[Total] = SUM(Sales[SalesAmount]) puts the grand total on every row. You wanted a measure.
• Multiplying two SUMs. Use SUMX for row-level maths.
• Using / instead of DIVIDE. One empty month and your visual shows errors.
• No date table. Time intelligence on the auto date hierarchy gives odd results and bloats the model.
• FORMAT on everything. Formatted text breaks sorting and totals. Use the format pane on the measure instead.
• Long measures without variables. VAR makes measures readable and stops the same expression being evaluated twice.

YoY % (with variables) =
VAR CurrentSales = [Total Sales]
VAR LastYear = [Sales LY]
RETURN
    DIVIDE ( CurrentSales - LastYear, LastYear )

Practice questions: test your DAX

Question 1DAX · Iterators

Your Sales table has Quantity and UnitPrice but no revenue column. Which measure returns correct total revenue at every level of a report?

1. ARevenue = SUM(Sales[Quantity]) * SUM(Sales[UnitPrice])
2. BRevenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
3. CRevenue = CALCULATE(SUM(Sales[Quantity]), Sales[UnitPrice])
4. DRevenue = SUM(Sales[Quantity] * Sales[UnitPrice])

Show answer and explanation+

Correct answer: B

SUMX multiplies on each row and then adds. Option A multiplies two totals, which is wrong as soon as there's more than one row. C isn't valid filter syntax, and D fails because SUM accepts a column, not an expression.

Question 2DAX · Filter context

A matrix shows Total Sales by Product Category. You want each row to show that category's share of all categories, ignoring the category filter on the row. Which denominator is right?

1. A[Total Sales]
2. BCALCULATE([Total Sales], Product[Category] = "Bikes")
3. CCALCULATE([Total Sales], REMOVEFILTERS(Product[Category]))
4. DSUMX(Product, [Total Sales])

Show answer and explanation+

Correct answer: C

REMOVEFILTERS on the category column gives the all-category total on every row, so DIVIDE([Total Sales], <that>) returns the share. A gives 100% everywhere, B always uses Bikes, and D still respects the row's category filter.

Question 3DAX · Measures vs columns

A colleague added a calculated column Sales[Customer Count] = DISTINCTCOUNT(Sales[CustomerKey]) and the visual shows huge numbers. What is the fix?

1. AMake it a measure instead of a calculated column
2. BChange DISTINCTCOUNT to COUNT
3. CWrap it in CALCULATE
4. DSet the column's summarization to Don't summarize

Show answer and explanation+

Correct answer: A

In a calculated column, DISTINCTCOUNT is evaluated per row without a filter, so every row stores the overall count, and the visual then sums those copies. A measure evaluates once per filter context, which is what you want.

Question 4DAX · Relationships

Sales has OrderDate (active relationship to Date) and ShipDate (inactive relationship to Date). You need sales by ship date in a separate measure. Which function do you use inside CALCULATE?

1. ARELATED
2. BCROSSFILTER
3. CTREATAS on the OrderDate column
4. DUSERELATIONSHIP

Show answer and explanation+

Correct answer: D

USERELATIONSHIP activates the inactive relationship for that calculation only: CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Date'[Date])). RELATED pulls a value across an existing active relationship, and CROSSFILTER changes direction, not which relationship is active.

Question 5DAX · Time intelligence

Your company's financial year runs April to March, and the model uses a day-first locale (English, India). Which measure gives financial-year-to-date sales?

1. ATOTALYTD([Total Sales], 'Date'[Date])
2. BCALCULATE([Total Sales], DATESYTD('Date'[Date], "31/3"))
3. CTOTALQTD([Total Sales], 'Date'[Date])
4. DSAMEPERIODLASTYEAR('Date'[Date])

Show answer and explanation+

Correct answer: B

DATESYTD's year-end date argument moves the year boundary to 31 March. Option A has no year-end date, so it resets on 1 January. D returns a table of dates, not a sales value.

How do you get good at DAX quickly?

Write measures for real questions, not exercises. Take a dataset you know (your own sales export, a public dataset) and answer ten business questions with measures: total, average order value, unique customers, share of total, year to date, last year, growth. Check every number against Excel the first time. That habit catches most mistakes before your manager does.

If you want structure, the Power BI Mastery course has a DAX module with labs, datasets and a quiz after every lesson. The whole course is 83 lessons across 14 modules, with 966 quiz questions and 8 PL-300 practice exams, for ₹1,999 (personal access). If you're aiming for certification, the PL-300 exam guide explains how DAX is tested, and Power BI projects for beginners gives you projects to practise on.

FAQ

How many DAX functions are there in Power BI?

Microsoft's DAX function reference lists several hundred functions across categories such as aggregation, date and time, filter, logical, text, time intelligence and table manipulation. Most reports need only twenty or thirty of them.

What is the most important DAX function?

CALCULATE. It evaluates an expression in a filter context you modify (CALCULATETABLE does the same for table expressions), and most useful measures (share of total, year to date, last year, filtered totals) are built on it.

What is the difference between SUM and SUMX?

SUM adds up one column. SUMX goes row by row through a table, evaluates an expression for each row (for example Quantity times UnitPrice) and then adds up the results.

Should I use a measure or a calculated column?

Use a measure for anything you aggregate or that should respond to slicers. Use a calculated column only when you need a value stored per row, for example to slice or group by it.

Is DAX hard to learn?

The syntax is easy. Filter context is the hard part, and it takes practice with real data. Most analysts get comfortable within a few weeks of writing measures regularly.