A Comprehensive Guide to Understanding DAX in Power BI

What is DAX?

Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models. DAX helps you assemble new information based on data already in the model.

Why is DAX important?

Learning how to create effective DAX formulas will help you make the most of your data. When you have all the information you need, you can start solving business problems that affect the profitability of your business.

The Fundamentals of DAX

Row and Filter Context

A Row context is a formula that includes a function that uses filters to identify a single row in a table. The function applies a row context to each row in the table to which the filter is applied.

A Filter context is one or more filters applied in a calculation, which determine a single value or result. You can use a filter context to reduce the values that are included in a calculation.

Functions

Aggregate Functions

These functions calculate a (scalar) value such as count, sum, average, minimum, or maximum for all rows in a column or table as defined by the expression.

Date and time functions

These functions help you create calculations based on dates and time. Many of the functions in DAX are similar to the Excel date and time functions. However, DAX functions use a datetime data type, and can take values from a column as an argument.

Filter functions

These functions help you return specific data types, look up values in related tables, and filter by related values. Lookup functions work by using tables and relationships between them. Filtering functions let you manipulate data context to create dynamic calculations.

Financial functions

Financial functions in DAX are used in formulas that perform financial calculations, such as net present value and rate of return. These functions are similar to financial functions used in Microsoft Excel.

Information functions

DAX information functions look at the cell or row that is provided as an argument and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value that you reference contains an error.

Logical functions

Logical functions act upon an expression to return information about the values or sets in the expression. For example, you can use the IF function to check the result of an expression and create conditional results.

Math & Trig functions

The mathematical functions in DAX are very similar to the Excel mathematical and trigonometric functions.

Parent and Child functions

These functions manage data that is presented as parent/child hierarchies.

Relationship functions

Functions in this category are for managing and utilizing relationships between tables.

Statistical functions

These functions calculate values related to statistical distributions and probability, such as standard deviation and number of permutations.

Table manipulation functions

These functions return a table or manipulate existing tables.

Text functions

DAX includes a set of text functions based on the library of string functions in Excel, but which have been modified to work with tables and columns in tabular models.

Time Intelligence functions

DAX includes time-intelligence functions that enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods. In order to perform calculations accurately in Power BI using time intelligence functions, you must mark your table as a date table.

CALCULATE and CALCULATETABLE

CALCULATE evaluates an expression in a modified filter context, whereas CALCULATETABLE evaluates a table expression in a modified filter context. CALCULATE performs exactly the same functionality, except it modifies the filter context applied to an expression that returns a scalar value.

For example, we might have:

CALCULATE(SUM('Sales Order[sales]), FILTER('Sales Order', 'Sales Order'[COUNTRY] = "Spain"))

The expression is the SUM - adding the total numbers in the sales column. We apply the filter where the country is Spain.

MAX and MIN

MAXIMUM

MAX - Returns the largest numeric value in a column, or between two scalar expressions. MAX(<column>) or MAX(<expression1>, <expression2>).

MAXA - Returns the largest value in a column. MAXA(<column>).

MAXX - Evaluates a expression for each row of a table and returns the largest numeric value. MAXX(<table>, <expression>).

MINIMUM

MIN - Returns the smallest numeric value in a column, or between two scalar expressions. MIN(<column>) or MIN(<expression1>, <expression2>).

MINA - Returns the smallest value in a column, including any logical values and numbers represented as text. MINA(<column>).

MINX - Returns the smallest numeric value that results from evaluating an expression for each row of a table. MINX(<table>, <expression>).

ALL vs ALLSELECTED vs ALLEXCEPT

ALL - Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Example:

CALCULATE(SUM(Sales[Amount]), ALL(Sales[Product]))

Returns the total sales amount without considering any filters on the Product column.

ALLSELECTED - Removes filters applied directly to a table or column, but respects filters applied through slicers or other visuals.

Example:

CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales[Product]))

Calculates the total sales amount considering the filters applied through slicers but ignoring other direct filters on Product.

ALLEXCEPT - Removes all filters from a table or column except for specified columns.

Example:

CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Region]))

Calculates the total sales amount while keeping filters on the Region column but ignoring filters on other columns.

ALL vs REMOVEFILTERS

REMOVEFILTERS clears filters from the specified table or columns.

ALL and REMOVEFILTERS share the exact same syntax.

ALL([<table> | <column>[, <column>[, <column>[,…]]]] ) vs REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]] ).

ALL returns the table or column with filters removed, whereas REMOVEFILTERS does not return anything.

CROSSFILTER

Used to control the direction and behaviour of relationships between tables when doing calculations. It changes how filters are applied between related tables, allowing you to specify the direction of filtering.

CROSSFILTER(<Table1_Column1>, <Table2_Column2>, <CrossFilterDirection>)

- CrossFilterDirection:

- None: No filtering is applied

- Single: Filters in only one direction (from the first table to the second table)

- Both: Filters are applied in both directions

KEEPFILTERS

Used to ensure that filters applied to a column or table are preserved when using functions like CALCULATE or CALCULATETABLE. It helps in maintaining the integrity of filter context by preventing certain filters from being removed or overridden.

KEEPFILTERS(<FilterExpression>)

LOOKUPVALUE

Used to retrieve a single value from a column in a table based on a specified condition. It's often used to perform lookups or to pull data from a related table into the current context.

LOOKUPVALUE( <Result_ColumnName>, <Search_ColumnName1>, <Search_Value1>, [<Search_ColumnName2>, <Search_Value2>, ...] )

- Result_ColumnName: The column from which the value is returned.

- Search_ColumnName1: The column where the search value is compared.

- Search_Value1: The value to search for in Search_ColumnName1.

- Search_ColumnName2, Search_Value2, ...: Optional additional pairs of search columns and values for more complex lookups.

TOPN

Returns the top N rows of the specified table.

TOPN( <N>, <Table>, <OrderBy_Column>, [<Order>], [<OrderBy_Column2>, <Order2>], ... )

- <N>: The number of top rows to return.

- <Table>: The table or table expression from which to return the rows.

- <OrderBy_Column>: The column used to sort the rows. The sort order is determined by the following argument.

- <Order>: Optional. Specifies the sort order (ASC for ascending or DESC for descending). If omitted, the default is descending.

- <OrderBy_Column2>, <Order2>, ...: Optional additional columns and sort orders for secondary sorting.

COUNT

COUNT - Counts the number of cells in a column that contain numbers. COUNT(<column>).

COUNTA - Counts the number of cells in a column that are not empty. COUNTA(<column>).

COUNTBLANK - Counts the number of blank cells in a column. COUNTBLANK(<column>).

COUNTROWS - Counts the number of rows in the specified table, or in a table defined by an expression. COUNTROWS(<table>).

COUNTX - Counts the number of rows that contain a number or an expression that evaluates to a number, when evaluating an expression over a table. COUNTX(<table>, <expression>).

COUNTAX - Counts nonblank results when evaluating the result of an expression over a table. COUNTAX(<table>, <expression>).

DISTINCTCOUNT - Counts the number of distinct values in a column. DISTINCTCOUNT(<column>).

DISTINCTCOUNTNOBLANK - Counts the number of distinct values in a column, skipping the blank values. DISTINCTCOUNTNOBLANK(<column>).

DISTINCT vs VALUES

Same as DISTINCT, VALUES retrieves the distinct values of a column. However, VALUES counts any blank values whereas DISTINCT only does if the entire column/table consists of them.

DISTINCT(<Column>) or DISTINCT(<Table>).

VALUES(<Column>) or VALUES(<Table>).

UTCNOW vs UTCTODAY

UTCNOW returns the current date and time in UTC. UTCTODAY returns the current date in UTC, with the time component set to midnight.

UTCNOW().

UTCTODAY().

OPENINGBALANCE & CLOSINGBALANCE

These are used to calculate the balance of a financial account at the beginning and end of a specified period.

OPENINGBALANCE - Calculates the balance of an account at the start of a specified period. OPENINGBALANCE(<Measure>, <DateColumn>).

CLOSINGBALANCE - Calculates the balance of an account at the end of a specified period. CLOSINGBALANCE(<Measure>, <DateColumn>).

NAMEOF

Retrieves the name of a column, table, or measure as a text string. NAMEOF(<Type>, <Name>).

- <Type>: The type of the object whose name you want to retrieve. It can be COLUMN, TABLE, or MEASURE.

- <Name>: The name of the object whose name is being retrieved. This should be in the form of a table or column reference.

SELECTEDVALUE

Used to retrieve the value of a column when there is a single value selected in the current filter context. SELECTEDVALUE(<Column>, [<AlternateResult>]).

- <Column>: The column from which you want to retrieve the selected value.

- <AlternateResult>: Optional. The value to return if there are multiple values or no value selected. If omitted, BLANK() is returned when the selection is ambiguous or empty.