Power BI Common DAX Functions Explained

Power BI includes hundreds of DAX functions. Most reports rely on a core set of about twenty to thirty functions that cover the vast majority of business calculations. This topic explains the most important and commonly used DAX functions with practical examples so you can start using them confidently.

Aggregation Functions

Aggregation functions combine multiple values into a single result.

SUM

Adds all numeric values in a column. Total Revenue = SUM(Sales[Revenue]) returns the total of every revenue value across all sales transactions currently in context.

SUMX

The X at the end means "iterate over a table." SUMX goes through each row of a table, evaluates an expression for that row, and sums all the results. Use this when the value you want to sum does not exist as a column — you need to calculate it first.

Example: Your Sales table has Quantity and Unit Price but no Revenue column. Calculate total revenue without creating a calculated column:

Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])

SUMX multiplies Quantity by Unit Price for each row, then sums all those row-level results.

COUNT and COUNTA and COUNTROWS

COUNT counts rows with numeric values in a column. COUNTA counts rows with any non-blank value (works with text too). COUNTROWS counts the total number of rows in a table regardless of column content.

Order Count = COUNTROWS(Sales) tells you how many sales transactions exist in the current context.

DISTINCTCOUNT

Counts the number of unique values in a column. If 500 sales transactions involve 80 unique customers, DISTINCTCOUNT returns 80, not 500.

Unique Customers = DISTINCTCOUNT(Sales[Customer ID])

Logical Functions

IF

Tests a condition and returns one value if the condition is true, another if false.

Sales Status = IF(Sales[Revenue] > 10000, "High Value", "Standard")

Each sale above 10,000 gets labeled "High Value" and the rest get "Standard." IF is used in both calculated columns and measures.

AND and OR

Combine multiple conditions. AND requires all conditions to be true. OR requires at least one condition to be true.

Premium Customer = IF(AND(Customers[Orders] > 5, Customers[Total Spend] > 50000), "Premium", "Regular")

SWITCH

A cleaner alternative to nested IF statements when you need to check one value against many possibilities.

Quarter Name = SWITCH(Dates[Month Number], 1, "Q1", 2, "Q1", 3, "Q1", 4, "Q2", 5, "Q2", 6, "Q2", 7, "Q3", 8, "Q3", 9, "Q3", "Q4")

SWITCH is much easier to read than a chain of nested IF formulas when handling many cases.

IFERROR

Catches errors and replaces them with a value you specify. Useful when a formula might fail for some rows.

Safe Margin = IFERROR(DIVIDE([Profit], [Revenue]), 0)

Filter Functions

CALCULATE

CALCULATE is the most powerful and important function in DAX. It evaluates an expression in a modified filter context — it lets you change or override the filters that are currently active in the report.

Example: Your report shows sales by product. You want one measure that always shows total sales for the entire company regardless of which product is selected:

All Products Sales = CALCULATE(SUM(Sales[Revenue]), ALL(Products))

The ALL(Products) argument removes any filter from the Products table. The result is total company sales regardless of product selection — useful for calculating percentage of total.

Another example — show sales for only the "Electronics" category, regardless of what is selected in the report:

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

ALL

Removes all filters from a table or column, returning all rows. Often used inside CALCULATE to ignore report selections for a specific dimension.

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

This divides each product's revenue by the total company revenue — producing each product's percentage share.

FILTER

Returns a filtered version of a table based on a condition. Often used inside CALCULATE or SUMX when you need to work with a subset of rows.

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

This sums only the sales transactions where the individual sale amount exceeds 10,000.

Text Functions

CONCATENATE and Ampersand

Combine text values. Full Name = Customers[First Name] & " " & Customers[Last Name] creates a combined name column.

LEFT, RIGHT, MID

Extract characters from text. LEFT extracts from the beginning, RIGHT from the end, MID from any position.

Region Code = LEFT(Stores[Store Code], 3) extracts the first three characters from a store code to get a region prefix.

LEN

Returns the number of characters in a text value. Code Length = LEN(Products[SKU]) tells you how long each product code is.

Date and Time Functions

TODAY and NOW

TODAY returns the current date. NOW returns the current date and time. Useful for calculating how many days have passed since an event.

Days Since Order = TODAY() - Sales[Order Date]

YEAR, MONTH, DAY

Extract specific parts from a date. Order Year = YEAR(Sales[Order Date]) returns the four-digit year for each order date.

DATEDIFF

Calculates the difference between two dates in a specified unit — days, months, quarters, or years.

Delivery Days = DATEDIFF(Sales[Order Date], Sales[Delivery Date], DAY)

Key Points

• SUM and SUMX both add values, but SUMX iterates row by row and can evaluate expressions that do not exist as columns.
• CALCULATE is the most versatile DAX function — it evaluates any expression within a modified filter context.
• ALL removes filters and is commonly used inside CALCULATE to calculate totals or percentages of total.
• IF and SWITCH handle conditional logic — use SWITCH when testing one value against many possible outcomes.
• Date functions like YEAR, MONTH, DATEDIFF, and TODAY power time-based calculations in reports.