DAX Functions Explained

What is a DAX function?

DAX (Data Analysis Expressions) is the formula language used in Power BI calculations, Analysis Services, and Power Pivot in Excel.

The function accepts values, columns, tables, or expressions and returns a scalar value or a table. You can use table results as filters for other expressions.

DAX formulas define calculations, while queries return data from the data model.

DAX expression output depends on the evaluation context, filters active on the visual, slicer selections, and relationships that pass those filters to model tables.

Power BI evaluates DAX measures when a query requests their value.

Example:

Total Sales = SUM ( Sales[Sales Amount] )

When the report groups this measure by product category, the model evaluates the sum once for each category’s filter context.

A grand total will trigger another evaluation in the total context.

CALCULATE and evaluation context:

DAX uses two types of evaluation context: filter context and row context. They can exist at the same time, and row context can be nested inside filter context.

• Filter context defines the data subset your formula sees, that comes from visual filters, slicers, cross-filtering, and filter arguments in functions like CALCULATE.
• Row context iterates a table row by row and gives an expression access to the values in the current row. Iterators and calculated columns create row context.

CALCULATE is the most important function in DAX. It runs a context transition and evaluates an expression in a modified filter context.

CALCULATE can remove, add, or replace filters.

Use REMOVEFILTERS to remove filters:

CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Color])) 

A normal filter argument will replace any existing filter on the same column, or adds a filter if none exists:

CALCULATE([Sales Amount], 'Product'[Type] = "Food") 

CALCULATE([Sales Amount], 'Date'[Year] = 2017)

Row context exists inside iterators and calculated columns, and filter context comes from the report canvas, from slicers, and from filter arguments in CALCULATE.

CALCULATE performs context transition and remains the central function of measure design.

Engines:

Power BI executes every DAX function on one of two engines inside the semantic model.

• The storage engine scans compressed columns in parallel, caches results, and answers simple aggregations at high speed.
• The formula engine coordinates queries and evaluates anything the storage engine cannot process, including iterators with row-level expressions.

A SUM over one column processing will finish inside the storage engine. And for SUMX with a conditional row expression, Power BI will call a formula engine time on every row.

Function categories:

1. Aggregation functions:

Aggregation functions return scalar results from columns or tables, for example, SUM, COUNTROWS, DISTINCTCOUNT, MIN, and MAX.

There are 2 types of aggregation functions:

• Set-based: SUM, AVERAGE, MAX.

Total Sales = SUM ( Sales[Sales Amount] )

• Iterator functions: SUMX, AVERAGEX, MAXX, MINX, COUNTX, and RANKX evaluate an expression row by row over a table, and then aggregate the results.

SUMX(
    Sales,
    Sales[Quantity] * Sales[Price]
)

Use an iterator only when the measure requires a row-level expression, for example, Revenue = [Quantity] x [Unit price].

2. Filter and value functions:

Filter and relationship functions query or alter model filters.

RELATED retrieves a value through a relationship in the row context.

TREATAS applies values from one table expression as filters on columns in another table.

LOOKUPVALUE searches for a result using one or more matching values, while CALCULATE with a relationship-based filter can be clearer where the model already defines that relationship.

Example:

CALCULATE(
    [Total Revenue],
    ALLEXCEPT(
        Products,
        Products[Category]
    )
)

ALL, ALLNOBLANKROW, ALLEXCEPT, and ALLSELECTED functions remove filters when used inside CALCULATE:

• ALL removes all filters from a table or columns.
• ALLEXCEPT removes filters from the expanded table except for listed columns. It can remove filters from related dimension tables too.
• ALLSELECTED removes row and column filters from the current query and retains other context filters and explicit filters.
• ALLNOBLANKROW works the same as ALL but excludes the blank row.

3. Date and time intelligence functions:

Time intelligence functions work with dates and periods: days, months, quarters, and years.

Before using these functions, mark a date table as a Date table in the model, make sure it has contiguous dates with no gaps, and disable auto date/time.

Here are the most common functions:

• SAMEPERIODLASTYEAR.
• DATEADD.
• DATESYTD, DATESMTD, DATESWTD.
• TOTALYTD, TOTALMTD.
• CLOSINGBALANCEYEAR.
• ENDOFYEAR, ENDOFWEEK.

Example:

YoY Revenue Growth % =
VAR CurrentRevenue = [Total Revenue]
VAR PriorYearRevenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date]))
RETURN
    DIVIDE(CurrentRevenue - PriorYearRevenue, PriorYearRevenue)

Time intelligence functions now accept either a traditional date column or a calendar reference.

A calendar is a tagged set of date and time columns in the semantic model. Calendar input returns its primary tagged columns and time-related columns and supports fiscal or nonstandard calendars.

4. Logical and conditional functions:

IF and SWITCH choose results depending on condition.

COALESCEfunction returns the first nonblank expression.

Example:

COALESCE(
    Customers[Phone],
    Customers[Email],
    "No Contact"
)

5. Text:

The same way as in SQL, you can manipulate and search in your text.

The most used text functions in DAX are:

• LEFT, RIGHT, MID.
• LEN.
• TRIM.
• UPPER, LOWER.
• SEARCH.
• CONCATENATE.
• FORMAT.
• VALUE.

Examples:

-- UPPER / LOWER
Customer Name Upper = UPPER(Customers[Name])
-- Result: "JOHN DOE" from "John Doe"

Customer Name Lower = LOWER(Customers[Name])
-- Result: "john doe"

-- TRIM - removes extra spaces
Clean Name = TRIM(Customers[Name])
-- Result: "John Doe" from "  John  Doe  "

-- LEN - length of text
Name Length = LEN(Customers[Name])

Full text search:

TEXTCONTAINS returns a Boolean match for a text column and a search string.

TEXTSIMILARITY returns a floating point relevance score for ranking.

Use TOPN with a descending TEXTSIMILARITY score to rank candidate text rows. Use TEXTCONTAINS when you need a Boolean filter.

6. Table manipulation functions:

Table functions shape temporary tables.

• VALUES and DISTINCT functions return distinct values.
• ADDCOLUMNS adds expression results.
• SUMMARIZECOLUMNS groups data, and TOPN selects the first rows according to a sort expression.

A table expression can feed another function. For example, FILTER can limit the rows passed to SUMX.

Keep filters on model columns where possible. Broad row by row table scans can raise query cost, especially when a measure repeats them for many visual cells.

Example:

TOPN(
    3,
    ADDCOLUMNS(
        VALUES(Products[Name]),
        "@Rev", [Total Revenue]
    ),
    [@Rev], DESC
)

Window functions:

OFFSET, INDEX, WINDOW, RANK, and ROWNUMBERwindow functions return tables of rows positioned by a sort specification.

ORDERBY, PARTITIONBY, and MATCHBY define the sort, the partitions, and the columns that identify matching rows.

Example:

Previous Month Sales =
CALCULATE (
    [Sales Amount],
    OFFSET (
        -1,
        ALLSELECTED ( 'Date'[Year Month] ),
        ORDERBY ( 'Date'[Year Month Number], ASC )
    )
)

Debugging and metadata functions:

The DAX query view in Desktop can run EVALUATE queries on the model, format expressions, and write new measures back.

The EVALUATEANDLOG function wraps a table expression inside a measure, returns the value unchanged, and logs every evaluation for display in the DAX query view and in external debuggers.

• Info functions:INFO.VIEW.TABLES(), INFO.VIEW.MEASURES(), INFO.VIEW.COLUMNS(), and INFO.VIEW.RELATIONSHIPS() return model metadata as tables inside DAX queries.
• Table references: TABLEOF returns a reference to a table associated with a column/measure/calendar.

Example:

EVALUATE
INFO.VIEW.MEASURES()

Visual calculation functions:

Visual calculations are DAX formulas that run on a visual. They use the data shown in the visual, so you can build running totals, moving averages, and period-over-period comparisons without managing filter context or model relationships.

They store on the visual and operate on aggregated data, which sometimes performs better than equivalent measures.

Visual calculations support most existing DAX functions plus extra functions built specifically for visual calculations: COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, ISATLEVEL, LAST, MOVINGAVERAGE, NEXT, PREVIOUS, RANGE, RUNNINGSUM, LOOKUP, and LOOKUPWITHTOTALS.

Visual calculations cannot be pinned to dashboards, exported with data, or reused in other visuals.

Example:

RUNNINGSUM(
    [Total Revenue],
    ORDERBY(
        Calendar[Date], ASC
    )
)

User-defined functions:

You can define reusable script as a user-defined function (UDF), then call it from measures, calculated columns, visual calculations, or other UDFs.

UDFs support scalar, table, and reference parameters.

They reduce duplication, but recursion is not supported, and they add complexity for simple cases.

The definition uses the FUNCTION keyword inside a DEFINE block, and a comment block above the function documents parameters and return values:

DEFINE
    /// Returns the amount with tax applied
    /// @param {NUMERIC} amount - Net amount before tax
    /// @returns Amount including 10 percent tax
    FUNCTION AddTax = ( amount : NUMERIC ) => amount * 1.1
EVALUATE
{ AddTax ( 10 ) }   // returns 11

Best practices:

• Avoid using bidirectional relationships.
• Store intermediate results in variables. Power BI evaluates variables only once.
• Avoid iterators when possible: SUMX processes row by row. Use set-based functions where filters can be pushed to the storage engine.
• Measure before optimizing: Use DAX Studio, Performance Analyzer, and DAX Optimizer to identify bottlenecks during performance profiling.
• Avoid wrapping a plain aggregation in CALCULATE when it adds no filter modification and there is no need for context transition.
• Use REMOVEFILTERS when you only need to clear filters. Reserve ALL for table results.

Follow me for more Power BI tips and tricks.

What should I explain next?

Let me know in the comments 👇