Top 50 DAX Functions in Power BI: Syntax, Examples and Why You Need Them

A working guide to Data Analysis Expressions: what each function does, its syntax, a real example against one shared sample dataset, the answer it returns, and the mistakes people make with it.

Why you need DAX formulas at all

Power BI can drag a column into a chart and sum it without any formula. That covers “total sales by region”. It stops there. Real business questions compare, filter, shift time, rank and divide, and the raw data has no column for any of that. DAX is how you teach the model those rules once, so every chart, slicer and page answers them correctly.

The data never holds the answer directly. Your sales table has quantity, price and date. Profit, margin, growth and “% of target” don’t exist until a formula creates them.
One definition, used everywhere. Write [Profit Margin %] once and it is correct in every visual, filter and drill-down. Without it, each analyst calculates margin their own way in Excel and the numbers disagree in meetings.
Numbers that react to the user. A measure recalculates for whatever region, month or product the viewer clicks. A pre-calculated Excel column cannot do that.
Time comparisons. “This year vs last year”, “year to date”, “last 90 days” are the questions managers ask most, and they need time-intelligence functions plus a date table.
Smaller, faster models. Calculating at query time instead of storing extra columns keeps reports quick on millions of rows.

Business question	Why a plain column is not enough	DAX you need
What is our profit and margin?	Cost lives in Products, quantity in Sales; margin is a ratio that must be recomputed per filter	SUMX, RELATED, DIVIDE
What share of sales does each category bring?	Needs the grand total next to each row, ignoring the row’s own filter	CALCULATE, ALL / ALLSELECTED
Are we growing vs last year?	Last year’s value must be fetched for whatever period is on screen	SAMEPERIODLASTYEAR, DATEADD, TOTALYTD
Did each region hit its target?	Targets sit in a separate table with no relationship	LOOKUPVALUE, SELECTEDVALUE
Which are our top products?	Rank changes with every filter the user applies	RANKX, TOPN
Which orders are still pending?	The status is implied by a blank ship date, not stored	ISBLANK, IF, COUNTROWS
Show a title that names the selected region	Text must change with the slicer	SELECTEDVALUE, FORMAT, CONCATENATEX

Each function entry below opens with what it does, then shows the exact problem it solves on the sample dataset. If you only learn ten, learn SUM, DIVIDE, CALCULATE, FILTER, ALL, RELATED, SUMX, SELECTEDVALUE, TOTALYTD and SAMEPERIODLASTYEAR. They cover most of the reports you will ever build.

Five ideas that make every DAX function make sense

DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services and Power Pivot. It looks like Excel, but it works on whole tables and columns instead of cells. Most confusion with DAX comes from a handful of concepts, not from the functions themselves. Read these once and the rest of the guide becomes much easier.

1. Measures vs calculated columns

A calculated column is computed once per row when the data refreshes and is stored in the model, like Sales[Order Size]. A measure is computed at query time, for whatever cell of a visual is being drawn, like [Total Sales]. Use columns for things you want to slice or filter by; use measures for numbers you want to aggregate. When in doubt, write a measure: it uses no storage and responds to every filter.

2. Row context

Inside a calculated column, or inside an iterator such as SUMX or FILTER, DAX evaluates the expression one row at a time. That “current row” is the row context. It is why Sales[Quantity] * Sales[UnitPrice] works in a column but fails in a plain measure: a measure has no current row.

3. Filter context

Every cell of a visual is calculated under a set of filters: the row and column headers of a matrix, slicers, page and report filters. That set is the filter context. [Total Sales] in the “South / 2025” cell returns only South 2025 sales because the filter context says so. CALCULATE is the one function that lets you change it.

4. Context transition

When CALCULATE runs inside a row context (or when you reference a measure inside an iterator, which wraps it in an invisible CALCULATE), the current row becomes a filter. This is how AVERAGEX(VALUES('Calendar'[YearMonth]), [Total Sales]) gets the sales of each month.

5. Variables

VAR … RETURN is not a function, but you should use it in almost every non-trivial measure. A variable is computed once, makes the formula readable, and is easy to debug: return the variable on its own to check its value.

Profit Margin % =
VAR Revenue = [Total Sales]
VAR Cost    = [Total Cost]
RETURN
    DIVIDE ( Revenue - Cost, Revenue )

The sample dataset

Every example below runs against the same small model: an Indian office-products retailer with 1,500 orders across 2024 and 2025. It ships as four CSV files (Sales.csv, Products.csv, Customers.csv, Targets.csv) plus a Calendar table you build in DAX in function 38. Amounts are in rupees and the “Result” lines use Indian digit grouping (₹1,00,000 = one lakh).

Table	Rows	Columns	Role
`Sales`	1,500	OrderID, OrderDate, ShipDate, CustomerID, ProductID, Quantity, UnitPrice, Discount, SalesAmount	Fact table. 51 orders have a blank ShipDate (not shipped yet).
`Products`	15	ProductID, ProductName, Category, SubCategory, UnitCost, ListPrice	Dimension: 3 categories (Electronics, Furniture, Office Supplies).
`Customers`	80	CustomerID, CustomerName, Segment, City, Region, JoinDate	Dimension: 4 regions (North, South, East, West).
`Targets`	8	Year, Region, SalesTarget	Deliberately not related to anything. Used with LOOKUPVALUE.
`Calendar`	731	Date, Year, MonthNo, Month, YearMonth, Quarter, Month End	Date table built with CALENDAR, marked as a date table.

Model setup in Power BI Desktop

Get data → Text/CSV and load the four files. Check that OrderDate, ShipDate and JoinDate are typed as Date and Discount as a decimal number.
Create the Calendar table (function 38), then Table tools → Mark as date table using Calendar[Date].
In Model view, create these relationships (all many-to-one, single direction): Sales[ProductID] → Products[ProductID], Sales[CustomerID] → Customers[CustomerID], Sales[OrderDate] → Calendar[Date] (active) and Sales[ShipDate] → Calendar[Date] (inactive, dashed line; used by USERELATIONSHIP).
Create an empty table called _Measures (Enter data → load an empty table) to keep your measures in one place.

Headline figures you can use to check your model: Total Sales ₹3,07,72,553.05, 2024 sales ₹1,34,62,405.35, 2025 sales ₹1,73,10,147.70, total units 7,344.

Aggregation and iterators

The building blocks of every report. Simple aggregators (SUM, AVERAGE…) work on one column. Their “X” versions are iterators: they walk a table row by row, evaluate an expression, then aggregate the results.

01. SUM (Aggregation)

Adds up all the numbers in a single column, respecting the current filter context. This is the first measure in almost every model, and most other measures are built on top of it.

Syntax: SUM ( <column> )

Total Sales = SUM ( Sales[SalesAmount] )
Total Units = SUM ( Sales[Quantity] )

Result on sample data: Total Sales = ₹3,07,72,553.05; Total Units = 7,344. Put Total Sales in a matrix with Products[Category] on rows and it splits automatically: Electronics ₹2,22,18,183.90, Furniture ₹64,81,150.15, Office Supplies ₹20,73,219.00.

Watch out: SUM accepts only a column, not an expression. SUM(Sales[Quantity] * Sales[UnitPrice]) is an error; use SUMX for that.

02. AVERAGE (Aggregation)

Returns the arithmetic mean of a numeric column. Blank values are ignored, but zeros are counted, which matters when a missing value has been loaded as 0.

Syntax: AVERAGE ( <column> )

Avg Order Value = AVERAGE ( Sales[SalesAmount] )

Result on sample data: ₹20,515.04 per order (₹3,07,72,553.05 ÷ 1,500 rows).

Watch out: AVERAGE is a per-row average. If one order can span several rows, compute DIVIDE([Total Sales], DISTINCTCOUNT(Sales[OrderID])) instead.

03. MIN (Aggregation)

Returns the smallest value in a column. Works on numbers and dates, and with two scalar arguments it returns the smaller of the two.

Syntax: MIN ( <column> ) | MIN ( <expr1>, <expr2> )

Smallest Order   = MIN ( Sales[SalesAmount] )
First Order Date = MIN ( Sales[OrderDate] )
Capped Discount  = MIN ( [Discount Given], 1000000 )   -- two-argument form

Result on sample data: Smallest Order = ₹159.20 (one Gel Pen Pack with 20% off); First Order Date = 2 Jan 2024.

04. MAX (Aggregation)

The mirror of MIN: the largest value in a column, or the larger of two expressions. MAX('Calendar'[Date]) is the standard way to get “the last date in the current filter”, which most time-intelligence patterns rely on.

Syntax: MAX ( <column> ) | MAX ( <expr1>, <expr2> )

Largest Order   = MAX ( Sales[SalesAmount] )
Last Order Date = MAX ( Sales[OrderDate] )

Result on sample data: Largest Order = ₹2,31,200.00 (an order of Laptop Pro 14s).

05. COUNT (Aggregation)

Counts the non-blank values in a column of numbers, dates or text. Because it skips blanks, it is a quick way to count “rows where something has happened”.

Syntax: COUNT ( <column> )

Shipped Orders = COUNT ( Sales[ShipDate] )

Result on sample data: 1,449 (1,500 orders minus the 51 with a blank ShipDate).

Related: COUNTA also counts TRUE/FALSE columns; COUNTBLANK counts the blanks (here, 51).

06. COUNTROWS (Aggregation)

Counts the rows of a table, or of any table expression. It is the preferred way to count records because it does not depend on any column being non-blank, and it combines naturally with FILTER and VALUES.

Syntax: COUNTROWS ( [<table>] )

Orders = COUNTROWS ( Sales )

Pending Orders =
COUNTROWS ( FILTER ( Sales, ISBLANK ( Sales[ShipDate] ) ) )

Result on sample data: Orders = 1,500; Pending Orders = 51.

07. DISTINCTCOUNT (Aggregation)

Counts the number of unique values in a column. Use it for “how many different customers, products or days” questions.

Syntax: DISTINCTCOUNT ( <column> )

Active Customers = DISTINCTCOUNT ( Sales[CustomerID] )
Products Sold    = DISTINCTCOUNT ( Sales[ProductID] )

Result on sample data: Active Customers = 80 (every customer bought at least once); Products Sold = 15.

Watch out: DISTINCTCOUNT counts a blank as a value. Use DISTINCTCOUNTNOBLANK if blanks should be excluded.

08. SUMX (Iterator)

Evaluates an expression for every row of a table, then adds up the results. Use it whenever the number you want to total does not exist as a column, such as quantity × price, or cost from a related table.

Syntax: SUMX ( <table>, <expression> )

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

Discount Given = [Gross Sales] - [Total Sales]

Result on sample data: Gross Sales = ₹3,31,08,796.00; Discount Given = ₹23,36,242.95.

Why not a calculated column? A column Quantity * UnitPrice plus SUM gives the same total but stores 1,500 extra values. With millions of rows, SUMX in a measure is usually the leaner choice.

09. AVERAGEX (Iterator)

Evaluates an expression for each row of a table and averages the results. Iterating over a list of months, customers or days, it answers “average per X” questions.

Syntax: AVERAGEX ( <table>, <expression> )

Avg Monthly Sales =
AVERAGEX ( VALUES ( 'Calendar'[YearMonth] ), [Total Sales] )

Result on sample data: At grand total: ₹12,82,189.71 (₹3,07,72,553.05 over 24 months). Filter to 2025 and it becomes the 2025 monthly average.

How it works: because [Total Sales] is a measure, each month in VALUES triggers context transition, so the measure is evaluated for that month only.

10. MAXX (Iterator)

Evaluates an expression over a table and returns the largest result. MINX is its counterpart. Typical uses: best month, biggest customer, latest date per group.

Syntax: MAXX ( <table>, <expression> )

Best Month Sales =
MAXX ( VALUES ( 'Calendar'[YearMonth] ), [Total Sales] )

Biggest Customer Spend =
MAXX ( VALUES ( Customers[CustomerID] ), [Total Sales] )

Tip: to return the name of the best month rather than its value, combine TOPN(1, …) with CONCATENATEX or use a variable and FILTER.

Filter and context functions

These functions read or rewrite the filter context. CALCULATE is the engine; the others either produce filters for it (FILTER, ALL, KEEPFILTERS…) or tell you what filters are active (SELECTEDVALUE, ISFILTERED…).

11. CALCULATE (Filter)

The most important function in DAX. It evaluates an expression in a modified filter context: each filter argument either adds a filter or replaces the existing filter on the same column. It also performs context transition when called from a row context.

Syntax: CALCULATE ( <expression> [, <filter1> [, <filter2> …]] )

Electronics Sales =
CALCULATE ( [Total Sales], Products[Category] = "Electronics" )

South 2025 Sales =
CALCULATE (
    [Total Sales],
    Customers[Region] = "South",
    'Calendar'[Year] = 2025
)

Result on sample data: Electronics Sales = ₹2,22,18,183.90; South 2025 Sales = ₹59,39,775.25.

Key behaviour: in a matrix with Category on rows, Electronics Sales shows ₹2.22 crore on every row, including Furniture, because the filter argument overwrites the row’s Category filter. See KEEPFILTERS (17) to change that.

12. FILTER (Filter / table)

Returns a table containing only the rows for which a condition is true. Use it inside CALCULATE when the condition involves a measure, an expression across several columns, or anything a simple Column = value filter cannot express.

Syntax: FILTER ( <table>, <condition> )

Sales from Big Orders =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[Quantity] * Sales[UnitPrice] > 50000 )
)

High-Value Customers =
COUNTROWS (
    FILTER ( VALUES ( Customers[CustomerID] ), [Total Sales] > 500000 )
)

Performance: filter columns, not whole tables. FILTER(ALL(Products[Category]), …) scans 3 rows; FILTER(Sales, …) scans every sales row. For simple equality, write Products[Category] = "Furniture" directly in CALCULATE.

13. ALL (Filter)

Returns all rows of a table, or all values of columns, ignoring any filters on them. Inside CALCULATE it removes filters, which is how you build “percent of total” measures.

Syntax: ALL ( <table> | <column> [, <column> …] )

Sales All Products =
CALCULATE ( [Total Sales], ALL ( Products ) )

% of Grand Total =
DIVIDE ( [Total Sales], [Sales All Products] )

Result on sample data: By category: Electronics 72.20%, Furniture 21.06%, Office Supplies 6.74%.

14. ALLEXCEPT (Filter)

Removes every filter on a table except the ones on the columns you list. Useful for “share within my group” calculations, for example a city’s share of its own region.

Syntax: ALLEXCEPT ( <table>, <column> [, <column> …] )

Region Total =
CALCULATE ( [Total Sales], ALLEXCEPT ( Customers, Customers[Region] ) )

City % of Region = DIVIDE ( [Total Sales], [Region Total] )

Use case: put Region and City on rows of a matrix. Each city shows its share of its own region, and the four region subtotals each show 100%.

15. ALLSELECTED (Filter)

Removes filters coming from inside the visual (rows, columns) but keeps filters coming from outside it (slicers, page filters). The result is “percent of what the user can currently see”.

Syntax: ALLSELECTED ( [<table> | <column> [, …]] )

% of Visible Total =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED ( Products[Category] ) )
)

Compare with ALL: untick Office Supplies in a slicer. With ALL, Electronics stays at 72.20%. With ALLSELECTED it rises to about 77.4%, because the total now covers only the two visible categories.

16. REMOVEFILTERS (Filter modifier)

Clears filters from tables or columns inside CALCULATE. It behaves exactly like ALL used as a filter argument, but its name states the intent, so formulas are easier to read. It cannot be used as a table expression.

Syntax: REMOVEFILTERS ( [<table> | <column> [, …]] )

All Regions Sales =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Customers[Region] ) )

Region Share = DIVIDE ( [Total Sales], [All Regions Sales] )

Result on sample data: For 2025: South 34.31% (₹59.40 lakh of ₹1.73 crore).

17. KEEPFILTERS (Filter modifier)

Makes a CALCULATE filter intersect with the existing filter instead of replacing it. Rows outside the new filter become blank rather than showing the overridden value.

Syntax: KEEPFILTERS ( <filter expression> )

Electronics Only =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Products[Category] = "Electronics" )
)

Result on sample data: In a matrix by Category: Electronics row = ₹2,22,18,183.90, Furniture and Office Supplies rows = blank. Without KEEPFILTERS all three rows show ₹2,22,18,183.90.

18. VALUES (Table)

Returns a one-column table of the distinct values of a column that are visible in the current filter context (or the visible rows of a table). It is the usual input for iterators and for checking what the user has selected. DISTINCT is similar but never returns the extra blank row that VALUES adds for unmatched keys.

Syntax: VALUES ( <column> | <table> )

Regions in View = COUNTROWS ( VALUES ( Customers[Region] ) )

Avg Sales per Customer =
AVERAGEX ( VALUES ( Customers[CustomerID] ), [Total Sales] )

Result on sample data: Regions in View = 4 with no slicer; Avg Sales per Customer = ₹3,84,656.91 at grand total.

19. SELECTEDVALUE (Information)

Returns the value of a column when exactly one value is visible, otherwise an alternate result (blank by default). It is the cleanest way to read a slicer selection and to build dynamic titles.

Syntax: SELECTEDVALUE ( <column> [, <alternateResult>] )

Selected Region = SELECTEDVALUE ( Customers[Region], "All regions" )

Report Title =
"Sales overview – " & [Selected Region]

Result on sample data: “Sales overview – South” with South selected; “Sales overview – All regions” with nothing or several selected. Use it as a card or a visual’s title via fx.

20. HASONEVALUE (Information)

Returns TRUE when the filter context leaves exactly one distinct value in a column. Typically used to show a number only at a certain level of a matrix, or to hide meaningless totals.

Syntax: HASONEVALUE ( <column> )

Sales (Region level only) =
IF ( HASONEVALUE ( Customers[Region] ), [Total Sales] )

Use case: targets are set per region, so a “Target %” grand total that adds all regions together can be misleading. Wrapping it in HASONEVALUE blanks the total row.

21. ISFILTERED (Information)

Returns TRUE when a column is being filtered directly (by a slicer, a visual’s rows, or a filter argument). ISCROSSFILTERED also returns TRUE when the filter arrives through a relationship.

Syntax: ISFILTERED ( <column> | <table> )

Filter Note =
IF (
    ISFILTERED ( Products[Category] ),
    "Category: " & CONCATENATEX ( VALUES ( Products[Category] ), Products[Category], ", " ),
    "All categories"
)

Relationship functions

Move values between related tables, switch to an inactive relationship, or look a value up when there is no relationship at all.

22. RELATED (Relationship)

Fetches a value from the “one” side of a relationship into the current row of the “many” side. Works in calculated columns and inside iterators, anywhere there is a row context on the many side.

Syntax: RELATED ( <column> )

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

-- Measures
Total Cost =
SUMX ( Sales, Sales[Quantity] * RELATED ( Products[UnitCost] ) )

Profit = [Total Sales] - [Total Cost]

Result on sample data: Total Cost = ₹2,30,24,520.00; Profit = ₹77,48,033.05.

Watch out: RELATED only goes from many to one. Going the other way (one product → its many sales) needs RELATEDTABLE.

23. RELATEDTABLE (Relationship)

From a row on the “one” side, returns the table of matching rows on the “many” side. It is CALCULATETABLE with context transition, so it is mostly used in calculated columns on dimension tables.

Syntax: RELATEDTABLE ( <table> )

-- Calculated columns on Customers
Order Count    = COUNTROWS ( RELATEDTABLE ( Sales ) )
Lifetime Value = SUMX ( RELATEDTABLE ( Sales ), Sales[SalesAmount] )

Result on sample data: On average a customer has 18.75 orders (1,500 ÷ 80). Use Lifetime Value to band customers into tiers you can slice by.

24. USERELATIONSHIP (Relationship)

Activates an inactive relationship for the duration of one CALCULATE. It is how one Calendar table can serve both order date and ship date without duplicating it.

Syntax: USERELATIONSHIP ( <column1>, <column2> )

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

Result to expect: plotted by month, this line lags Total Sales by a few days, and its grand total is lower because the 51 unshipped orders have no ship date to join on. The relationship must already exist in the model (dashed line).

25. LOOKUPVALUE (Lookup)

Returns a value from a table by matching one or more search columns, like a multi-key VLOOKUP. It needs no relationship, which makes it handy for small mapping tables such as targets. If more than one different value matches, it returns an error.

Syntax: LOOKUPVALUE ( <result_column>, <search_column>, <search_value> [, …] [, <alternate>] )

Sales Target =
LOOKUPVALUE (
    Targets[SalesTarget],
    Targets[Region], SELECTEDVALUE ( Customers[Region] ),
    Targets[Year],   SELECTEDVALUE ( 'Calendar'[Year] )
)

Target Achieved % = DIVIDE ( [Total Sales], [Sales Target] )

Result on sample data: South 2025: target ₹33,35,000, actual ₹59,39,775.25, achieved 178.1%.

When to avoid: for large tables a proper relationship plus RELATED is faster and easier to maintain.

Logical and information functions

Branching, flags and blank handling. These turn raw numbers into labels people can act on.

26. IF (Logical)

Checks a condition and returns one value when it is TRUE and another when it is FALSE. If the false branch is omitted, the result is blank.

Syntax: IF ( <condition>, <value_if_true> [, <value_if_false>] )

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

-- Calculated column on Sales
Discounted? = IF ( Sales[Discount] > 0, "Yes", "No" )

Tip: both branches must return compatible types. Mixing a number and text forces the column to text, which breaks sorting and conditional formatting.

27. SWITCH (Logical)

Compares an expression against a list of values and returns the matching result. With TRUE() as the expression it becomes a readable replacement for nested IFs, testing conditions top to bottom.

Syntax: SWITCH ( <expression>, <value1>, <result1> [, …] [, <else>] )

-- Calculated column on Sales
Order Size =
SWITCH (
    TRUE (),
    Sales[SalesAmount] >= 50000, "Large",
    Sales[SalesAmount] >= 10000, "Medium",
    "Small"
)

-- Calculated column on Calendar
Season =
SWITCH ( 'Calendar'[MonthNo], 10, "Festive", 11, "Festive", 12, "Year end", "Regular" )

28. AND (Logical)

Returns TRUE only when both arguments are TRUE. AND takes exactly two arguments; for three or more conditions use the && operator, which is what most DAX authors write anyway.

Syntax: AND ( <logical1>, <logical2> ) ≡ <logical1> && <logical2>

-- Calculated column on Sales
Bulk Electronics =
AND ( RELATED ( Products[Category] ) = "Electronics", Sales[Quantity] >= 3 )

Bulk Electronics Discounted =
RELATED ( Products[Category] ) = "Electronics"
    && Sales[Quantity] >= 3
    && Sales[Discount] > 0

29. OR (Logical)

Returns TRUE when at least one of two arguments is TRUE. As with AND, use the || operator for more than two conditions, or the IN operator for a list of values.

Syntax: OR ( <logical1>, <logical2> ) ≡ <logical1> || <logical2>

-- Calculated column on Sales
Festive Order = OR ( MONTH ( Sales[OrderDate] ) = 10, MONTH ( Sales[OrderDate] ) = 11 )

-- Same thing with IN
Festive Order 2 = MONTH ( Sales[OrderDate] ) IN { 10, 11 }

30. COALESCE (Logical)

Returns the first argument that is not blank. The standard use is replacing blank results with zero so visuals show “0” instead of hiding the row, or falling back from one value to another.

Syntax: COALESCE ( <expr1>, <expr2> [, …] )

Sales (show zero) = COALESCE ( [Total Sales], 0 )

Effective Date =
COALESCE ( MAX ( Sales[ShipDate] ), MAX ( Sales[OrderDate] ) )

Watch out: returning 0 instead of blank makes every product/region combination appear in a table, even ones with no sales. Use it where that is what you want.

31. ISBLANK (Information)

Returns TRUE when a value is blank. Blank in DAX is a distinct state, similar to NULL in SQL; comparing with = BLANK() also matches 0 and empty text, while ISBLANK matches only true blanks.

Syntax: ISBLANK ( <value> )

-- Calculated column on Sales
Ship Status = IF ( ISBLANK ( Sales[ShipDate] ), "Pending", "Shipped" )

Result on sample data: Pending 51, Shipped 1,449.

Math functions

Two math functions show up in nearly every report: safe division and rounding.

32. DIVIDE (Math)

Divides two numbers and returns blank (or a value you choose) instead of an error when the denominator is zero or blank. Use it for every ratio instead of the / operator.

Syntax: DIVIDE ( <numerator>, <denominator> [, <alternateResult>] )

Profit Margin % = DIVIDE ( [Profit], [Total Sales] )

Units per Order = DIVIDE ( [Total Units], [Orders], 0 )

Result on sample data: Profit Margin = 25.18%; Units per Order = 4.90.

33. ROUND (Math)

Rounds a number to a given number of digits. A negative digit count rounds to the left of the decimal point. ROUNDUP and ROUNDDOWN always round away from or towards zero.

Syntax: ROUND ( <number>, <num_digits> )

Sales (Lakh)  = ROUND ( [Total Sales] / 100000, 2 )
Sales (Crore) = ROUND ( [Total Sales] / 10000000, 2 )
Nearest 1000  = ROUND ( [Total Sales], -3 )

Result on sample data: Sales (Lakh) = 307.73; Sales (Crore) = 3.08; Nearest 1000 = 3,07,73,000.

Tip: for display only, prefer the column’s format string. Round in DAX when the rounded value feeds another calculation.

Text functions

Labels, codes and dynamic text for cards and titles.

34. FORMAT (Text)

Converts a number or date to text using a format string. Essential for dynamic labels and for month-name columns. The output is text, so it no longer sorts or sums like a number.

Syntax: FORMAT ( <value>, <format_string> [, <locale>] )

Sales Label = "Revenue: ₹" & FORMAT ( [Total Sales], "#,##0" )

-- Calculated columns on Calendar
Month     = FORMAT ( 'Calendar'[Date], "MMM" )
YearMonth = FORMAT ( 'Calendar'[Date], "YYYY-MM" )

Margin Label = FORMAT ( [Profit Margin %], "0.0%" )

Result on sample data: “Revenue: ₹30,772,553”; Month = “Jan”; YearMonth = “2025-06”; Margin Label = “25.2%”. Pass "en-IN" as the locale for lakh/crore grouping.

Watch out: sort the text Month column by MonthNo (Column tools → Sort by column), otherwise months sort alphabetically: Apr, Aug, Dec…

35. CONCATENATEX (Text iterator)

Iterates over a table, evaluates an expression for each row, and joins the results into one string with a delimiter, optionally sorted. Ideal for “selected items” text.

Syntax: CONCATENATEX ( <table>, <expression> [, <delimiter> [, <orderBy>, <order>]] )

Selected Categories =
CONCATENATEX (
    VALUES ( Products[Category] ),
    Products[Category],
    ", ",
    Products[Category], ASC
)

Result on sample data: “Electronics, Furniture, Office Supplies” with no filter. The two-text version, CONCATENATE, joins only two strings; the & operator does the same.

36. LEFT (Text)

Returns the first N characters of a string. RIGHT takes them from the end and MID from the middle. Together they parse codes and IDs.

Syntax: LEFT ( <text> [, <num_chars>] )

-- Calculated columns
Order Prefix     = LEFT ( Sales[OrderID], 2 )                -- "SO"
Order Number     = VALUE ( RIGHT ( Sales[OrderID], 5 ) )     -- 53
Customer Initial = LEFT ( Customers[CustomerName], 1 )

37. SEARCH (Text)

Returns the position of one string inside another, ignoring case. The fourth argument is what to return when nothing is found; set it to 0 so a missing match does not raise an error. FIND is the case-sensitive version.

Syntax: SEARCH ( <find_text>, <within_text> [, <start_num> [, <NotFoundValue>]] )

-- Calculated column on Products
Is Laptop = SEARCH ( "laptop", Products[ProductName], 1, 0 ) > 0

-- Measure
Laptop Sales = CALCULATE ( [Total Sales], Products[Is Laptop] = TRUE () )

Result on sample data: Is Laptop is TRUE for Laptop Pro 14 and Laptop Air 13; Laptop Sales = ₹1,24,22,900.00.

Shortcut: CONTAINSSTRING(Products[ProductName], "laptop") returns TRUE/FALSE directly.

Date and time functions

Build the date table and do row-level date arithmetic. Time intelligence (next section) depends on getting this part right.

38. CALENDAR (Date / table)

Returns a single-column table of continuous dates between a start and an end date. Wrapped in ADDCOLUMNS, it is the usual way to create a date table in DAX. CALENDARAUTO does the same but scans the model for the date range.

Syntax: CALENDAR ( <start_date>, <end_date> )

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year",      YEAR ( [Date] ),
    "MonthNo",   MONTH ( [Date] ),
    "Month",     FORMAT ( [Date], "MMM" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" ),
    "Quarter",   "Q" & QUARTER ( [Date] ),
    "Month End", EOMONTH ( [Date], 0 )
)

Result on sample data: A 731-row table (2024 is a leap year). Create it with Modeling → New table, then mark it as a date table.

Rule: time-intelligence functions need a date table with one row for every day, no gaps, covering whole years.

39. YEAR (Date)

Returns the year of a date as a four-digit integer. Its siblings MONTH, DAY, QUARTER, WEEKDAY and WEEKNUM extract the other parts. Use them to build calendar columns or group in calculated columns.

Syntax: YEAR ( <date> )

-- Calculated columns on Sales
Order Year  = YEAR ( Sales[OrderDate] )
Order Month = MONTH ( Sales[OrderDate] )

-- Measure: sales in the current calendar year of the data
Sales 2025 = CALCULATE ( [Total Sales], 'Calendar'[Year] = 2025 )

Result on sample data: Sales 2025 = ₹1,73,10,147.70.

40. DATEDIFF (Date)

Returns the number of interval boundaries crossed between two dates, in DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE or SECOND. It counts boundaries, so 31 Dec → 1 Jan is one YEAR.

Syntax: DATEDIFF ( <start_date>, <end_date>, <interval> )

-- Calculated column on Sales
Days to Ship =
IF (
    NOT ISBLANK ( Sales[ShipDate] ),
    DATEDIFF ( Sales[OrderDate], Sales[ShipDate], DAY )
)

-- Calculated column on Customers
Tenure (Years) = DATEDIFF ( Customers[JoinDate], DATE ( 2025, 12, 31 ), YEAR )

-- Measure
Avg Days to Ship = AVERAGE ( Sales[Days to Ship] )

Tip: use TODAY() instead of a fixed date for a live “age” column; it refreshes with the dataset.

41. EOMONTH (Date)

Returns the last day of the month, a given number of months before or after a date. Handy for month-end reporting, due dates and joining to monthly snapshot tables. EDATE shifts by months without moving to month-end.

Syntax: EOMONTH ( <start_date>, <months> )

-- Calculated columns
Month End          = EOMONTH ( 'Calendar'[Date], 0 )    -- 2025-02-28
Next Month End     = EOMONTH ( Sales[OrderDate], 1 )
Payment Due (Net 30 EOM) = EOMONTH ( Sales[OrderDate], 0 ) + 30

Time intelligence

Functions that shift or accumulate the date filter: year-to-date, last year, previous month, rolling windows. They all take the 'Calendar'[Date] column and are used inside CALCULATE.

42. TOTALYTD (Time intelligence)

Evaluates an expression from the start of the year up to the last date in the current filter. An optional year-end date supports fiscal years, such as India’s April–March year. TOTALQTD and TOTALMTD work the same way. It is shorthand for CALCULATE(expr, DATESYTD(dates)).

Syntax: TOTALYTD ( <expression>, <dates> [, <filter>] [, <year_end_date>] )

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

Sales FYTD (Apr–Mar) =
TOTALYTD ( [Total Sales], 'Calendar'[Date], "3/31" )

Result on sample data: Sales YTD at June 2025 = ₹79,68,430.65 (1 Jan – 30 Jun 2025).

43. SAMEPERIODLASTYEAR (Time intelligence)

Returns the dates in the current filter shifted back one year. Used in CALCULATE, it gives last year’s value for whatever period is on screen: a day, a month, a quarter or a year.

Syntax: SAMEPERIODLASTYEAR ( <dates> )

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

YoY Growth % =
VAR Curr = [Total Sales]
VAR Prev = [Sales LY]
RETURN DIVIDE ( Curr - Prev, Prev )

Result on sample data: For 2025: Sales LY = ₹1,34,62,405.35; YoY Growth = +28.58%. For 2024 it is blank, since there is no 2023 data.

44. DATEADD (Time intelligence)

Shifts the dates in the current filter by a number of days, months, quarters or years, forward or back. It is the general form of SAMEPERIODLASTYEAR and is used for month-over-month and quarter-over-quarter comparisons.

Syntax: DATEADD ( <dates>, <number_of_intervals>, <interval> )

Sales PM =
CALCULATE ( [Total Sales], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

MoM Change % = DIVIDE ( [Total Sales] - [Sales PM], [Sales PM] )

Sales Same Qtr LY =
CALCULATE ( [Total Sales], DATEADD ( 'Calendar'[Date], -4, QUARTER ) )

45. DATESBETWEEN (Time intelligence)

Returns the dates between a start and end date, inclusive. Combined with MAX('Calendar'[Date]) it builds rolling windows: last 30, 90 or 365 days. DATESINPERIOD does the same with a length instead of an end date.

Syntax: DATESBETWEEN ( <dates>, <start_date>, <end_date> )

Sales Rolling 90 Days =
VAR LastDay = MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        DATESBETWEEN ( 'Calendar'[Date], LastDay - 89, LastDay )
    )

Festive Season 2025 =
CALCULATE (
    [Total Sales],
    DATESBETWEEN ( 'Calendar'[Date], DATE ( 2025, 10, 1 ), DATE ( 2025, 11, 30 ) )
)

46. PREVIOUSMONTH (Time intelligence)

Returns all dates of the month before the first date in the current filter. Unlike DATEADD, it always returns a complete month, even when the current selection is a single day. PREVIOUSQUARTER, PREVIOUSYEAR and the NEXT… variants follow the same pattern.

Syntax: PREVIOUSMONTH ( <dates> )

Sales Previous Month =
CALCULATE ( [Total Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) )

DATEADD vs PREVIOUSMONTH: on a visual by day, DATEADD(-1, MONTH) on 15 March returns 15 February’s sales; PREVIOUSMONTH returns all of February.

Table and ranking functions

Build summary tables, add computed columns to them, pick the top N, and rank items.

47. SUMMARIZECOLUMNS (Table)

Groups by one or more columns and adds named measure results, removing rows where every measure is blank. It is what Power BI visuals generate under the hood, and the best way to build summary tables in DAX queries or calculated tables. SUMMARIZE is the older grouping function; use it only for grouping, not for adding calculated columns.

Syntax: SUMMARIZECOLUMNS ( <groupBy_column> [, …] [, <filterTable>] [, <name>, <expression>] … )

Category Summary =
SUMMARIZECOLUMNS (
    Products[Category],
    "Sales",    [Total Sales],
    "Profit",   [Profit],
    "Margin %", [Profit Margin %]
)

Category	Sales	Share
Electronics	₹2,22,18,183.90	72.20%
Furniture	₹64,81,150.15	21.06%
Office Supplies	₹20,73,219.00	6.74%

Try it: open DAX query view in Power BI Desktop and run EVALUATE SUMMARIZECOLUMNS(...) to see the table without creating anything in the model.

48. ADDCOLUMNS (Table)

Returns a table with new calculated columns appended. Combined with VALUES or CALENDAR, it builds date tables and per-item summary tables, and inside measures it prepares virtual tables for iterators.

Syntax: ADDCOLUMNS ( <table>, <name>, <expression> [, <name>, <expression>] … )

Product Stats =
ADDCOLUMNS (
    VALUES ( Products[ProductName] ),
    "Sales",  [Total Sales],
    "Units",  [Total Units],
    "Margin", [Profit Margin %]
)

-- Inside a measure: months where sales beat ₹15 lakh
Strong Months =
COUNTROWS (
    FILTER (
        ADDCOLUMNS ( VALUES ( 'Calendar'[YearMonth] ), "@Sales", [Total Sales] ),
        [@Sales] > 1500000
    )
)

Convention: prefix virtual column names with @ so they are not mistaken for model columns.

49. TOPN (Table)

Returns the top N rows of a table ordered by an expression. Used as a CALCULATE filter, it answers “what do my top 3 products contribute?”. Ties can return more than N rows.

Syntax: TOPN ( <n>, <table>, <orderBy_expression> [, <order>] [, …] )

Top 3 Products Sales =
CALCULATE (
    [Total Sales],
    TOPN ( 3, ALL ( Products[ProductName] ), [Total Sales], DESC )
)

Top 3 Share = DIVIDE ( [Top 3 Products Sales], [Sales All Products] )

Result on sample data: Laptop Air 13 (₹68,06,100) + Laptop Pro 14 (₹56,16,800) + Noise-Cancel Headphones (₹47,91,020.85) = ₹1,72,13,920.85, a 55.94% share.

50. RANKX (Ranking)

Ranks the current item among all items of a table by an expression. Wrapping the table in ALL ensures every item is compared against the full list, not just itself. The last argument chooses SKIP (1, 1, 3) or DENSE (1, 1, 2) ranking for ties.

Syntax: RANKX ( <table>, <expression> [, <value> [, <order> [, <ties>]]] )

Product Rank =
IF (
    HASONEVALUE ( Products[ProductName] ),
    RANKX ( ALL ( Products[ProductName] ), [Total Sales], , DESC, DENSE )
)

Result on sample data: Laptop Air 13 = 1, Laptop Pro 14 = 2, Noise-Cancel Headphones = 3, Bookshelf Oak = 4, 27in Monitor = 5.

Most common bug: RANKX(Products, …) without ALL ranks every product as 1, because each row of the visual only sees itself.

Best practices that save hours

Build measures on measures. Define [Total Sales] once and reference it everywhere. When the definition changes, every dependent measure updates.
Always use DIVIDE for ratios and a proper date table for anything involving time.
Prefer measures to calculated columns unless you need to slice, filter or sort by the result.
Use VAR … RETURN for any formula longer than one line; it is faster and easier to debug.
Filter columns, not tables, inside CALCULATE and FILTER.
Reference columns with their table name (Sales[Quantity]) and measures without it ([Total Sales]), so readers can tell them apart.
Format your code with one argument per line for longer formulas. DAX Formatter (daxformatter.com) does this automatically.
Check your numbers against a known total, like the headline figures in the dataset section above, before building visuals.

Practice exercises on the sample data

Which region missed its 2025 target? (Hint: LOOKUPVALUE + DIVIDE. Answer: North reached about 108%, so no region missed.)
Rank customers by lifetime value within each region using RANKX and ALLEXCEPT.
Build a “Sales FYTD vs last FYTD” measure for an April–March year.
Show the month with the highest sales as text on a card, using TOPN and CONCATENATEX.