Learn more about this course.

Using Advanced DAX Functions

Functions are predefined formulas that perform calculations by using specific values called arguments in a particular order or structure.

Arguments can be other functions, another formula, column references, numbers, text, logical values such as TRUE or FALSE, or constants.

DAX includes the following categories of functions: Date and Time, Information, Logical, Mathematical, Statistical, Text, and Time Intelligence functions. If you are familiar with functions in Excel formulas, many of the functions in DAX will appear similar to you.

However, DAX functions are unique in the following ways:

A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.
If you need to customise calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of argument to perform calculations that vary by context.
DAX includes many functions that return a table rather than a value. The table is not displayed but is used to provide input to other functions. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
DAX includes a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on them. For example, you can compare sums across parallel periods.

Context

Context is one of the most important DAX concepts to understand. There are two types of context in DAX called row context and filter context. Let’s first look at the row context.

Row Context

Row context is most easily thought of as the current row. For example, let’s assume we have a simple DAX formula that’s used to create new data (named Margin) for each row in a calculated column, defined as follows:

Margin:=[SalesAmount]-[TotalCost]. The formula =[SalesAmount] – [TotalCost] calculates a value in the Margin column for each row in the table. Values for each row are calculated from values in two other columns, [SalesAmount] and [TotalCost] in the same row. DAX can calculate the values for each row in the Margin column because it has the context: For each row, it takes values in the [TotalCost] column and subtracts them from values in the [SalesAmount] column.

Row context doesn’t just apply to calculated columns. Row context also applies whenever a formula has a function that applies filters to identify a single row in a table. The function will inherently apply a row context for each row of the table over which it is filtering. This type of row context most often applies to calculated fields.

Filter Context

Filter context is a little more difficult to understand than row context. You can most easily think of filter context as one or more filters applied in a calculation that determines a result or value.

Filter context does not exist in place of row context; rather, it applies in addition to the row context. For example, to further narrow down the values to include in a calculation, you can apply a filter context which not only specifies the row context but also specifies only a particular value (filter) in that row context.

Filter context is easily seen in pivot tables. For example, when you add TotalCost to the Values area and then add Year and Region to the row or columns, you are defining a filter context that selects a subset of data based on a given year and region.

Why is filter context so important to DAX? While filter context can most easily be applied by adding column and row labels and slicers in a pivot table, filter context can also be applied in a DAX formula by defining a filter using functions (such as ALL, RELATED, FILTER, CALCULATE), by relationships, and by other calculated fields and columns.

As you can imagine, being able to define filter context within a formula has powerful uses. The ability to reference only a particular value in a related table is just one such example. Don’t worry if you don’t completely understand context right away. As you create your own formulas, you’ll better understand context and why it’s so important in DAX.