DAX Functions in Power BI: 25 Formulas Every Analyst Uses, with Examples on One Sales Model

What sample model do the DAX examples use?

Every example uses the same small star schema. You can type these tables into Power BI Desktop with Home > Enter data and follow along.

Sales (the fact table, 10 orders):

Products: P1 Laptop (Electronics, unit cost ₹42,000), P2 Headphones (Electronics, ₹900), P3 Desk Lamp (Home, ₹450).

Customers: C1 Mumbai (Business), C2 Pune (Consumer), C3 Delhi (Business), C4 Bengaluru (Consumer).

Date: a calendar table built with DAX in function 22 below.

Relationships are one-to-many from Products[ProductID], Customers[CustomerID] and 'Date'[Date] to the matching columns in Sales. The expected results below were cross-checked by recomputing the same logic in SQL on these rows.

What is the difference between row context and filter context in DAX?

Filter context is the set of filters active when a measure is calculated: the row and column of a matrix, slicers, page filters and any filters added by CALCULATE. When a table visual shows Mumbai in one row, the measure in that row only "sees" Mumbai's sales.

Row context means "the current row". It exists in calculated columns and inside iterator functions such as SUMX and FILTER, which walk through a table one row at a time. Row context lets you read column values from that row, but it does not filter anything by itself.

Two small tests make this concrete. First, this calculated column works, because a calculated column has a row context:

LineTotal = Sales[Quantity] * Sales[UnitPrice]

The same expression written as a measure fails, because a measure has no current row. That is why measures use iterators like SUMX instead.

Second, add these two calculated columns to the Customers table:

Qty Wrong = SUM ( Sales[Quantity] )
Qty Right = CALCULATE ( SUM ( Sales[Quantity] ) )

Qty Wrong shows the grand total, 23, on every row, because row context does not filter the Sales table. CALCULATE performs context transition: it turns the current row into a filter, so each customer gets only their own quantity. Once this clicks, most confusing DAX results start to make sense.

Aggregation functions (1 to 8)

1. SUM

Adds up one numeric column. Syntax: SUM(<column>).

Total Quantity = SUM ( Sales[Quantity] )

Result for the whole model: 23.

2. SUMX

Iterates a table, evaluates an expression for each row, then adds the results. Use it whenever you need to multiply or combine columns before summing. Syntax: SUMX(<table>, <expression>).

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

Result: ₹2,75,100. Revenue is reused in most examples below.

3. AVERAGE

The simple mean of a column. Syntax: AVERAGE(<column>).

Avg Unit Price = AVERAGE ( Sales[UnitPrice] )

Result: ₹21,375. This ignores quantity, so it is the average listed price per order line, not a weighted price.

4. AVERAGEX

Averages an expression evaluated row by row. Syntax: AVERAGEX(<table>, <expression>).

Avg Order Value = AVERAGEX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Result: ₹27,510 (₹2,75,100 across 10 orders, since each row is one order).

5. COUNTROWS

Counts rows in a table. Syntax: COUNTROWS(<table>).

Orders = COUNTROWS ( Sales )

Result: 10. Prefer this over COUNT on an ID column when you mean "number of rows".

6. DISTINCTCOUNT

Counts unique values in a column. Syntax: DISTINCTCOUNT(<column>).

Customers Buying = DISTINCTCOUNT ( Sales[CustomerID] )

Result: 4.

7. MIN and MAX

Return the smallest or largest value in a column. Syntax: MIN(<column>), MAX(<column>).

First Order Date = MIN ( Sales[OrderDate] )
Last Order Date = MAX ( Sales[OrderDate] )

Results: 15-01-2025 and 20-03-2026. In a table by customer, these become each customer's first and last order.

8. DIVIDE

Safe division that returns blank (or an alternate value) instead of an error when the denominator is zero. Syntax: DIVIDE(<numerator>, <denominator>[, <alternateresult>]).

Cost = SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )
Profit = [Revenue] - [Cost]
Margin % = DIVIDE ( [Profit], [Revenue] )

Results: Cost ₹2,20,800, Profit ₹54,300, Margin 19.7% (format the measure as a percentage).

Relationship functions (9 and 10)

9. RELATED

Fetches a value from the "one" side of a relationship while you are on the "many" side, in a row context. Syntax: RELATED(<column>). The Cost measure above uses it inside SUMX. As a calculated column in Sales:

Category = RELATED ( Products[Category] )

Row S07 returns Home; row S09 returns Electronics.

10. RELATEDTABLE

Goes the other way: from the "one" side, returns the related rows on the "many" side. Syntax: RELATEDTABLE(<table>). As a calculated column in Customers:

Order Count = COUNTROWS ( RELATEDTABLE ( Sales ) )

Results: C1 3, C2 2, C3 3, C4 2.

Filter functions (11 to 17)

11. CALCULATE

The most important DAX function. It evaluates an expression in a modified filter context. Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> ...]]).

Electronics Revenue = CALCULATE ( [Revenue], Products[Category] = "Electronics" )

Result: ₹2,65,700. One surprise: in a matrix with Category on rows, the Home row also shows ₹2,65,700, because this filter replaces any existing filter on Products[Category]. If you want it to respect the row instead (and show blank for Home), wrap the condition in KEEPFILTERS.

12. FILTER

Iterates a table and returns only rows that meet a condition. Use it inside CALCULATE when the condition involves an expression, not just one column. Syntax: FILTER(<table>, <condition>).

Large Order Revenue =
CALCULATE (
    [Revenue],
    FILTER ( Sales, Sales[Quantity] * Sales[UnitPrice] >= 10000 )
)

Result: ₹2,57,000 (orders S01, S04, S05 and S09). Avoid FILTER over a whole large fact table when a simple column filter will do; it is slower.

13. ALL

Removes filters from a table or columns. Inside CALCULATE it is the usual way to get a grand total for a "% of total" measure. Syntax: ALL([<table> | <column>[, <column>...]]).

Revenue % of Total =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Products ) ) )

In a table by Category: Electronics 96.6%, Home 3.4%.

14. REMOVEFILTERS

Does the same job as ALL when used as a CALCULATE filter, but the name says what it does. It cannot be used as a table expression on its own. Syntax: REMOVEFILTERS([<table> | <column>[, <column>...]]).

Revenue % of Categories =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], REMOVEFILTERS ( Products[Category] ) ) )

Same results as above: 96.6% and 3.4%.

15. ALLSELECTED

Removes filters coming from the visual's rows and columns but keeps filters from slicers and outer filters. Use it for "% of what the user has selected". Syntax: ALLSELECTED([<table> | <column>...]).

Revenue % of Selection =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALLSELECTED ( Products ) ) )

With a product slicer set to Headphones and Desk Lamp:

16. VALUES

Returns the distinct values of a column that are visible in the current filter context, as a one-column table. Syntax: VALUES(<column>). It is often combined with an iterator:

Active Months =
COUNTROWS ( FILTER ( VALUES ( 'Date'[YearMonth] ), [Revenue] > 0 ) )

Result: 6 (January to March in both years). Calling the [Revenue] measure inside FILTER triggers context transition for each month, just like the CALCULATE example earlier.

17. SELECTEDVALUE

Returns the value of a column when exactly one value is filtered, otherwise an alternate result. Great for dynamic titles. Syntax: SELECTEDVALUE(<column>[, <alternateResult>]).

Selected Category = SELECTEDVALUE ( Products[Category], "All categories" )
Chart Title = "Revenue: " & [Selected Category]

With the Home slicer on, the title reads Revenue: Home; with nothing selected, Revenue: All categories.

Logic and ranking functions (18 to 21)

18. IF

Returns one value if a condition is true and another if false. Syntax: IF(<condition>, <true>[, <false>]).

Revenue Flag = IF ( [Revenue] >= 100000, "High", "Normal" )

By city: Mumbai (₹1,53,000) is High; Delhi (₹61,400), Bengaluru (₹56,200) and Pune (₹4,500) are Normal.

19. SWITCH

Replaces nested IFs. The SWITCH(TRUE(), ...) pattern checks conditions in order and returns the first match. Syntax: SWITCH(<expression>, <value>, <result>[, ...][, <else>]). As a calculated column in Sales, using the LineTotal column from earlier:

Order Band =
SWITCH (
    TRUE (),
    Sales[LineTotal] >= 50000, "Large",
    Sales[LineTotal] >= 3000, "Medium",
    "Small"
)

Result: 4 Large, 4 Medium, 2 Small orders.

20. RANKX

Ranks each item by an expression. Syntax: RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]). Use ALL on the column you rank over, or every row ranks as 1.

Product Rank = RANKX ( ALL ( Products[Product] ), [Revenue] )

Results: Laptop 1 (₹2,57,000), Desk Lamp 2 (₹9,400), Headphones 3 (₹8,700).

21. TOPN

Returns the top N rows of a table by an expression. Syntax: TOPN(<n>, <table>, <orderBy_expression>[, <order>[, ...]]).

Top 2 Product Revenue =
CALCULATE ( [Revenue], TOPN ( 2, ALL ( Products[Product] ), [Revenue] ) )

Result: ₹2,66,400 (Laptop plus Desk Lamp). If two products tie at the cut-off, TOPN returns both, so you can get more than N rows.

Date and time-intelligence functions (22 to 25)

22. CALENDAR

Creates a table with one row per date between two dates. Syntax: CALENDAR(<start_date>, <end_date>). Build the Date table as a new calculated table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

This gives 730 rows. Mark it as a date table (Table tools > Mark as date table), sort Month by MonthNum, and relate 'Date'[Date] to Sales[OrderDate]. Classic time-intelligence functions expect a date column with no gaps, which is why you use a separate date table instead of the order dates.

23. TOTALYTD

Year-to-date total. Syntax: TOTALYTD(<expression>, <dates>[, <filter>][, <year_end_date>]).

Revenue YTD = TOTALYTD ( [Revenue], 'Date'[Date] )
Revenue FYTD = TOTALYTD ( [Revenue], 'Date'[Date], "3/31" )

By month in 2026: January ₹1,01,500, February ₹1,09,700, March ₹1,67,700. The second measure sets a 31 March year end, which matches the Indian financial year. The year-end string is read using the model's locale, so "3/31" suits an English (US) model; check the format for yours.

24. SAMEPERIODLASTYEAR

Shifts the current dates back one year. Syntax: SAMEPERIODLASTYEAR(<dates>).

Revenue PY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Revenue] - [Revenue PY], [Revenue PY] )

25. DATEADD

Shifts dates by a number of days, months, quarters or years. Syntax: DATEADD(<dates>, <number_of_intervals>, <interval>), where the interval is written without quotes (MONTH, not "MONTH").

Revenue PM = CALCULATE ( [Revenue], DATEADD ( 'Date'[Date], -1, MONTH ) )
MoM % = DIVIDE ( [Revenue] - [Revenue PM], [Revenue PM] )

For March 2026, Revenue PM is ₹8,200 (February), so MoM is 607.3%. For January 2026 it is blank, because there were no December 2025 sales, and DIVIDE returns blank rather than an error.

DAX functions cheat sheet

What mistakes do people make with DAX?

• Using calculated columns where a measure belongs. Columns are computed once at refresh and stored; measures respond to slicers. Totals and ratios should almost always be measures.
• Summing a ratio. A column of margins added up is meaningless. Compute DIVIDE([Profit], [Revenue]) at whatever level the visual shows.
• No proper date table. Time intelligence on raw order dates gives wrong or missing results when dates have gaps.
• Forgetting that CALCULATE filters replace existing ones, as in the Home row example in function 11.
• Long formulas without variables. Use VAR and RETURN to name steps. It makes measures easier to read and debug.

How should you practise DAX?

Rebuild this model, write all 25 measures and check that your numbers match the ones above. Then load a larger public sales dataset and build a one-page report with revenue, margin, YoY growth and a top-products table. Our Power BI projects for beginners has project ideas, and Power BI interview questions shows how DAX is tested. If you are still deciding between tools, read Power BI vs Tableau.

Microsoft's Power BI Data Analyst certification (exam PL-300) expects you to be proficient in Power Query and DAX, so this list is also a sensible starting point for that exam.

Frequently Asked Questions

What are the most important DAX functions to learn first?

Start with SUM, SUMX, COUNTROWS, DISTINCTCOUNT, DIVIDE and CALCULATE. Then add ALL or REMOVEFILTERS for percentage-of-total measures, and TOTALYTD and SAMEPERIODLASTYEAR once you have a proper date table.

What is the difference between SUM and SUMX in Power BI?

SUM adds up a single column. SUMX goes through a table row by row, evaluates an expression such as quantity times price, and then adds the results. Use SUMX whenever the value you need does not exist as one column.

What is the difference between a measure and a calculated column?

A calculated column is computed for every row when the data refreshes and is stored in the model. A measure is computed at query time in the current filter context, so it responds to slicers and visuals. Totals, ratios and KPIs should usually be measures.

Why does my DAX measure show the same value in every row?

Usually because a filter is being removed or replaced. A CALCULATE filter on the same column as the visual's rows replaces that row filter, and ALL removes it. Check which filters your measure keeps, and use KEEPFILTERS or ALLSELECTED if needed.

Do I need a date table for DAX time intelligence?

For the classic time-intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR and DATEADD, yes in practice. Use a separate table with one row per day and no gaps, mark it as a date table and relate it to your fact table.

Is DAX hard to learn for Excel users?

The syntax feels familiar because many functions share Excel names. The harder part is thinking in filter context instead of cells. Most people find it clicks after they build a few percentage-of-total and year-on-year measures themselves.

Sources and methodology

Function syntax and behaviour were checked against Microsoft's official DAX documentation in September 2026. The sample model is invented for teaching. Expected results were cross-checked by recomputing the same logic in SQL (SQLite) on the 10 sample rows; build the model in Power BI Desktop to confirm them yourself.

• Microsoft Learn, DAX function reference and DAX overview (row context, filter context), checked September 2026.
• Microsoft Learn, function pages for RANKX, REMOVEFILTERS, SELECTEDVALUE, TOTALYTD, SAMEPERIODLASTYEAR and DATEADD, checked September 2026.
• Microsoft Learn, Create date tables in Power BI Desktop, checked September 2026.
• Microsoft Learn, Microsoft Certified: Power BI Data Analyst Associate (exam PL-300, Power Query and DAX proficiency), checked September 2026.
• ISS: Data & Business Intelligence program page.

The choice of 25 functions and the practice advice are ISS editorial guidance.

Next steps

Build the sample model, write the measures, and compare your results with this guide. Then apply the same measures to a real dataset and publish a one-page report you can show in interviews.

If you want a live, mentor-led path through SQL, Python and dashboards, review the Data & Business Intelligence curriculum and check whether its tools match your goals. Applying is free: apply here. You can also download the free Data Analyst Starter Kit offered on this page.