The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel

Understanding the direction of a relationship

Each relationship can have a unidirectional or bidirectional cross filter. Filtering always happens from the one-side of the relationship to the many-side. If the cross filter is bidirectional—that is, if it has two arrows on it—the filtering also happens from the many-side to the one-side.

An example might help in understanding this behavior. If a report is based on the data model shown in Figure 1-1, with the years on the rows and Quantity and Count of Product Name in the values area, it produces the result shown in Figure 1-2.

Calendar Year is a column that belongs to the Date table. Because Date is on the one-side of the relationship with Sales, the engine filters Sales based on the year. This is why the quantity shown is filtered by year.

With Products, the scenario is slightly different. The filtering happens because the relationship between the Sales and Product tables is bidirectional. When we put the count of product names in the report, we obtain the number of products sold in each year because the filter on the year propagates to Product through Sales. If the relationship between Sales and Product were unidirectional, the result would be different, as we explain in the following sections.

If we modify the report by putting Color on the rows and adding Count of Date in the values area, the result is different, as shown in Figure 1-3.

The filter on the rows is the Color column in the Product table. Because Product is on the one-side of the relationship with Sales, Quantity is filtered correctly. Count of Product Name is filtered because it is computing values from the table that is on the rows, that is Product. The unexpected number is Count of Date. Indeed, it always shows the same value for all the rows—that is, the total number of rows in the Date table.

The filter coming from the Color column does not propagate to Date because the relationship between Date and Sales is unidirectional. Thus, although Sales has an active filter on it, the filter cannot propagate to Date because the type of relationship prevents it.

If we change the relationship between Date and Sales to enable bidirectional cross-filtering, the result is as shown in Figure 1-4.

The numbers now reflect the number of days when at least one product of the given color was sold. At first sight, it might look as if all the relationships should be defined as bidirectional, so as to let the filter propagate in any direction and always return results that make sense. As you will learn later in this book, designing a data model this way is almost never appropriate. In fact, depending on the scenario you are working with, you will choose the correct propagation of relationships. If you follow our suggestions, you will avoid bidirectional filtering as much as you can.

Cells versus tables

Excel performs calculations over cells. A cell is referenced using its coordinates. Thus, we can write formulas as follows:

= (A1 * 1.25) - B2

In DAX, the concept of a cell and its coordinates does not exist. DAX works on tables and columns, not cells. As a consequence, DAX expressions refer to tables and columns, and this means writing code differently. The concepts of tables and columns are not new in Excel. In fact, if we define an Excel range as a table by using the Format as Table function, we can write formulas in Excel that reference tables and columns. In Figure 1-5, the SalesAmount column evaluates an expression that references columns in the same table instead of cells in the workbook.

Using Excel, we refer to columns in a table using the [@ColumnName] format. ColumnName is the name of the column to use, and the @ symbol means “take the value for the current row.” Although the syntax is not intuitive, normally we do not write these expressions. They appear when we click a cell, and Excel takes care of inserting the right code for us.

You might think of Excel as having two different ways of performing calculations. We can use standard cell references, in which case the formula for F4 would be E4*D4, or we can use column references inside a table. Using column references offers the advantage that we can use the same expression in all the cells of a column and Excel will compute the formula with a different value for each row.

Unlike Excel, DAX works only on tables. All the formulas must reference columns inside tables. For example, in DAX we write the previous multiplication this way:

Click here to view code image

Sales[SalesAmount] = Sales[ProductPrice] * Sales[ProductQuantity]

As you can see, each column is prefixed with the name of its table. In Excel, we do not provide the table name because Excel formulas work inside a single table. However, DAX works on a data model containing many tables. As a consequence, we must specify the table name because two columns in different tables might have the same name.

Many functions in DAX work the same way as the equivalent Excel function. For example, the IF function reads the same way in DAX and in Excel:

Click here to view code image

Excel IF ( [@SalesAmount] > 10, 1, 0)
DAX IF ( Sales[SalesAmount] > 10, 1, 0)

One important aspect where the syntax of Excel and DAX is different is in the way to reference the entire column. In fact, in [@ProductQuantity], the @ means “the value in the current row.” In DAX, there is no need to specify that a value must be from the current row, because this is the default behavior of the language. In Excel, we can reference the entire column—that is, all the rows in that column—by removing the @ symbol. You can see this in Figure 1-6.

The value of the AllSales column is the same in all the rows because it is the grand total of the SalesAmount column. In other words, there is a syntactical difference between the value of a column in the current row and the value of the column as a whole.

DAX is different. In DAX, this is how you write the AllSales expression of Figure 1-6:

Click here to view code image

AllSales := SUM ( Sales[SalesAmount] )

There is no syntactical difference between retrieving the value of a column for a specific row and using the column as a whole. DAX understands that we want to sum all the values of the column because we use the column name inside an aggregator (in this case the SUM function), which requires a column name to be passed as a parameter. Thus, although Excel requires an explicit syntax to differentiate between the two types of data to retrieve, DAX does the disambiguation automatically. This distinction might be confusing—at least in the beginning.

Excel and DAX: Two functional languages

One aspect where the two languages are similar is that both Excel and DAX are functional languages. A functional language is made up of expressions that are basically function calls. In Excel and DAX, the concepts of statements, loops, and jumps do not exist although they are common to many programming languages. In DAX, everything is an expression. This aspect of the language is often a challenge for programmers coming from different languages, but it should be no surprise at all for Excel users.

Iterators in DAX

A concept that might be new to you is the concept of iterators. When working in Excel, you perform calculations one step at a time. The previous example showed that to compute the total of sales, we create one column containing the price multiplied by the quantity. Then as a second step, we sum it to compute the total sales. This number is then useful as a denominator to compute the percentage of sales of each product, for example.

Using DAX, you can perform the same operation in a single step by using iterators. An iterator does exactly what its name suggests: it iterates over a table and performs a calculation on each row of the table, aggregating the result to produce the single value requested.

Using the previous example, we can now compute the sum of all sales using the SUMX iterator:

Click here to view code image

AllSales :=
SUMX (
    Sales,
    Sales[ProductQuantity] * Sales[ProductPrice]
)

This approach brings to light both an advantage and a disadvantage. The advantage is that we can perform many complex calculations in a single step without worrying about adding columns that would end up being useful only for specific formulas. The disadvantage is that programming with DAX is less visual than programming with Excel. Indeed, you do not see the column computing the price multiplied by the quantity; it exists only for the lifetime of the calculation.

As we will explain later, we can create a calculated column that computes the multiplication of price by quantity. Nevertheless, doing so is seldom a good practice because it uses memory and might slow down the calculations, unless you use DirectQuery and Aggregations, as we explain in Chapter 18, “Optimizing VertiPaq.”

DAX requires theory

Let us be clear: The fact that DAX requires one to study theory first is not a difference between programming languages. This is a difference in mindset. You are probably used to searching the web for complex formulas and solution patterns for the scenarios you are trying to solve. When you are using Excel, chances are you will find a formula that almost does what you need. You can copy the formula, customize it to fit your needs, and then use it without worrying too much about how it works.

This approach, which works in Excel, does not work with DAX, however. You need to study DAX theory and thoroughly understand how evaluation contexts work before you can write good DAX code. If you do not have a proper theoretical foundation, you will find that DAX either computes values like magic or it computes strange numbers that make no sense. The problem is not DAX but the fact that you do not yet understood exactly how DAX works.

Luckily, the theory behind DAX is limited to a couple of important concepts, which we explain in Chapter 4, “Understanding evaluation contexts.” When you reach that chapter, be prepared for some intense learning. After you master that content, DAX will have no secrets for you, and learning DAX will mainly be a matter of gaining experience. Remember: knowing is half the battle. So do not try to go further until you are somewhat proficient with evaluation contexts.

Relationship handling

The first difference between SQL and DAX is in the way relationships work in the model. In SQL, we can set foreign keys between tables to declare relationships, but the engine never uses these foreign keys in queries unless we are explicit about them. For example, if we have a Customers table and a Sales table, where CustomerKey is a primary key in Customers and a foreign key in Sales, we can write the following query:

Click here to view code image

SELECT
   Customers.CustomerName,
   SUM ( Sales.SalesAmount ) AS SumOfSales
FROM
   Sales
   INNER JOIN Customers
    ON Sales.CustomerKey = Customers.CustomerKey
GROUP BY
   Customers.CustomerName

Though we declare the relationship in the model using foreign keys, we still need to be explicit and state the join condition in the query. Although this approach makes queries more verbose, it is useful because you can use different join conditions in different queries, giving you a lot of freedom in the way you express queries.

In DAX, relationships are part of the model, and they are all LEFT OUTER JOINs. When they are defined in the model, you no longer need to specify the join type in the query: DAX uses an automatic LEFT OUTER JOIN in the query whenever you use columns related to the primary table. Thus, in DAX you would write the previous SQL query as follows:

Click here to view code image

EVALUATE
SUMMARIZECOLUMNS (
    Customers[CustomerName],
    "SumOfSales", SUM ( Sales[SalesAmount] )
)

Because DAX knows the existing relationship between Sales and Customers, it does the join automatically following the model. Finally, the SUMMARIZECOLUMNS function needs to perform a group by Customers[CustomerName], but we do not have a keyword for that: SUMMARIZECOLUMNS automatically groups data by selected columns.

DAX is a functional language

SQL is a declarative language. You define what you need by declaring the set of data you want to retrieve using SELECT statements, without worrying about how the engine actually retrieves the information.

DAX, on the other hand, is a functional language. In DAX, every expression is a function call. Function parameters can, in turn, be other function calls. The evaluation of parameters might lead to complex query plans that DAX executes to compute the result.

For example, if we want to retrieve only customers who live in Europe, we can write this query in SQL:

Click here to view code image

SELECT
   Customers.CustomerName,
   SUM ( Sales.SalesAmount ) AS SumOfSales
FROM
   Sales
   INNER JOIN Customers
    ON Sales.CustomerKey = Customers.CustomerKey
WHERE
   Customers.Continent = 'Europe'
GROUP BY
   Customers.CustomerName

Using DAX, we do not declare the WHERE condition in the query. Instead, we use a specific function (FILTER) to filter the result:

Click here to view code image

EVALUATE
SUMMARIZECOLUMNS (
    Customers[CustomerName],
    FILTER (
        Customers,
        Customers[Continent] = "Europe"
    ),
    "SumOfSales", SUM ( Sales[SalesAmount] )
)

You can see that FILTER is a function: it returns only the customers living in Europe, producing the expected result. The order in which we nest the functions and the kinds of functions we use have a strong impact on both the result and the performance of the engine. This happens in SQL too, although in SQL we trust the query optimizer to find the optimal query plan. In DAX, although the query optimizer does a great job, you, as programmer, bear more responsibility in writing good code.

DAX as a programming and querying language

In SQL, a clear distinction exists between the query language and the programming language—that is, the set of instructions used to create stored procedures, views, and other pieces of code in the database. Each SQL dialect has its own statements to let programmers enrich the data model with code. However, DAX virtually makes no distinction between querying and programming. A rich set of functions manipulates tables and can, in turn, return tables. The FILTER function in the previous query is a good example of this.

In that respect, it appears that DAX is simpler than SQL. When you learn it as a programming language—its original use—you will know everything needed to also use it as a query language.

Subqueries and conditions in DAX and SQL

One of the most powerful features of SQL as a query language is the option of using subqueries. DAX features similar concepts. In the case of DAX subqueries, however, they stem from the functional nature of the language.

For example, to retrieve customers and total sales specifically for the customers who bought more than US$100 worth, we can write this query in SQL:

Click here to view code image

SELECT
   CustomerName,
   SumOfSales
FROM (
   SELECT
    Customers.CustomerName,
    SUM ( Sales.SalesAmount ) AS SumOfSales
   FROM
    Sales
    INNER JOIN Customers
     ON Sales.CustomerKey = Customers.CustomerKey
   GROUP BY
    Customers.CustomerName
   ) AS SubQuery
WHERE
   SubQuery.SumOfSales > 100

We can obtain the same result in DAX by nesting function calls:

Click here to view code image

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        Customers[CustomerName],
        "SumOfSales", SUM ( Sales[SalesAmount] )
    ),
    [SumOfSales] > 100
)

In this code, the subquery that retrieves CustomerName and SumOfSales is later fed into a FILTER function that retains only the rows where SumOfSales is greater than 100. Right now, this code might seem unreadable to you. However, as soon as you start learning DAX, you will discover that using subqueries is much easier than in SQL, and it flows naturally because DAX is a functional language.

Multidimensional versus Tabular

MDX works in the multidimensional space defined by a model. The shape of the multidimensional space is based on the architecture of dimensions and hierarchies defined in the model, and this, in turn, defines the set of coordinates of the multidimensional space. Intersections of sets of members in different dimensions define points in the multidimensional space. You may have taken some time to realize that the [All] member of any attribute hierarchy is indeed a point in the multidimensional space.

DAX works in a much simpler way. There are no dimensions, no members, and no points in the multidimensional space. In other words, there is no multidimensional space at all. There are hierarchies, which we can define in the model, but they are different from hierarchies in MDX. The DAX space is built on top of tables, columns, and relationships. Each table in a Tabular model is neither a measure group nor a dimension: it is just a table, and to compute values, you scan it, filter it, or sum values inside it. Everything is based on the two simple concepts of tables and relationships.

You will soon discover that from the modeling point of view, Tabular offers fewer options than Multidimensional does. In this case, having fewer options does not mean being less powerful because you can use DAX as a programming language to enrich the model. The real modeling power of Tabular is the tremendous speed of DAX. In fact, you probably try to avoid overusing MDX in your model because optimizing MDX speed is often a challenge. DAX, on the other hand, is amazingly fast. Thus, most of the complexity of the calculations is not in the model but in the DAX formulas instead.

DAX as a programming and querying language

DAX and MDX are both programming languages and query languages. In MDX, the difference is made clear by the presence of the MDX script. You use MDX in the MDX script, along with several special statements that can be used in the script only, such as SCOPE statements. You use MDX in queries when you write SELECT statements that retrieve data. In DAX, this is somewhat different. You use DAX as a programming language to define calculated columns, calculated tables, and measures. The concept of calculated columns and calculated tables is new to DAX and does not exist in MDX; measures are similar to calculated members in MDX. You can also use DAX as a query language—for example, to retrieve data from a Tabular model using Reporting Services. Nevertheless, DAX functions do not have a specific role and can be used in both queries and calculation expressions. Moreover, you can also query a Tabular model using MDX. Thus, the querying part of MDX works with Tabular models, whereas DAX is the only option when it comes to programming a Tabular model.

Hierarchies

Using MDX, you rely on hierarchies to perform most of the calculations. If you wanted to compute the sales in the previous year, you would have to retrieve the PrevMember of the CurrentMember on the Year hierarchy and use it to override the MDX filter. For example, you can write the formula this way to define a previous year calculation in MDX:

Click here to view code image

CREATE MEMBER CURRENTCUBE.[Measures].[SamePeriodPreviousYearSales] AS
(
    [Measures].[Sales Amount],
    ParallelPeriod (
        [Date].[Calendar].[Calendar Year],
        1,
        [Date].[Calendar].CurrentMember
    )
);

The measure uses the ParallelPeriod function, which returns the cousin of the CurrentMember on the Calendar hierarchy. Thus, it is based on the hierarchies defined in the model. We would write the same calculation in DAX using filter contexts and standard time-intelligence functions:

Click here to view code image

SamePeriodPreviousYearSales :=
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

We can write the same calculation in many other ways using FILTER and other DAX functions, but the idea remains the same: instead of using hierarchies, we filter tables. This difference is huge, and you will probably miss hierarchy calculations until you get used to DAX.

Another important difference is that in MDX you refer to [Measures].[Sales Amount], and the aggregation function that you need to use is already defined in the model. In DAX, there is no predefined aggregation. In fact, as you might have noticed, the expression to compute is SUM(Sales[Sales Amount]). The predefined aggregation is no longer in the model. We need to define it whenever we want to use it. We can always create a measure that computes the sum of sales, but this would be beyond the scope of this section and is explained later in the book.

One more important difference between DAX and MDX is that MDX makes heavy use of the SCOPE statement to implement business logic (again, using hierarchies), whereas DAX needs a completely different approach. Indeed, hierarchy handling is missing in the language altogether.

For example, if we want to clear a measure at the Year level, in MDX we would write this statement:

Click here to view code image

SCOPE ( [Measures].[SamePeriodPreviousYearSales], [Date].[Month].[All] )
   THIS = NULL;
END SCOPE;

DAX does not have something like a SCOPE statement. To obtain the same result, we need to check for the presence of filters in the filter context, and the scenario is much more complex:

Click here to view code image

SamePeriodPreviousYearSales :=
IF (
    ISINSCOPE ( 'Date'[Month] ),
    CALCULATE (
        SUM ( Sales[Sales Amount] ),
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    ),
    BLANK ()
)

Intuitively, this formula returns a value only if the user is browsing the calendar hierarchy at the month level or below. Otherwise, it returns a BLANK. You will later learn what this formula computes in detail. It is much more error-prone than the equivalent MDX code. To be honest, hierarchy handling is one of the features that is really missing in DAX.

Leaf-level calculations

Finally, when using MDX, you probably got used to avoiding leaf-level calculations. Performing leaf-level computation in MDX turns out to be so slow that you should always prefer to precompute values and leverage aggregations to return results. In DAX, leaf-level calculations work incredibly fast and aggregations serve a different purpose, being useful only for large datasets. This requires a shift in your mind when it is time to build the data models. In most cases, a data model that fits perfectly in SSAS Multidimensional is not the right fit for Tabular and vice versa.

DAX data types

DAX can perform computations with different numeric types, of which there are seven. Over time, Microsoft introduced different names for the same data types, creating some sort of confusion. Table 2-1 provides the different names under which you might find each DAX data type.

Table 2-1 Data Types

In this book, we use the names in the first column of Table 2-1 adhering to the de facto standards in the database and Business Intelligence community. For example, in Power BI, a column containing either TRUE or FALSE would be called TRUE/FALSE, whereas in SQL Server, it would be called a BIT. Nevertheless, the historical and most common name for this type of value is Boolean.

DAX comes with a powerful type-handling system so that we do not have to worry about data types. In a DAX expression, the resulting type is based on the type of the term used in the expression. You need to be aware of this in case the type returned from a DAX expression is not the expected type; you would then have to investigate the data type of the terms used in the expression itself.

For example, if one of the terms of a sum is a date, the result also is a date; likewise, if the same operator is used with integers, the result is an integer. This behavior is known as operator overloading, and an example is shown in Figure 2-1, where the OrderDatePlusOneWeek column is calculated by adding 7 to the value of the Order Date column.

Click here to view code image

Sales[OrderDatePlusOneWeek] = Sales[Order Date] + 7

The result is a date.

In addition to operator overloading, DAX automatically converts strings into numbers and numbers into strings whenever required by the operator. For example, if we use the & operator, which concatenates strings, DAX converts its arguments into strings. The following formula returns “54” as a string:

= 5 & 4

On the other hand, this formula returns an integer result with the value of 9:

= "5" + "4"

The resulting value depends on the operator and not on the source columns, which are converted following the requirements of the operator. Although this behavior looks convenient, later in this chapter you see what kinds of errors might happen during these automatic conversions. Moreover, not all the operators follow this behavior. For example, comparison operators cannot compare strings with numbers. Consequently, you can add one number with a string, but you cannot compare a number with a string. You can find a complete reference here: https://docs.microsoft.com/en-us/power-bi/desktop-data-types. Because the rules are so complex, we suggest you avoid automatic conversions altogether. If a conversion needs to happen, we recommend that you control it and make the conversion explicit. To be more explicit, the previous example should be written like this:

Click here to view code image

= VALUE ( "5" ) + VALUE ( "4" )

People accustomed to working with Excel or other languages might be familiar with DAX data types. Some details about data types depend on the engine, and they might be different for Power BI, Power Pivot, or Analysis Services. You can find more detailed information about Analysis Services DAX data types at http://msdn.microsoft.com/en-us/library/gg492146.aspx, and Power BI information is available at https://docs.microsoft.com/en-us/power-bi/desktop-data-types. However, it is useful to share a few considerations about each of these data types.

= TODAY () + 1

= Sales[Unit Price] > Sales[Unit Cost]

IF ( [measure] > 0, 1, "N/A" )

DAX operators

Now that you have seen the importance of operators in determining the type of an expression, see Table 2-2, which provides a list of the operators available in DAX.

Table 2-2 Operators

Moreover, the logical operators are also available as DAX functions, with a syntax similar to Excel’s. For example, we can write expressions like these:

Click here to view code image

AND ( [CountryRegion] = "USA", [Quantity] > 0 )
OR ( [CountryRegion] = "USA", [Quantity] > 0 )

These examples are equivalent, respectively, to the following:

Click here to view code image

[CountryRegion] = "USA" && [Quantity] > 0
[CountryRegion] = "USA" || [Quantity] > 0

Using functions instead of operators for Boolean logic becomes helpful when writing complex conditions. In fact, when it comes to formatting large sections of code, functions are much easier to format and to read than operators are. However, a major drawback of functions is that we can pass in only two parameters at a time. Therefore, we must nest functions if we have more than two conditions to evaluate.

Table constructors

In DAX we can define anonymous tables directly in the code. If the table has a single column, the syntax requires only a list of values—one for each row—delimited by curly braces. We can delimit multiple rows by parentheses, which are optional if the table is made of a single column. The two following definitions, for example, are equivalent:

Click here to view code image

{ "Red", "Blue", "White" }
{ ( "Red" ), ( "Blue" ), ( "White" ) }

If the table has multiple columns, parentheses are mandatory. Every column should have the same data type throughout all its rows; otherwise, DAX will automatically convert the column to a data type that can accommodate all the data types provided in different rows for the same column.

Click here to view code image

{
    ( "A", 10, 1.5, DATE ( 2017, 1, 1 ), CURRENCY ( 199.99 ), TRUE ),
    ( "B", 20, 2.5, DATE ( 2017, 1, 2 ), CURRENCY ( 249.99 ), FALSE ),
    ( "C", 30, 3.5, DATE ( 2017, 1, 3 ), CURRENCY ( 299.99 ), FALSE )
}

The table constructor is commonly used with the IN operator. For example, the following are possible, valid syntaxes in a DAX predicate:

Click here to view code image

'Product'[Color] IN { "Red", "Blue", "White" }

( 'Date'[Year], 'Date'[MonthNumber] ) IN { ( 2017, 12 ), ( 2018, 1 ) }

This second example shows the syntax required to compare a set of columns (tuple) using the IN operator. Such syntax cannot be used with a comparison operator. In other words, the following syntax is not valid:

Click here to view code image

( 'Date'[Year], 'Date'[MonthNumber] ) = ( 2007, 12 )

However, we can rewrite it using the IN operator with a table constructor that has a single row, as in the following example:

Click here to view code image

( 'Date'[Year], 'Date'[MonthNumber] ) IN { ( 2007, 12 ) }

Conditional statements

In DAX we can write a conditional expression using the IF function. For example, we can write an expression returning MULTI or SINGLE depending on the quantity value being greater than one or not, respectively.

IF (
    Sales[Quantity] > 1,
    "MULTI",
    "SINGLE"
)

The IF function has three parameters, but only the first two are mandatory. The third is optional, and it defaults to BLANK. Consider the following code:

IF (
    Sales[Quantity] > 1,
    Sales[Quantity]
)

It corresponds to the following explicit version:

IF (
    Sales[Quantity] > 1,
    Sales[Quantity],
    BLANK ()
)

Calculated columns

Depending on the tool you are using, you can create a calculated column in different ways. Indeed, the concept remains the same: a calculated column is a new column added to your model, but instead of being loaded from a data source, it is created by resorting to a DAX formula.

A calculated column is just like any other column in a table, and we can use it in rows, columns, filters, or values of a matrix or any other report. We can also use a calculated column to define a relationship, if needed. The DAX expression defined for a calculated column operates in the context of the current row of the table that the calculated column belongs to. Any reference to a column returns the value of that column for the current row. We cannot directly access the values of other rows.

If you are using the default Import Mode of Tabular and are not using DirectQuery, one important concept to remember about calculated columns is that these columns are computed during database processing and then stored in the model. This concept might seem strange if you are accustomed to SQL-computed columns (not persisted), which are evaluated at query time and do not use memory. In Tabular, however, all calculated columns occupy space in memory and are computed during table processing.

This behavior is helpful whenever we create complex calculated columns. The time required to compute complex calculated columns is always process time and not query time, resulting in a better user experience. Nevertheless, be mindful that a calculated column uses precious RAM. For example, if we have a complex formula for a calculated column, we might be tempted to separate the steps of computation into different intermediate columns. Although this technique is useful during project development, it is a bad habit in production because each intermediate calculation is stored in RAM and wastes valuable space.

If a model is based on DirectQuery instead, the behavior is hugely different. In DirectQuery mode, calculated columns are computed on the fly when the Tabular engine queries the data source. This might result in heavy queries executed by the data source, therefore producing slow models.

Sales[DaysToDeliver] = Sales[Delivery Date] - Sales[Order Date]

Sales[DaysToDeliver] = INT ( Sales[Delivery Date] - Sales[Order Date] )

Measures

Calculated columns are useful, but you can define calculations in a DAX model in another way. Whenever you do not want to compute values for each row but rather want to aggregate values from many rows in a table, you will find these calculations useful; they are called measures.

For example, you can define a few calculated columns in the Sales table to compute the gross margin amount:

Click here to view code image

Sales[SalesAmount] = Sales[Quantity] * Sales[Net Price]
Sales[TotalCost] = Sales[Quantity] * Sales[Unit Cost]
Sales[GrossMargin] = Sales[SalesAmount] – Sales[TotalCost]

What happens if you want to show the gross margin as a percentage of the sales amount? You could create a calculated column with the following formula:

Click here to view code image

Sales[GrossMarginPct] = Sales[GrossMargin] / Sales[SalesAmount]

This formula computes the correct value at the row level—as you can see in Figure 2-3—but at the grand total level the result is clearly wrong.

The value shown at the grand total level is the sum of the individual percentages computed row by row within the calculated column. When we compute the aggregate value of a percentage, we cannot rely on calculated columns. Instead, we need to compute the percentage based on the sum of individual columns. We must compute the aggregated value as the sum of gross margin divided by the sum of sales amount. In this case, we need to compute the ratio on the aggregates; you cannot use an aggregation of calculated columns. In other words, we compute the ratio of the sums, not the sum of the ratios.

It would be equally wrong to simply change the aggregation of the GrossMarginPct column to an average and rely on the result because doing so would provide an incorrect evaluation of the percentage, not considering the differences between amounts. The result of this averaged value is visible in Figure 2-4, and you can easily check that (330.31 / 732.23) is not equal to the value displayed, 45.96%; it should be 45.11% instead.

The correct implementation for GrossMarginPct is with a measure:

Click here to view code image

GrossMarginPct := SUM ( Sales[GrossMargin] ) / SUM (Sales[SalesAmount] )

As we have already stated, the correct result cannot be achieved with a calculated column. If you need to operate on aggregated values instead of operating on a row-by-row basis, you must create measures. You might have noticed that we used := to define a measure instead of the equal sign (=). This is a standard we used throughout the book to make it easier to differentiate between measures and calculated columns in code.

After you define GrossMarginPct as a measure, the result is correct, as you can see in Figure 2-5.

Measures and calculated columns both use DAX expressions; the difference is the context of evaluation. A measure is evaluated in the context of a visual element or in the context of a DAX query. However, a calculated column is computed at the row level of the table it belongs to. The context of the visual element (later in the book, you will learn that this is a filter context) depends on user selections in the report or on the format of the DAX query. Therefore, when using SUM(Sales[SalesAmount]) in a measure, we mean the sum of all the rows that are aggregated under a visualization. However, when we use Sales[SalesAmount] in a calculated column, we mean the value of the SalesAmount column in the current row.

A measure needs to be defined in a table. This is one of the requirements of the DAX language. However, the measure does not really belong to the table. Indeed, we can move a measure from one table to another table without losing its functionality.

Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]

GrossMargin := SUM ( Sales[SalesAmount] ) - SUM ( Sales[TotalProductCost] )

Conversion errors

The first kind of error is the conversion error. As we showed previously in this chapter, DAX automatically converts values between strings and numbers whenever the operator requires it. All these examples are valid DAX expressions:

Click here to view code image

"10" + 32 = 42
"10" & 32 = "1032"
10 & 32 = "1032"
DATE (2010,3,25) = 3/25/2010
DATE (2010,3,25) + 14 = 4/8/2010
DATE (2010,3,25) & 14 = "3/25/201014"

These formulas are always correct because they operate with constant values. However, what about the following formula if VatCode is a string?

Sales[VatCode] + 100

Because the first operand of this sum is a column that is of Text data type, you as a developer must be confident that DAX can convert all the values in that column into numbers. If DAX fails in converting some of the content to suit the operator needs, a conversion error will occur. Here are some typical situations:

Click here to view code image

"1 + 1" + 0 = Cannot convert value '1 + 1' of type Text to type Number
DATEVALUE ("25/14/2010") = Type mismatch

If you want to avoid these errors, it is important to add error detection logic in DAX expressions to intercept error conditions and return a result that makes sense. One can obtain the same result by intercepting the error after it has happened or by checking the operands for the error situation beforehand. Nevertheless, checking for the error situation proactively is better than letting the error happen and then catching it.

Arithmetic operations errors

The second category of errors is arithmetic operations, such as the division by zero or the square root of a negative number. These are not conversion-related errors: DAX raises them whenever we try to call a function or use an operator with invalid values.

The division by zero requires special handling because its behavior is not intuitive (except, maybe, for mathematicians). When one divides a number by zero, DAX returns the special value Infinity. In the special cases of 0 divided by 0 or Infinity divided by Infinity, DAX returns the special NaN (not a number) value.

Because this is unusual behavior, it is summarized in Table 2-3.

Table 2-3 Special Result Values for Division by Zero

It is important to note that Infinity and NaN are not errors but special values in DAX. In fact, if one divides a number by Infinity, the expression does not generate an error. Instead, it returns 0:

9954 / ( 7 / 0 ) = 0

Apart from this special situation, DAX can return arithmetic errors when calling a function with an incorrect parameter, such as the square root of a negative number:

Click here to view code image

SQRT ( -1 ) = An argument of function 'SQRT' has the wrong data type or the result is too
large or too small

If DAX detects errors like this, it blocks any further computation of the expression and raises an error. One can use the ISERROR function to check if an expression leads to an error. We show this scenario later in this chapter.

Keep in mind that special values like NaN are displayed in the user interface of several tools such as Power BI as regular values. They can, however, be treated as errors when shown by other client tools such as an Excel pivot table. Finally, these special values are detected as errors by the error detection functions.

= BLANK ()

=IF (
    Sales[DiscountPerc] = 0,              -- Check if there is a discount
    BLANK (),                             -- Return a blank if no discount is present
    Sales[DiscountPerc] * Sales[Amount]   -- Compute the discount otherwise
)

= 10 * Sales[Amount]

BLANK () = 0     -- Always returns TRUE
BLANK () = ""    -- Always returns TRUE

Sales[DiscountPerc] = 0  -- Returns TRUE if DiscountPerc is either BLANK or 0
Sales[Clerk] = ""        -- Returns TRUE if Clerk is either BLANK or ""

ISBLANK ( Sales[DiscountPerc] )  -- Returns TRUE only if DiscountPerc is BLANK
ISBLANK ( Sales[Clerk] )         -- Returns TRUE only if Clerk is BLANK

BLANK () + BLANK () = BLANK ()
10 * BLANK () = BLANK ()
BLANK () / 3 = BLANK ()
BLANK () / BLANK () = BLANK ()

BLANK () − 10 = −10
18 + BLANK () = 18
4 / BLANK () = Infinity
0 / BLANK () = NaN
BLANK () || BLANK () = FALSE
BLANK () && BLANK () = FALSE
( BLANK () = BLANK () ) = TRUE
( BLANK () = TRUE ) = FALSE
( BLANK () = FALSE ) = TRUE
( BLANK () = 0 ) = TRUE
( BLANK () = "" ) = TRUE
ISBLANK ( BLANK() ) = TRUE
FALSE || BLANK () = FALSE
FALSE && BLANK () = FALSE
TRUE || BLANK () = TRUE
TRUE && BLANK () = FALSE

Intercepting errors

Now that we have detailed the various kinds of errors that can occur, we still need to show you the techniques to intercept errors and correct them or, at least, produce an error message containing meaningful information. The presence of errors in a DAX expression frequently depends on the value of columns used in the expression itself. Therefore, one might want to control the presence of these error conditions and return an error message. The standard technique is to check whether an expression returns an error and, if so, replace the error with a specific message or a default value. There are a few DAX functions for this task.

The first of them is the IFERROR function, which is similar to the IF function, but instead of evaluating a Boolean condition, it checks whether an expression returns an error. Two typical uses of the IFERROR function are as follows:

Click here to view code image

= IFERROR ( Sales[Quantity] * Sales[Price], BLANK () )
= IFERROR ( SQRT ( Test[Omega] ), BLANK () )

In the first expression, if either Sales[Quantity] or Sales[Price] is a string that cannot be converted into a number, the returned expression is an empty value. Otherwise, the product of Quantity and Price is returned.

In the second expression, the result is an empty cell every time the Test[Omega] column contains a negative number.

Using IFERROR this way corresponds to a more general pattern that requires using ISERROR and IF:

Click here to view code image

= IF (
    ISERROR ( Sales[Quantity] * Sales[Price] ),
    BLANK (),
    Sales[Quantity] * Sales[Price]
)

= IF (
    ISERROR ( SQRT ( Test[Omega] ) ),
    BLANK (),
    SQRT ( Test[Omega] )
)

In these cases, IFERROR is a better option. One can use IFERROR whenever the result is the same expression tested for an error; there is no need to duplicate the expression in two places, and the code is safer and more readable. However, a developer should use IF when they want to return the result of a different expression.

Besides, one can avoid raising the error altogether by testing parameters before using them. For example, one can detect whether the argument for SQRT is positive, returning BLANK for negative values:

= IF (
    Test[Omega] >= 0,
    SQRT ( Test[Omega] ),
    BLANK ()
)

Considering that the third argument of an IF statement defaults to BLANK, one can also write the same expression more concisely:

= IF (
    Test[Omega] >= 0,
    SQRT ( Test[Omega] )
)

A frequent scenario is to test against empty values. ISBLANK detects empty values, returning TRUE if its argument is BLANK. This capability is important especially when a value being unavailable does not imply that it is 0. The following example calculates the cost of shipping for a sale transaction, using a default shipping cost for the product if the transaction itself does not specify a weight:

Click here to view code image

= IF (
   ISBLANK ( Sales[Weight] ),             -- If the weight is missing
   Sales[DefaultShippingCost],            -- then return the default cost
   Sales[Weight] * Sales[ShippingPrice]   -- otherwise multiply weight by shipping price
)

If we simply multiply product weight by shipping price, we get an empty cost for all the sales transactions without weight data because of the propagation of BLANK in multiplications.

When using variables, errors must be checked at the time of variable definition rather than where we use them. In fact, the first formula in the following code returns zero, the second formula always throws an error, and the last one produces different results depending on the version of the product using DAX (the latest version throws an error also):

Click here to view code image

IFERROR ( SQRT ( -1 ), 0 )                -- This returns 0

VAR WrongValue = SQRT ( -1 )              -- Error happens here, so the result is
RETURN                                    -- always an error
    IFERROR ( WrongValue, 0 )             -- This line is never executed

IFERROR (                                 -- Different results depending on versions
    VAR WrongValue = SQRT ( -1 )          -- IFERROR throws an error in 2017 versions
    RETURN                                -- IFERROR returns 0 in versions until 2016
        WrongValue,
    0
)

The error happens when WrongValue is evaluated. Thus, the engine will never execute the IFERROR function in the second example, whereas the outcome of the third example depends on product versions. If you need to check for errors, take some extra precautions when using variables.

IFERROR (
   SQRT ( Test[Omega] ),
   BLANK ()
)

IF (
   Test[Omega] >= 0,
   SQRT ( Test[Omega] ),
   BLANK ()
)

SUMX (
    Table,
    IFERROR ( VALUE ( Table[Amount] ), BLANK () )
)

IFERROR (
    SUMX (
        Table,
        VALUE ( Table[Amount] )
    ),
    BLANK ()
)

Generating errors

Sometimes, an error is just an error, and the formula should not return a default value in case of an error. Indeed, returning a default value would end up producing an actual result that would be incorrect. For example, a configuration table that contains inconsistent data should produce an invalid report rather than numbers that are unreliable, and yet it might be considered correct.

Moreover, instead of a generic error, one might want to produce an error message that is more meaningful to the users. Such a message would help users find where the problem is.

Consider a scenario that requires the computation of the square root of the absolute temperature measured in Kelvin, to approximately adjust the speed of sound in a complex scientific calculation. Obviously, we do not expect that temperature to be a negative number. If that happens due to a problem in the measurement, we need to raise an error and stop the calculation.

In that case, this code is dangerous because it hides the problem:

Click here to view code image

= IFERROR (
    SQRT ( Test[Temperature] ),
    0
)

Instead, to protect the calculations, one should write the formula like this:

Click here to view code image

= IF (
    Test[Temperature] >= 0,
    SQRT ( Test[Temperature] ),
    ERROR ( "The temperature cannot be a negative number. Calculation aborted." )
)

Aggregation functions

In the previous sections, we described the basic aggregators like SUM, AVERAGE, MIN, and MAX. You learned that SUM and AVERAGE, for example, work only on numeric columns.

DAX also offers an alternative syntax for aggregation functions inherited from Excel, which adds the suffix A to the name of the function, just to get the same name and behavior as Excel. However, these functions are useful only for columns containing Boolean values because TRUE is evaluated as 1 and FALSE as 0. Text columns are always considered 0. Therefore, no matter what is in the content of a column, if one uses MAXA on a text column, the result will always be a 0. Moreover, DAX never considers empty cells when it performs the aggregation. Although these functions can be used on nonnumeric columns without retuning an error, their results are not useful because there is no automatic conversion to numbers for text columns. These functions are named AVERAGEA, COUNTA, MINA, and MAXA. We suggest that you do not use these functions, whose behavior will be kept unchanged in the future because of compatibility with existing code that might rely on current behavior.

The functions you learned earlier are useful to perform the aggregation of values. Sometimes, you might not be interested in aggregating values but only in counting them. DAX offers a set of functions that are useful to count rows or values:

• COUNT operates on any data type, apart from Boolean.

• COUNTA operates on any type of column.

• COUNTBLANK returns the number of empty cells (blanks or empty strings) in a column.

• COUNTROWS returns the number of rows in a table.

• DISTINCTCOUNT returns the number of distinct values of a column, blank value included if present.

• DISTINCTCOUNTNOBLANK returns the number of distinct values of a column, no blank value included.

COUNT and COUNTA are nearly identical functions in DAX. They return the number of values of the column that are not empty, regardless of their data type. They are inherited from Excel, where COUNTA accepts any data type including strings, whereas COUNT accepts only numeric columns. If we want to count all the values in a column that contain an empty value, you can use the COUNTBLANK function. Both blanks and empty values are considered empty values by COUNTBLANK. Finally, if we want to count the number of rows of a table, you can use the COUNTROWS function. Beware that COUNTROWS requires a table as a parameter, not a column.

The last two functions, DISTINCTCOUNT and DISTINCTCOUNTNOBLANK, are useful because they do exactly what their names suggest: count the distinct values of a column, which it takes as its only parameter. DISTINCTCOUNT counts the BLANK value as one of the possible values, whereas DISTINCTCOUNTNOBLANK ignores the BLANK value.

Logical functions

Sometimes we want to build a logical condition in an expression—for example, to implement different calculations depending on the value of a column or to intercept an error condition. In these cases, we can use one of the logical functions in DAX. The earlier section titled “Handling errors in DAX expressions” described the two most important functions of this group: IF and IFERROR. We described the IF function in the “Conditional statements” section, earlier in this chapter.

Logical functions are very simple and do what their names suggest. They are AND, FALSE, IF, IFERROR, NOT, TRUE, and OR. For example, if we want to compute the amount as quantity multiplied by price only when the Price column contains a numeric value, we can use the following pattern:

Click here to view code image

Sales[Amount] = IFERROR ( Sales[Quantity] * Sales[Price], BLANK ( ) )

If we did not use IFERROR and if the Price column contained an invalid number, the result for the calculated column would be an error because if a single row generates a calculation error, the error propagates to the whole column. The use of IFERROR, however, intercepts the error and replaces it with a blank value.

Another interesting function in this category is SWITCH, which is useful when we have a column containing a low number of distinct values, and we want to get different behaviors depending on its value. For example, the column Size in the Product table contains S, M, L, XL, and we might want to decode this value in a more explicit column. We can obtain the result by using nested IF calls:

Click here to view code image

'Product'[SizeDesc] =
IF (
    'Product'[Size] = "S",
    "Small",
    IF (
        'Product'[Size] = "M",
        "Medium",
        IF (
            'Product'[Size] = "L",
            "Large",
            IF (
                'Product'[Size] = "XL",
                "Extra Large",
                "Other"
            )
        )
    )
)

A more convenient way to express the same formula, using SWITCH, is like this:

Click here to view code image

'Product'[SizeDesc] =
SWITCH (
    'Product'[Size],
    "S", "Small",
    "M", "Medium",
    "L", "Large",
    "XL", "Extra Large",
    "Other"
)

The code in this latter expression is more readable, though not faster, because internally DAX translates SWITCH statements into a set of nested IF functions.

SWITCH (
   TRUE (),
   Product[Size] = "XL" && Product[Color] = "Red", "Red and XL",
   Product[Size] = "XL" && Product[Color] = "Blue", "Blue and XL",
   Product[Size] = "L" && Product[Color] = "Green", "Green and L"
)

Information functions

Whenever there is the need to analyze the type of an expression, you can use one of the information functions. All these functions return a Boolean value and can be used in any logical expression. They are ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, and ISTEXT.

It is important to note that when a column is passed as a parameter instead of an expression, the functions ISNUMBER, ISTEXT, and ISNONTEXT always return TRUE or FALSE depending on the data type of the column and on the empty condition of each cell. This makes these functions nearly useless in DAX; they have been inherited from Excel in the first DAX version.

You might be wondering whether you can use ISNUMBER with a text column just to check whether a conversion to a number is possible. Unfortunately, this approach is not possible. If you want to test whether a text value is convertible to a number, you must try the conversion and handle the error if it fails. For example, to test whether the column Price (which is of type string) contains a valid number, one must write

Click here to view code image

Sales[IsPriceCorrect] = NOT ISERROR ( VALUE ( Sales[Price] ) )

DAX tries to convert from a string value to a number. If it succeeds, it returns TRUE (because ISERROR returns FALSE); otherwise, it returns FALSE (because ISERROR returns TRUE). For example, the conversion fails if some of the rows have an “N/A” string value for price.

However, if we try to use ISNUMBER, as in the following expression, we always receive FALSE as a result:

Click here to view code image

Sales[IsPriceCorrect] = ISNUMBER ( Sales[Price] )

In this case, ISNUMBER always returns FALSE because, based on the definition in the model, the Price column is not a number but a string, regardless of the content of each row.

Mathematical functions

The set of mathematical functions available in DAX is similar to the set available in Excel, with the same syntax and behavior. The mathematical functions of common use are ABS, EXP, FACT, LN, LOG, LOG10, MOD, PI, POWER, QUOTIENT, SIGN, and SQRT. Random functions are RAND and RANDBETWEEN. By using EVEN and ODD, you can test numbers. GCD and LCM are useful to compute the greatest common denominator and least common multiple of two numbers. QUOTIENT returns the integer division of two numbers.

Finally, several rounding functions deserve an example; in fact, we might use several approaches to get the same result. Consider these calculated columns, along with their results in Figure 2-8:

Click here to view code image

FLOOR = FLOOR ( Tests[Value], 0.01 )
TRUNC = TRUNC ( Tests[Value], 2 )
ROUNDDOWN = ROUNDDOWN ( Tests[Value], 2 )
MROUND = MROUND ( Tests[Value], 0.01 )
ROUND = ROUND ( Tests[Value], 2 )
CEILING = CEILING ( Tests[Value], 0.01 )
ISO.CEILING = ISO.CEILING ( Tests[Value], 0.01 )
ROUNDUP = ROUNDUP ( Tests[Value], 2 )
INT = INT ( Tests[Value] )
FIXED = FIXED ( Tests[Value], 2, TRUE )

FLOOR, TRUNC, and ROUNDDOWN are similar except in the way we can specify the number of digits to round. In the opposite direction, CEILING and ROUNDUP are similar in their results. You can see a few differences in the way the rounding is done between MROUND and ROUND function.

Trigonometric functions

DAX offers a rich set of trigonometric functions that are useful for certain calculations: COS, COSH, COT, COTH, SIN, SINH, TAN, and TANH. Prefixing them with A computes the arc version (arcsine, arccosine, and so on). We do not go into the details of these functions because their use is straightforward.

DEGREES and RADIANS perform conversion to degrees and radians, respectively, and SQRTPI computes the square root of its parameter after multiplying it by pi.

Text functions

Most of the text functions available in DAX are similar to those available in Excel, with only a few exceptions. The text functions are CONCATENATE, CONCATENATEX, EXACT, FIND, FIXED, FORMAT, LEFT, LEN, LOWER, MID, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, TRIM, UPPER, and VALUE. These functions are useful for manipulating text and extracting data from strings that contain multiple values. For example, Figure 2-9 shows an example of the extraction of first and last names from a string that contains these values separated by commas, with the title in the middle that we want to remove.

To achieve this result, you start calculating the position of the two commas. Then we use these numbers to extract the right part of the text. The SimpleConversion column implements a formula that might return inaccurate values if there are fewer than two commas in the string, and it raises an error if there are no commas at all. The FirstLastName column implements a more complex expression that does not fail in case of missing commas:

Click here to view code image

People[Comma1] = IFERROR ( FIND ( ",", People[Name] ), BLANK ( ) )
People[Comma2] = IFERROR ( FIND ( " ,", People[Name], People[Comma1] + 1 ), BLANK ( ) )
People[SimpleConversion] =
MID ( People[Name], People[Comma2] + 1, LEN ( People[Name] ) )
    & " "
    & LEFT ( People[Name], People[Comma1] - 1 )
People[FirstLastName] =
TRIM (
    MID (
        People[Name],
        IF ( ISNUMBER ( People[Comma2] ), People[Comma2], People[Comma1] ) + 1,
        LEN ( People[Name] )
    )
)
    & IF (
        ISNUMBER ( People[Comma1] ),
        " " & LEFT ( People[Name], People[Comma1] - 1 ),
        ""
    )

As you can see, the FirstLastName column is defined by a long DAX expression, but you must use it to avoid possible errors that would propagate to the whole column if even a single value generates an error.

Conversion functions

You learned previously that DAX performs automatic conversions of data types to adjust them to operator needs. Although the conversion happens automatically, a set of functions can still perform explicit data type conversions.

CURRENCY can transform an expression into a Currency type, whereas INT transforms an expression into an Integer. DATE and TIME take the date and time parts as parameters and return a correct DateTime. VALUE transforms a string into a numeric format, whereas FORMAT gets a numeric value as its first parameter and a string format as its second parameter, and it can transform numeric values into strings. FORMAT is commonly used with DateTime. For example, the following expression returns “2019 Jan 12”:

Click here to view code image

= FORMAT ( DATE ( 2019, 01, 12 ), "yyyy mmm dd" )

The opposite operation, that is, converting strings into DateTime values, is performed using the DATEVALUE function.

DATEVALUE ( "28/02/2018" ) -- This is February 28 in European format
DATEVALUE ( "02/28/2018" ) -- This is February 28 in American format
DATEVALUE ( "2018-02-28" ) -- This is February 28 (format is not ambiguous)

Date and time functions

In almost every type of data analysis, handling time and dates is an important part of the job. Many DAX functions operate on date and time. Some of them correspond to similar functions in Excel and make simple transformations to and from a DateTime data type. The date and time functions are DATE, DATEVALUE, DAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NOW, SECOND, TIME, TIMEVALUE, TODAY, WEEKDAY, WEEKNUM, YEAR, and YEARFRAC.

These functions are useful to compute values on top of dates, but they are not used to perform typical time intelligence calculations such as comparing aggregated values year over year or calculating the year-to-date value of a measure. To perform time intelligence calculations, you use another set of functions called time intelligence functions, which we describe in Chapter 8, “Time intelligence calculations.”

As we mentioned earlier in this chapter, a DateTime data type internally uses a floating-point number wherein the integer part corresponds to the number of days after December 30, 1899, and the decimal part indicates the fraction of the day in time. Hours, minutes, and seconds are converted into decimal fractions of the day. Thus, adding an integer number to a DateTime value increments the value by a corresponding number of days. However, you will probably find it more convenient to use the conversion functions to extract the day, month, and year from a date. The following expressions used in Figure 2-10 show how to extract this information from a table containing a list of dates:

Click here to view code image

'Date'[Day] = DAY ( Calendar[Date] )
'Date'[Month] = FORMAT ( Calendar[Date], "mmmm" )
'Date'[MonthNumber] = MONTH ( Calendar[Date] )
'Date'[Year] = YEAR ( Calendar[Date] )

Relational functions

Two useful functions that you can use to navigate through relationships inside a DAX formula are RELATED and RELATEDTABLE.

You already know that a calculated column can reference column values of the table in which it is defined. Thus, a calculated column defined in Sales can reference any column of Sales. However, what if one must refer to a column in another table? In general, one cannot use columns in other tables unless a relationship is defined in the model between the two tables. If the two tables share a relationship, you can use the RELATED function to access columns in the related table.

For example, one might want to compute a calculated column in the Sales table that checks whether the product that has been sold is in the “Cell phones” category and, in that case, apply a reduction factor to the standard cost. To compute such a column, one must use a condition that checks the value of the product category, which is not in the Sales table. Nevertheless, a chain of relationships starts from Sales, reaching Product Category through Product and Product Subcategory, as shown in Figure 2-11.

Regardless of how many steps are necessary to travel from the original table to the related table, DAX follows the complete chain of relationships, and it returns the related column value. Thus, the formula for the AdjustedCost column can look like this:

Click here to view code image

Sales[AdjustedCost] =
IF (
    RELATED ( 'Product Category'[Category] ) = "Cell Phone",
    Sales[Unit Cost] * 0.95,
    Sales[Unit Cost]
)

In a one-to-many relationship, RELATED can access the one-side from the many-side because in that case, only one row in the related table exists, if any. If no such row exists, RELATED returns BLANK.

If an expression is on the one-side of the relationship and needs to access the many-side, RELATED is not helpful because many rows from the other side might be available for a single row. In that case, we can use RELATEDTABLE. RELATEDTABLE returns a table containing all the rows related to the current row. For example, if we want to know how many products are in each category, we can create a column in Product Category with this formula:

Click here to view code image

'Product Category'[NumOfProducts] = COUNTROWS ( RELATEDTABLE ( Product ) )

For each product category, this calculated column shows the number of products related, as shown in Figure 2-12.

As is the case for RELATED, RELATEDTABLE can follow a chain of relationships always starting from the one-side and going toward the many-side. RELATEDTABLE is often used in conjunction with iterators. For example, if we want to compute the sum of quantity multiplied by net price for each category, we can write a new calculated column as follows:

Click here to view code image

'Product Category'[CategorySales] =
SUMX (
    RELATEDTABLE ( Sales ),
    Sales[Quantity] * Sales[Net Price]
)

The result of this calculated column is shown in Figure 2-13.

Because the column is calculated, this result is consolidated in the table, and it does not change according to the user selection in the report, as it would if it were written in a measure.

Understanding filter contexts

Let us begin by understanding what an evaluation context is. All DAX expressions are evaluated inside a context. The context is the “environment” within which the formula is evaluated. For example, consider a measure such as

Click here to view code image

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

This formula computes the sum of quantity multiplied by price in the Sales table. We can use this measure in a report and look at the results, as shown in Figure 4-1.

This number alone does not look interesting. However, if you think carefully, the formula computes exactly what one would expect: the sum of all sales amounts. In a real report, one is likely to slice the value by a certain column. For example, we can select the product brand, use it on the rows, and the matrix report starts to reveal interesting business insights as shown in Figure 4-2.

The grand total is still there, but now it is the sum of smaller values. Each value, together with all the others, provides more detailed insights. However, you should note that something weird is happening: The formula is not computing what we apparently asked. In fact, inside each cell of the report, the formula is no longer computing the sum of all sales. Instead, it computes the sales of a given brand. Finally, note that nowhere in the code does it say that it can (or should) work on subsets of data. This filtering happens outside of the formula.

Each cell computes a different value because of the evaluation context under which DAX executes the formula. You can think of the evaluation context of a formula as the surrounding area of the cell where DAX evaluates the formula.

DAX evaluates all formulas within a respective context. Even though the formula is the same, the result is different because DAX executes the same code against different subsets of data.

This context is named Filter Context and, as the name suggests, it is a context that filters tables. Any formula ever authored will have a different value depending on the filter context used to perform its evaluation. This behavior, although intuitive, needs to be well understood because it hides many complexities.

Every cell of the report has a different filter context. You should consider that every cell has a different evaluation—as if it were a different query, independent from the other cells in the same report. The engine might perform some level of internal optimization to improve computation speed, but you should assume that every cell has an independent and autonomous evaluation of the underlying DAX expression. Therefore, the computation of the Total row in Figure 4-2 is not computed by summing the other rows of the report. It is computed by aggregating all the rows of the Sales table, although this means other iterations were already computed for the other rows in the same report. Consequently, depending on the DAX expression, the result in the Total row might display a different result, unrelated to the other rows in the same report.

When Brand is on the rows, the filter context filters one brand for each cell. If we increase the complexity of the matrix by adding the year on the columns, we obtain the report in Figure 4-3.

Now each cell shows a subset of data pertinent to one brand and one year. The reason for this is that the filter context of each cell now filters both the brand and the year. In the Total row, the filter is only on the brand, whereas in the Total column the filter is only on the year. The grand total is the only cell that computes the sum of all sales because—there—the filter context does not apply any filter to the model.

The rules of the game should be clear at this point: The more columns we use to slice and dice, the more columns are being filtered by the filter context in each cell of the matrix. If one adds the Store[Continent] column to the rows, the result is—again—different, as shown in Figure 4-4.

Now the filter context of each cell is filtering brand, country, and year. In other words, the filter context contains the complete set of fields that one uses on rows and columns of the report.

Visual interactions in Power BI compose a filter context by combining different elements from a graphical interface. Indeed, the filter context of a cell is computed by merging together all the filters coming from rows, columns, slicers, and any other visual used for filtering. For example, look at Figure 4-5.

The filter context of the top-left cell (A.Datum, CY 2007, 57,276.00) not only filters the row and the column of the visual, but it also filters the occupation (Professional) and the continent (Europe), which are coming from different visuals. All these filters contribute to the definition of a single filter context valid for one cell, which DAX applies to the whole data model prior to evaluating the formula.

A more formal definition of a filter context is to say that a filter context is a set of filters. A filter, in turn, is a list of tuples, and a tuple is a set of values for some defined columns. Figure 4-6 shows a visual representation of the filter context under which the highlighted cell is evaluated. Each element of the report contributes to creating the filter context, and every cell in the report has a different filter context.

The filter context of Figure 4-6 contains three filters. The first filter contains a tuple for Calendar Year with the value CY 2007. The second filter contains two tuples for Education with the values High School and Partial College. The third filter contains a single tuple for Brand, with the value Contoso. You might notice that each filter contains tuples for one column only. You will learn how to create tuples with multiple columns later. Multi-column tuples are both powerful and complex tools in the hand of a DAX developer.

Before leaving this introduction, let us recall the measure used at the beginning of this section:

Click here to view code image

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Here is the correct way of reading the previous measure: The measure computes the sum of Quantity multiplied by Net Price for all the rows in Sales which are visible in the current filter context.

The same applies to simpler aggregations. For example, consider this measure:

Click here to view code image

Total Quantity := SUM ( Sales[Quantity] )

It sums the Quantity column of all the rows in Sales that are visible in the current filter context. You can better understand its working by considering the corresponding SUMX version:

Click here to view code image

Total Quantity := SUMX ( Sales, Sales[Quantity] )

Looking at the SUMX definition, we might consider that the filter context affects the evaluation of the Sales expression, which only returns the rows of the Sales table that are visible in the current filter context. This is true, but you should consider that the filter context also applies to the following measures, which do not have a corresponding iterator:

Click here to view code image

Customers := DISTINCTCOUNT ( Sales[CustomerKey] )  -- Count customers in filter context

Colors :=
VAR ListColors = DISTINCT ( 'Product'[Color] )     -- Unique colors in filter context
RETURN COUNTROWS ( ListColors )                    -- Count unique colors

It might look pedantic, at this point, to spend so much time stressing the concept that a filter context is always active, and that it affects the formula result. Nevertheless, keep in mind that DAX requires you to be extremely precise. Most of the complexity of DAX is not in learning new functions. Instead, the complexity comes from the presence of many subtle concepts. When these concepts are mixed together, what emerges is a complex scenario. Right now, the filter context is defined by the report. As soon as you learn how to create filter contexts by yourself (a critical skill described in the next chapter), being able to understand which filter context is active in each part of your formula will be of paramount importance.

Understanding the row context

In the previous section, you learned about the filter context. In this section, you now learn the second type of evaluation context: the row context. Remember, although both the row context and the filter context are evaluation contexts, they are not the same concept. As you learned in the previous section, the purpose of the filter context is, as its name implies, to filter tables. On the other hand, the row context is not a tool to filter tables. Instead, it is used to iterate over tables and evaluate column values.

This time we use a different formula for our considerations, defining a calculated column to compute the gross margin:

Click here to view code image

Sales[Gross Margin] = Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )

There is a different value for each row in the resulting calculated column, as shown in Figure 4-7.

As expected, for each row of the table there is a different value in the calculated column. Indeed, because there are given values in each row for the three columns used in the expression, it comes as a natural consequence that the final expression computes different values. As it happened with the filter context, the reason is the presence of an evaluation context. This time, the context does not filter a table. Instead, it identifies the row for which the calculation happens.

In other words, we know that a calculated column is computed row by row, but how does DAX know which row it is currently iterating? It knows the row because there is another evaluation context providing the row—it is the row context. When we create a calculated column over a table with one million rows, DAX creates a row context that evaluates the expression iterating over the table row by row, using the row context as the cursor.

When we create a calculated column, DAX creates a row context by default. In that case, there is no need to manually create a row context: A calculated column is always executed in a row context. You have already learned how to create a row context manually—by starting an iteration. In fact, one can write the gross margin as a measure, like in the following code:

Click here to view code image

Gross Margin :=
SUMX (
    Sales,
    Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )
)

In this case, because the code is for a measure, there is no automatic row context. SUMX, being an iterator, creates a row context that starts iterating over the Sales table, row by row. During the iteration, it executes the second expression of SUMX inside the row context. Thus, during each step of the iteration, DAX knows which value to use for the three column names used in the expression.

The row context exists when we create a calculated column or when we are computing an expression inside an iteration. There is no other way of creating a row context. Moreover, it helps to think that a row context is needed whenever we want to obtain the value of a column for a certain row. For example, the following measure definition is invalid. Indeed, it tries to compute the value of Sales[Net Price] and there is no row context providing the row for which the calculation needs to be executed:

Click here to view code image

Gross Margin := Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )

This same expression is valid when executed for a calculated column, and it is invalid if used in a measure. The reason is not that measures and calculated columns have different ways of using DAX. The reason is that a calculated column has an automatic row context, whereas a measure does not. If one wants to evaluate an expression row by row inside a measure, one needs to start an iteration to create a row context.

At this point, we need to repeat one important concept: A row context is not a special kind of filter context that filters one row. The row context is not filtering the model in any way; the row context only indicates to DAX which row to use out of a table. If one wants to apply a filter to the model, the tool to use is the filter context. On the other hand, if the user wants to evaluate an expression row by row, then the row context will do the job.

Using SUM in a calculated column

The first test uses an aggregator inside a calculated column. What is the result of the following expression, used in a calculated column, in Sales?

Click here to view code image

Sales[SumOfSalesQuantity] = SUM ( Sales[Quantity] )

Remember, this internally corresponds to this equivalent syntax:

Click here to view code image

Sales[SumOfSalesQuantity] = SUMX ( Sales, Sales[Quantity] )

Because it is a calculated column, it is computed row by row in a row context. What number do you expect to see? Choose from these three answers:

• The value of Quantity for that row, that is, a different value for each row.

• The total of Quantity for all the rows, that is, the same value for all the rows.

• An error; we cannot use SUM inside a calculated column.

Stop reading, please, while we wait for your educated guess before moving on.

Here is the correct reasoning. You have learned that the formula means, “the sum of quantity for all the rows visible in the current filter context.” Moreover, because the code is executed for a calculated column, DAX evaluates the formula row by row, in a row context. Nevertheless, the row context is not filtering the table. The only context that can filter the table is the filter context. This turns the question into a different one: What is the filter context, when the formula is evaluated? The answer is straightforward: The filter context is empty. Indeed, the filter context is created by visuals or by queries, and a calculated column is computed at data refresh time when no filtering is happening. Thus, SUM works on the whole Sales table, aggregating the value of Sales[Quantity] for all the rows of Sales.

The correct answer is the second answer. This calculated column computes the same value for each row, that is, the grand total of Sales[Quantity] repeated for all the rows. Figure 4-8 shows the result of the SumOfSalesQuantity calculated column.

This example shows that the two evaluation contexts exist at the same time, but they do not interact. The evaluation contexts both work on the result of a formula, but they do so in different ways. Aggregators like SUM, MIN, and MAX only use the filter context, and they ignore the row context. If you have chosen the first answer, as many students typically do, it is perfectly normal. The thing is that you are still confusing the filter context and the row context. Remember, the filter context filters; the row context iterates. The first answer is the most common, when using intuitive logic, but it is wrong—now you know why. However, if you chose the correct answer ... then we are glad this section helped you in learning the important difference between the two contexts.

Using columns in a measure

The second test is slightly different. Imagine we define the formula for the gross margin in a measure instead of in a calculated column. We have a column with the net price, another column for the product cost, and we write the following expression:

Click here to view code image

GrossMargin% := ( Sales[Net Price] - Sales[Unit Cost] ) / Sales[Unit Cost]

What will the result be? As it happened earlier, choose among the three possible answers:

• The expression works correctly, time to test the result in a report.

• An error, we should not even write this formula.

• We can define the formula, but it will return an error when used in a report.

As in the previous test, stop reading, think about the answer, and then read the following explanation.

The code references Sales[Net Price] and Sales[Unit Cost] without any aggregator. As such, DAX needs to retrieve the value of the columns for a certain row. DAX has no way of detecting which row the formula needs to be computed for because there is no iteration happening and the code is not in a calculated column. In other words, DAX is missing a row context that would make it possible to retrieve a value for the columns that are part of the expression. Remember that a measure does not have an automatic row context; only calculated columns do. If we need a row context in a measure, we should start an iteration.

Thus, the second answer is the correct one. We cannot write the formula because it is syntactically wrong, and we get an error when trying to enter the code.

Nested row contexts on different tables

The expression evaluated by an iterator can be very complex. Moreover, the expression can, on its own, contain further iterations. At first sight, starting an iteration inside another iteration might look strange. Still, it is a common DAX practice because nesting iterators produce powerful expressions.

For example, the following code contains three nested iterators, and it scans three tables: Categories, Products, and Sales.

Click here to view code image

SUMX (
    'Product Category',                   -- Scans the Product Category table
    SUMX (                                -- For each category
        RELATEDTABLE ( 'Product' ),       -- Scans the category products
        SUMX (                            -- For each product
            RELATEDTABLE ( Sales )        -- Scans the sales of that product
            Sales[Quantity]               --
                * 'Product'[Unit Price]   -- Computes the sales amount of that sale
                * 'Product Category'[Discount]
        )
    )
)

The innermost expression—the multiplication of three factors—references three tables. In fact, three row contexts are opened during that expression evaluation: one for each of the three tables that are currently being iterated. It is also worth noting that the two RELATEDTABLE functions return the rows of a related table starting from the current row context. Thus, RELATEDTABLE ( Product), being executed in a row context from the Categories table, returns the products of the given category. The same reasoning applies to RELATEDTABLE ( Sales ), which returns the sales of the given product.

The previous code is suboptimal in terms of both performance and readability. As a rule, it is fine to nest iterators provided that the number of rows to scan is not too large: hundreds is good, thousands is fine, millions is bad. Otherwise, we may easily hit performance issues. We used the previous code to demonstrate that it is possible to create multiple nested row contexts; we will see more useful examples of nested iterators later in the book. One can express the same calculation in a much faster and readable way by using the following code, which relies on one individual row context and the RELATED function:

Click here to view code image

SUMX (
    Sales,
    Sales[Quantity]
        * RELATED ( 'Product'[Unit Price] )
        * RELATED ( 'Product Category'[Discount] )
)

Whenever there are multiple row contexts on different tables, one can use them to reference the iterated tables in a single DAX expression. There is one scenario, however, which proves to be challenging. This happens when we nest multiple row contexts on the same table, which is the topic covered in the following section.

Nested row contexts on the same table

The scenario of having nested row contexts on the same table might seem rare. However, it does happen quite often, and more frequently in calculated columns. Imagine we want to rank products based on the list price. The most expensive product should be ranked 1, the second most expensive product should be ranked 2, and so on. We could solve the scenario using the RANKX function. But for educational purposes, we show how to solve it using simpler DAX functions.

To compute the ranking, for each product we can count the number of products whose price is higher than the current product’s. If there is no product with a higher price than the current product price, then the current product is the most expensive and its ranking is 1. If there is only one product with a higher price, then the ranking is 2. In fact, what we are doing is computing the ranking of a product by counting the number of products with a higher price and adding 1 to the result.

Therefore, one can author a calculated column using this code, where we used PriceOfCurrentProduct as a placeholder to indicate the price of the current product.

Click here to view code image

1.  'Product'[UnitPriceRank] =
2.  COUNTROWS (
3.      FILTER (
4.          'Product',
5.          'Product'[Unit Price] > PriceOfCurrentProduct
6.      )
7.  ) + 1

FILTER returns the products with a price higher than the current products’ price, and COUNTROWS counts the rows of the result of FILTER. The only remaining issue is finding a way to express the price of the current product, replacing PriceOfCurrentProduct with a valid DAX syntax. By “current,” we mean the value of the column in the current row when DAX computes the column. It is harder than you might expect.

Focus your attention on line 5 of the previous code. There, the reference to Product[Unit Price] refers to the value of Unit Price in the current row context. What is the active row context when DAX executes row number 5? There are two row contexts. Because the code is written in a calculated column, there is a default row context automatically created by the engine that scans the Product table. Moreover, FILTER being an iterator, there is the row context generated by FILTER that scans the product table again. This is shown graphically in Figure 4-9.

The outer box includes the row context of the calculated column, which is iterating over Product. However, the inner box shows the row context of the FILTER function, which is iterating over Product too. The expression Product[Unit Price] depends on the context. Therefore, a reference to Product[Unit Price] in the inner box can only refer to the currently iterated row by FILTER. The problem is that, in that box, we need to evaluate the value of Unit Price that is referenced by the row context of the calculated column, which is now hidden.

Indeed, when one does not create a new row context using an iterator, the value of Product[Unit Price] is the desired value, which is the value in the current row context of the calculated column, as in this simple piece of code:

Click here to view code image

Product[Test] = Product[Unit Price]

To further demonstrate this, let us evaluate Product[Unit Price] in the two boxes, with some dummy code. What comes out are different results as shown in Figure 4-10, where we added the evaluation of Product[Unit Price] right before COUNTROWS, only for educational purposes.

Here is a recap of the scenario so far:

• The inner row context, generated by FILTER, hides the outer row context.

• We need to compare the inner Product[Unit Price] with the value of the outer Product[Unit Price].

• If we write the comparison in the inner expression, we are unable to access the outer Product[Unit Price].

Because we can retrieve the current unit price, if we evaluate it outside of the row context of FILTER, the best approach to this problem is saving the value of the Product[Unit Price] inside a variable. Indeed, one can evaluate the variable in the row context of the calculated column using this code:

Click here to view code image

'Product'[UnitPriceRank] =
VAR
    PriceOfCurrentProduct = 'Product'[Unit Price]
RETURN
    COUNTROWS (
        FILTER (
           'Product',
           'Product'[Unit Price] > PriceOfCurrentProduct
        )
    ) + 1

Moreover, it is even better to write the code in a more descriptive way by using more variables to separate the different steps of the calculation. This way, the code is also easier to follow:

Click here to view code image

'Product'[UnitPriceRank] =
VAR PriceOfCurrentProduct = 'Product'[Unit Price]
VAR MoreExpensiveProducts =
    FILTER (
        'Product',
        'Product'[Unit Price] > PriceOfCurrentProduct
    )
RETURN
    COUNTROWS ( MoreExpensiveProducts ) + 1

Figure 4-11 shows a graphical representation of the row contexts of this latter formulation of the code, which makes it easier to understand which row context DAX computes each part of the formula in.

Figure 4-12 shows the result of this calculated column.

Because there are 14 products with the same unit price, their rank is always 1; the fifteenth product has a rank of 15, shared with other products with the same price. It would be great if we could rank 1, 2, 3 instead of 1, 15, 19 as is the case in the figure. We will fix this soon but, before that, it is important to make a small digression.

To solve a scenario like the one proposed, it is necessary to have a solid understanding of what a row context is, to be able to detect which row context is active in different parts of the formula and, most importantly, to conceive how the row context affects the value returned by a DAX expression. It is worth stressing that the same expression Product[Unit Price], evaluated in two different parts of the formula, returns different values because of the different contexts under which it is evaluated. When one does not have a solid understanding of evaluation contexts, it is extremely hard to work on such complex code.

As you have seen, a simple ranking expression with two row contexts proves to be a challenge. Later in Chapter 5 you learn how to create multiple filter contexts. At that point, the complexity of the code increases a lot. However, if you understand evaluation contexts, these scenarios are simple. Before moving to the next level in DAX, you need to understand evaluation contexts well. This is the reason why we urge you to read this whole section again—and maybe the whole chapter so far—until these concepts are crystal clear. It will make reading the next chapters much easier and your learning experience much smoother.

Before leaving this example, we need to solve the last detail—that is, ranking using a sequence of 1, 2, 3 instead of the sequence obtained so far. The solution is easier than expected. In fact, in the previous code we focused on counting the products with a higher price. By doing that, the formula counted 14 products ranked 1 and assigned 15 to the second ranking level. However, counting products is not very useful. If the formula counted the prices higher than the current price, rather than the products, then all 14 products would be collapsed into a single price.

Click here to view code image

'Product'[UnitPriceRankDense] =
VAR PriceOfCurrentProduct = 'Product'[Unit Price]
VAR HigherPrices =
    FILTER (
        VALUES ( 'Product'[Unit Price] ),
        'Product'[Unit Price] > PriceOfCurrentProduct
    )
RETURN
    COUNTROWS ( HigherPrices ) + 1

Figure 4-13 shows the new calculated column, along with UnitPriceRank.

This final small step is counting prices instead of counting products, and it might seem harder than expected. The more you work with DAX, the easier it will become to start thinking in terms of ad hoc temporary tables created for the purpose of a calculation.

In this example you learned that the best technique to handle multiple row contexts on the same table is by using variables. Keep in mind that variables were introduced in the DAX language as late as 2015. You might find existing DAX code—written before the age of variables—that uses another technique to access outer row contexts: the EARLIER function, which we describe in the next section.

Using the EARLIER function

DAX provides a function that accesses the outer row contexts: EARLIER. EARLIER retrieves the value of a column by using the previous row context instead of the last one. Therefore, we can express the value of PriceOfCurrentProduct using EARLIER ( Product[UnitPrice] ).

Many DAX newbies feel intimidated by EARLIER because they do not understand row contexts well enough and they do not realize that they can nest row contexts by creating multiple iterations over the same table. EARLIER is a simple function, once you understand the concept of row context and nesting. For example, the following code solves the previous scenario without using variables:

Click here to view code image

'Product'[UnitPriceRankDense] =
COUNTROWS (
    FILTER (
        VALUES ( 'Product'[Unit Price] ),
        'Product'[UnitPrice] > EARLIER ( 'Product'[UnitPrice] )
    )
) + 1

The only reason to learn EARLIER is to be able to read existing DAX code. There are no further reasons to use EARLIER in newer DAX code because variables are a better way to save the required value when the right row context is accessible. Using variables for this purpose is a best practice and results in more readable code.

Row contexts and relationships

The row context iterates; it does not filter. Iteration is the process of scanning a table row by row and of performing an operation in the meantime. Usually, one wants some kind of aggregation like sum or average. During an iteration, the row context is iterating an individual table, and it provides a value to all the columns of the table, and only that table. Other tables, although related to the iterated table, do not have a row context on them. In other words, the row context does not interact automatically with relationships.

Consider as an example a calculated column in the Sales table containing the difference between the unit price stored in the fact table and the unit price stored in the Product table. The following DAX code does not work because it uses the Product[UnitPrice] column and there is no row context on Product:

Click here to view code image

Sales[UnitPriceVariance] = Sales[Unit Price] - 'Product'[Unit Price]

This being a calculated column, DAX automatically generates a row context on the table containing the column, which is the Sales table. The row context on Sales provides a row-by-row evaluation of expressions using the columns in Sales. Even though Product is on the one-side of a one-to-many relationship with Sales, the iteration is happening on the Sales table only.

When we are iterating on the many-side of a relationship, we can access columns on the one-side of the relationship, but we must use the RELATED function. RELATED accepts a column reference as the parameter and retrieves the value of the column in the corresponding row in the target table. RELATED can only reference one column and multiple RELATED functions are required to access more than one column on the one-side of the relationship. The correct version of the previous code is the following:

Click here to view code image

Sales[UnitPriceVariance] = Sales[Unit Price] - RELATED ( 'Product'[Unit Price] )

RELATED requires a row context (that is, an iteration) on the table on the many-side of a relationship. If the row context were active on the one-side of a relationship, then RELATED would no longer be useful because RELATED would find multiple rows by following the relationship. In this case, that is, when iterating the one-side of a relationship, the function to use is RELATEDTABLE. RELATEDTABLE returns all the rows of the table on the many-side that are related with the currently iterated table. For example, if one wants to compute the number of sales of each product, the following formula defined as a calculated column on Product solves the problem:

Click here to view code image

Product[NumberOfSales] =
VAR SalesOfCurrentProduct = RELATEDTABLE ( Sales )
RETURN
    COUNTROWS ( SalesOfCurrentProduct )

This expression counts the number of rows in the Sales table that corresponds to the current product. The result is visible in Figure 4-18.

Both RELATED and RELATEDTABLE can traverse a chain of relationships; they are not limited to a single hop. For example, one can create a column with the same code as before but, this time, in the Product Category table:

Click here to view code image

'Product Category'[NumberOfSales] =
VAR SalesOfCurrentProductCategory = RELATEDTABLE ( Sales )
RETURN
    COUNTROWS ( SalesOfCurrentProductCategory )

The result is the number of sales for the category, which traverses the chain of relationships from Product Category to Product Subcategory, then to Product to finally reach the Sales table.

In a similar way, one can create a calculated column in the Product table that copies the category name from the Product Category table.

Click here to view code image

'Product'[Category] = RELATED ( 'Product Category'[Category] )

In this case, a single RELATED function traverses the chain of relationships from Product to Product Subcategory to Product Category.

Regarding chains of relationships, all the relationships need to be of the same type—that is, one-to-many or many-to-one. If the chain links two tables through a one-to-many relationship to a bridge table, followed by a many-to-one relationship to the second table, then neither RELATED nor RELATEDTABLE works with single-direction filter propagation. Only RELATEDTABLE can work using bidirectional filter propagation, as explained later. On the other hand, a one-to-one relationship behaves as a one-to-many and as a many-to-one relationship at the same time. Thus, there can be a one-to-one relationship in a chain of one-to-many (or many-to-one) without interrupting the chain.

For example, in the model we chose as a reference, Customer is related to Sales and Sales is related to Product. There is a one-to-many relationship between Customer and Sales, and then a many-to-one relationship between Sales and Product. Thus, a chain of relationships links Customer to Product. However, the two relationships are not in the same direction. This scenario is known as a many-to-many relationship. A customer is related to many products bought and a product is in turn related to many customers who bought that product. We cover many-to-many relationships later in Chapter 15, “Advanced relationships”; let us focus on row context, for the moment. If one uses RELATEDTABLE through a many-to-many relationship, the result would be wrong. Consider a calculated column in Product with this formula:

Click here to view code image

Product[NumOfBuyingCustomers] =
VAR CustomersOfCurrentProduct = RELATEDTABLE ( Customer )
RETURN
    COUNTROWS ( CustomersOfCurrentProduct )

The result of the previous code is not the number of customers who bought that product. Instead, the result is the total number of customers, as shown in Figure 4-19.

RELATEDTABLE cannot follow the chain of relationships because they are not going in the same direction. The row context from Product does not reach Customers. It is worth noting that if we try the formula in the opposite direction, that is, if we count the number of products bought for each customer, the result is correct: a different number for each row representing the number of products bought by the customer. The reason for this behavior is not the propagation of a row context but, rather, the context transition generated by RELATEDTABLE. We added this final note for full disclosure. It is not time to elaborate on this just yet. You will have a better understanding of this after reading Chapter 5.

Filter context and relationships

In the previous section, you learned that the row context iterates and, as such, that it does not use relationships. The filter context, on the other hand, filters. A filter context is not applied to an individual table. Instead, it always works on the whole model. At this point, you can update the evaluation context mantra to its complete formulation:

The filter context filters the model; the row context iterates one table.

Because a filter context filters the model, it uses relationships. The filter context interacts with relationships automatically, and it behaves differently depending on how the cross-filter direction of the relationship is set. The cross-filter direction is represented with a small arrow in the middle of a relationship, as shown in Figure 4-20.

The filter context uses a relationship by going in the direction allowed by the arrow. In all relationships the arrow allows propagation from the one-side to the many-side, whereas when the cross-filter direction is BOTH, propagation is allowed from the many-side to the one-side too.

A relationship with a single cross-filter is a unidirectional relationship, whereas a relationship with BOTH cross-filter directions is a bidirectional relationship.

This behavior is intuitive. Although we have not explained this sooner, all the reports we have used so far relied on this behavior. Indeed, in a typical report filtering by Product[Color] and aggregating the Sales[Quantity], one would expect the filter from Product to propagate to Sales. This is exactly what happens: Product is on the one-side of a relationship; thus a filter on Product propagates to Sales, regardless of the cross-filter direction.

Because our sample data model contains both a bidirectional relationship and many unidirectional relationships, we can demonstrate the filtering behavior by using three different measures that count the number of rows in the three tables: Sales, Product, and Customer.

Click here to view code image

[NumOfSales]     := COUNTROWS ( Sales )
[NumOfProducts]  := COUNTROWS ( Product )
[NumOfCustomers] := COUNTROWS ( Customer )

The report contains the Product[Color] on the rows. Therefore, each cell is evaluated in a filter context that filters the product color. Figure 4-21 shows the result.

In this first example, the filter is always propagating from the one-side to the many-side of relationships. The filter starts from Product[Color]. From there, it reaches Sales, which is on the many-side of the relationship with Product, and Product, because it is the very same table. On the other hand, NumOfCustomers always shows the same value—the total number of customers. This is because the relationship between Customer and Sales does not allow propagation from Sales to Customer. The filter is moved from Product to Sales, but from there it does not reach Customer.

You might have noticed that the relationship between Sales and Product is a bidirectional relationship. Thus, a filter context on Customer also filters Sales and Product. We can prove it by changing the report, slicing by Customer[Education] instead of Product[Color]. The result is visible in Figure 4-22.

This time the filter starts from Customer. It can reach the Sales table because Sales is on the many-side of the relationship. Furthermore, it propagates from Sales to Product because the relationship between Sales and Product is bidirectional—its cross-filter direction is BOTH.

Beware that a single bidirectional relationship in a chain does not make the whole chain bidirectional. In fact, a similar measure that counts the number of subcategories, such as the following one, demonstrates that the filter context starting from Customer does not reach Product Subcategory:

Click here to view code image

NumOfSubcategories := COUNTROWS ( 'Product Subcategory' )

Adding the measure to the previous report produces the results shown in Figure 4-23, where the number of subcategories is the same for all the rows.

Because the relationship between Product and Product Subcategory is unidirectional, the filter does not propagate to Product Subcategory. If we update the relationship, setting the cross-filter direction to BOTH, the result is different as shown in Figure 4-24.

With the row context, we use RELATED and RELATEDTABLE to propagate the row context through relationships. On the other hand, with the filter context, no functions are needed to propagate the filter. The filter context filters the model, not a table. As such, once one applies a filter context, the entire model is subject to the filter according to the relationships.

Creating filter contexts

Here we will introduce the reason why one would want to create new filter contexts with a practical example. As described in the next sections, writing code without being able to create new filter contexts results in verbose and unreadable code. What follows is an example of how creating a new filter context can drastically improve code that, at first, looked rather complex.

Contoso is a company that sells electronic products all around the world. Some products are branded Contoso, whereas others have different brands. One of the reports requires a comparison of the gross margins, both as an amount and as a percentage, of Contoso-branded products against their competitors. The first part of the report requires the following calculations:

Click here to view code image

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Gross Margin := SUMX ( Sales, Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] ) )
GM % := DIVIDE ( [Gross Margin], [Sales Amount] )

One beautiful aspect of DAX is that you can build more complex calculations on top of existing measures. In fact, you can appreciate this in the definition of GM %, the measure that computes the percentage of the gross margin against the sales. GM % simply invokes the two original measures as it divides them. If you already have a measure that computes a value, you can call the measure instead of rewriting the full code.

Using the three measures defined above, one can build the first report, as shown in Figure 5-1.

The next step in building the report is more intricate. In fact, the final report we want is the one in Figure 5-2 that shows two additional columns: the gross margin for Contoso-branded products, both as amount and as percentage.

With the knowledge acquired so far, you are already capable of authoring the code for these two measures. Indeed, because the requirement is to restrict the calculation to only one brand, a solution is to use FILTER to restrict the calculation of the gross margin to Contoso products only:

Click here to view code image

Contoso GM :=
VAR ContosoSales =             -- Saves the rows of Sales which are related
    FILTER (                   -- to Contoso-branded products into a variable
        Sales,
        RELATED ( 'Product'[Brand] ) = "Contoso"
    )
VAR ContosoMargin =            -- Iterates over ContosoSales
    SUMX (                     -- to only compute the margin for Contoso
        ContosoSales,
        Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )
    )
RETURN
    ContosoMargin

The ContosoSales variable contains the rows of Sales related to all the Contoso-branded products. Once the variable is computed, SUMX iterates on ContosoSales to compute the margin. Because the iteration is on the Sales table and the filter is on the Product table, one needs to use RELATED to retrieve the related product for each row in Sales. In a similar way, one can compute the gross margin of Contoso by iterating the ContosoSales variable twice:

Click here to view code image

Contoso GM % :=
VAR ContosoSales =             -- Saves the rows of Sales which are related
    FILTER (                   -- to Contoso-branded products into a variable
        Sales,
        RELATED ( 'Product'[Brand] ) = "Contoso"
    )
VAR ContosoMargin =            -- Iterates over ContosoSales
    SUMX (                     -- to only compute the margin for Contoso
        ContosoSales,
        Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )
    )
VAR ContosoSalesAmount =       -- Iterates over ContosoSales
    SUMX (                     -- to only compute the sales amount for Contoso
        ContosoSales,
        Sales[Quantity] * Sales[Net Price]
    )
VAR Ratio =
    DIVIDE ( ContosoMargin, ContosoSalesAmount )
RETURN
    Ratio

The code for Contoso GM % is a bit longer but, from a logical point of view, it follows the same pattern as Contoso GM. Although these measures work, it is easy to note that the initial elegance of DAX is lost. Indeed, the model already contains one measure to compute the gross margin and another measure to compute the gross margin percentage. However, because the new measures needed to be filtered, we had to rewrite the expression to add the condition.

It is worth stressing that the basic measures Gross Margin and GM % can already compute the values for Contoso. In fact, from Figure 5-2 you can note that the gross margin for Contoso is equal to 3,877,070.65 and the percentage is equal to 52.73%. One can obtain the very same numbers by slicing the base measures Gross Margin and GM % by Brand, as shown in Figure 5-3.

In the highlighted cells, the filter context created by the report is filtering the Contoso brand. The filter context filters the model. Therefore, a filter context placed on the Product[Brand] column filters the Sales table because of the relationship linking Sales to Product. Using the filter context, one can filter a table indirectly because the filter context operates on the whole model.

Thus, if we could make DAX compute the Gross Margin measure by creating a filter context programmatically, which only filters the Contoso-branded products, then our implementation of the last two measures would be much easier. This is possible by using CALCULATE.

The complete description of CALCULATE comes later in this chapter. First, we examine the syntax of CALCULATE:

Click here to view code image

CALCULATE ( Expression, Condition1, ... ConditionN )

CALCULATE can accept any number of parameters. The only mandatory parameter is the first one, that is, the expression to evaluate. The conditions following the first parameter are called filter arguments. CALCULATE creates a new filter context based on the set of filter arguments. Once the new filter context is computed, CALCULATE applies it to the model, and it proceeds with the evaluation of the expression. Thus, by leveraging CALCULATE, the code for Contoso Margin and Contoso GM % becomes much simpler:

Click here to view code image

Contoso GM :=
CALCULATE (
    [Gross Margin],                 -- Computes the gross margin
    'Product'[Brand] = "Contoso"    -- In a filter context where brand = Contoso
)

Contoso GM % :=
CALCULATE (
    [GM %],                         -- Computes the gross margin percentage
    'Product'[Brand] = "Contoso"    -- In a filter context where brand = Contoso
)

Welcome back, simplicity and elegance! By creating a filter context that forces the brand to be Contoso, one can rely on existing measures and change their behavior without having to rewrite the code of the measures.

CALCULATE lets you create new filter contexts by manipulating the filters in the current context. As you have seen, this leads to simple and elegant code. In the next sections we provide a complete and more formal definition of the behavior of CALCULATE, describing in detail what CALCULATE does and how to take advantage of its features. Indeed, so far we have kept the example rather high-level when, in fact, the initial definition of the Contoso measures is not semantically equivalent to the final definition. There are some differences that one needs to understand well.

Introducing CALCULATE

Now that you have had an initial exposure to CALCULATE, it is time to start learning the details of this function. As introduced earlier, CALCULATE is the only DAX function that can modify the filter context; and remember, when we mention CALCULATE, we also include CALCULATETABLE. CALCULATE does not modify a filter context: It creates a new filter context by merging its filter parameters with the existing filter context. Once CALCULATE ends, its filter context is discarded and the previous filter context becomes effective again.

We have introduced the syntax of CALCULATE as

Click here to view code image

CALCULATE ( Expression, Condition1, ... ConditionN )

The first parameter is the expression that CALCULATE will evaluate. Before evaluating the expression, CALCULATE computes the filter arguments and uses them to manipulate the filter context.

The first important thing to note about CALCULATE is that the filter arguments are not Boolean conditions: The filter arguments are tables. Whenever you use a Boolean condition as a filter argument of CALCULATE, DAX translates it into a table of values.

In the previous section we used this code:

Click here to view code image

Contoso GM :=
CALCULATE (
    [Gross Margin],                 -- Computes the gross margin
    'Product'[Brand] = "Contoso"    -- In a filter context where brand = Contoso
)

Using a Boolean condition is only a shortcut for the complete CALCULATE syntax. This is known as syntax sugar. It reads this way:

Click here to view code image

Contoso GM :=
CALCULATE (
    [Gross Margin],                     -- Computes the gross margin
    FILTER (                            -- Using as valid values for Product[Brand]
        ALL ( 'Product'[Brand] ),       -- any value for Product[Brand]
        'Product'[Brand] = "Contoso"    -- which is equal to "Contoso"
    )
)

The two syntaxes are equivalent, and there are no performance or semantic differences between them. That being said, particularly when you are learning CALCULATE for the first time, it is useful to always read filter arguments as tables. This makes the behavior of CALCULATE more apparent. Once you get used to CALCULATE semantics, the compact version of the syntax is more convenient. It is shorter and easier to read.

A filter argument is a table, that is, a list of values. The table provided as a filter argument defines the list of values that will be visible—for the column—during the evaluation of the expression. In the previous example, FILTER returns a table with one row only, containing a value for Product[Brand] that equals “Contoso”. In other words, “Contoso” is the only value that CALCULATE will make visible for the Product[Brand] column. Therefore, CALCULATE filters the model including only products of the Contoso brand. Consider these two definitions:

Click here to view code image

Sales Amount :=
    SUMX (
        Sales,
        Sales[Quantity] * Sales[Net Price]
    )

Contoso Sales :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Product'[Brand] ),
        'Product'[Brand] = "Contoso"
    )
)

The filter parameter of FILTER in the CALCULATE of Contoso Sales scans ALL(Product[Brand]); therefore, any previously existing filter on the product brand is overwritten by the new filter. This is more evident when you use the measures in a report that slices by brand. You can see in Figure 5-4 that Contoso Sales reports on all the rows/brands the same value as Sales Amount did for Contoso specifically.

In every row, the report creates a filter context containing the relevant brand. For example, in the row for Litware the original filter context created by the report contains a filter that only shows Litware products. Then, CALCULATE evaluates its filter argument, which returns a table containing only Contoso. The newly created filter overwrites the previously existing filter on the same column. You can see a graphic representation of the process in Figure 5-5.

CALCULATE does not overwrite the whole original filter context. It only replaces previously existing filters on the columns contained in the filter argument. In fact, if one changes the report to now slice by Product[Category], the result is different, as shown in Figure 5-6.

Now the report is filtering Product[Category], whereas CALCULATE applies a filter on Product[Brand] to evaluate the Contoso Sales measure. The two filters do not work on the same column of the Product table. Therefore, no overwriting happens, and the two filters work together as a new filter context. As a result, each cell is showing the sales of Contoso for the given category. The scenario is depicted in Figure 5-7.

Now that you have seen the basics of CALCULATE, we can summarize its semantics:

• CALCULATE makes a copy of the current filter context.

• CALCULATE evaluates each filter argument and produces, for each condition, the list of valid values for the specified columns.

• If two or more filter arguments affect the same column, they are merged together using an AND operator (or using the set intersection in mathematical terms).

• CALCULATE uses the new condition to replace existing filters on the columns in the model. If a column already has a filter, then the new filter replaces the existing one. On the other hand, if the column does not have a filter, then CALCULATE adds the new filter to the filter context.

• Once the new filter context is ready, CALCULATE applies the filter context to the model, and it computes the first argument: the expression. In the end, CALCULATE restores the original filter context, returning the computed result.

CALCULATE accepts filters of two types:

• Lists of values, in the form of a table expression. In that case, you provide the exact list of values you want to make visible in the new filter context. The filter can be a table with any number of columns. Only the existing combinations of values in different columns will be considered in the filter.

• Boolean conditions, such as Product[Color] = “White”. These filters need to work on a single column because the result needs to be a list of values for a single column. This type of filter argument is also known as predicate.

If you use the syntax with a Boolean condition, DAX transforms it into a list of values. Thus, whenever you write this code:

Click here to view code image

Sales Amount Red Products :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red"
)

DAX transforms the expression into this:

Click here to view code image

Sales Amount Red Products :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Product'[Color] ),
        'Product'[Color] = "Red"
    )
)

For this reason, you can only reference one column in a filter argument with a Boolean condition. DAX needs to detect the column to iterate in the FILTER function, which is generated in the background automatically. If the Boolean expression references two or more columns, then you must explicitly write the FILTER iteration, as you learn later in this chapter.

Using CALCULATE to compute percentages

Now that we have introduced CALCULATE, we can use it to define several calculations. The goal of this section is to bring your attention to some details about CALCULATE that are not obvious at first sight. Later in this chapter, we will cover more advanced aspects of CALCULATE. For now, we focus on some of the issues you might encounter when you start using CALCULATE.

A pattern that appears often is that of percentages. When working with percentages, it is very important to define exactly the calculation required. In this set of examples, you learn how different uses of CALCULATE and ALL functions provide different results.

We can start with a simple percentage calculation. We want to build the following report showing the sales amount along with the percentage over the grand total. You can see in Figure 5-8 the result we want to obtain.

To compute the percentage, one needs to divide the value of Sales Amount in the current filter context by the value of Sales Amount in a filter context that ignores the existing filter on Category. In fact, the value of 1.26% for Audio is computed as 384,518.16 divided by 30,591,343.98.

In each row of the report, the filter context already contains the current category. Thus, for Sales Amount, the result is automatically filtered by the given category. The denominator of the ratio needs to ignore the current filter context, so that it evaluates the grand total. Because the filter arguments of CALCULATE are tables, it is enough to provide a table function that ignores the current filter context on the category and always returns all the categories—regardless of any filter. You previously learned that this function is ALL. Look at the following measure definition:

Click here to view code image

All Category Sales :=
CALCULATE (                         -- Changes the filter context of
    [Sales Amount],                 -- the sales amount
    ALL ( 'Product'[Category] )     -- making ALL categories visible
)

ALL removes the filter on the Product[Category] column from the filter context. Thus, in any cell of the report, it ignores any filter existing on the categories. The effect is that the filter on the category applied by the row of the report is removed. Look at the result in Figure 5-9. You can see that each row of the report for the All Category Sales measure returns the same value all the way through—the grand total of Sales Amount.

The All Category Sales measure is not useful by itself. It is unlikely a user would want to create a report that shows the same value on all the rows. However, that value is perfect as the denominator of the percentage we are looking to compute. In fact, the formula computing the percentage can be written this way:

Click here to view code image

Sales Pct :=
VAR CurrentCategorySales =                    -- CurrentCategorySales contains
    [Sales Amount]                            -- the sales in the current context
VAR AllCategoriesSales =                      -- AllCategoriesSales contains
    CALCULATE (                               -- the sales amount in a filter context
        [Sales Amount],                       -- where all the product categories
        ALL ( 'Product'[Category] )           -- are visible
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllCategoriesSales
    )
RETURN
    Ratio

As you have seen in this example, mixing table functions and CALCULATE makes it possible to author useful measures easily. We use this technique a lot in the book because it is the primary calculation tool in DAX.

As we said in the introduction of this section, it is important to pay attention to small details when authoring percentages like the one we are currently writing. In fact, the percentage works fine if the report is slicing by category. The code removes the filter from the category, but it does not touch any other existing filter. Therefore, if the report adds other filters, the result might not be exactly what one wants to achieve. For example, look at the report in Figure 5-10 where we added the Product[Color] column as a second level of detail in the rows of the report.

Looking at percentages, the value at the category level is correct, whereas the value at the color level looks wrong. In fact, the color percentages do not add up—neither to the category level nor to 100%. To understand the meaning of these values and how they are evaluated, it is always of great help to focus on one cell and understand exactly what happened to the filter context. Focus on Figure 5-11.

The original filter context created by the report contained both a filter on category and a filter on color. The filter on Product[Color] is not overwritten by CALCULATE, which only removes the filter from Product[Category]. As a result, the final filter context only contains the color. Therefore, the denominator of the ratio contains the sales of all the products of the given color—Black—and of any category.

The calculation being wrong is not an unexpected behavior of CALCULATE. The problem here is that the formula has been designed to specifically work with a filter on a category, leaving any other filter untouched. The same formula makes perfect sense in a different report. Look at what happens if one switches the order of the columns, building a report that slices by color first and category second, as in Figure 5-12.

The report in Figure 5-12 makes a lot more sense. The measure computes the same result, but it is more intuitive thanks to the layout of the report. The percentage shown is the percentage of the category inside the given color. Color by color, the percentage always adds up to 100%.

In other words, when the user is required to compute a percentage, they should pay special attention in determining the denominator of the percentage. CALCULATE and ALL are the primary tools to use, but the specification of the formula depends on the business requirements.

Back to the example: The goal is to fix the calculation so that it computes the percentage against a filter on either the category or the color. There are multiple ways of performing the operation, all leading to slightly different results that are worth examining deeper.

One possible solution is to let CALCULATE remove the filter from both the category and the color. Adding multiple filter arguments to CALCULATE accomplishes this goal:

Click here to view code image

Sales Pct :=
VAR CurrentCategorySales =
    [Sales Amount]
VAR AllCategoriesAndColorSales =
    CALCULATE (
        [Sales Amount],
        ALL ( 'Product'[Category] ), -- The two ALL conditions could also be replaced
        ALL ( 'Product'[Color] )     -- by ALL ( 'Product'[Category], 'Product'[Color] )
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllCategoriesAndColorSales
    )
RETURN
    Ratio

This latter version of Sales Pct works fine with the report containing the color and the category, but it still suffers from limitations similar to the previous versions. In fact, it produces the right percentage with color and category—as you can see in Figure 5-13—but it will fail as soon as one adds other columns to the report.

Adding another column to the report would create the same inconsistency noticed so far. If the user wants to create a percentage that removes all the filters on the Product table, they could still use the ALL function passing a whole table as an argument:

Click here to view code image

Sales Pct All Products :=
VAR CurrentCategorySales =
    [Sales Amount]
VAR AllProductSales =
    CALCULATE (
        [Sales Amount],
        ALL ( 'Product' )
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllProductSales
    )
RETURN
    Ratio

ALL on the Product table removes any filter on any column of the Product table. In Figure 5-14 you can see the result of that calculation.

So far, you have seen that by using CALCULATE and ALL together, you can remove filters—from a column, from multiple columns, or from a whole table. The real power of CALCULATE is that it offers many options to manipulate a filter context, and its capabilities do not end there. In fact, one might want to analyze the percentages by also slicing columns from different tables. For example, if the report is sliced by product category and customer continent, the last measure we created is not perfect yet, as you can see in Figure 5-15.

At this point, the problem might be evident to you. The measure at the denominator removes any filter from the Product table, but it leaves the filter on Customer[Continent] intact. Therefore, the denominator computes the total sales of all products in the given continent.

As in the previous scenario, the filter can be removed from multiple tables by putting several filters as arguments of CALCULATE:

Click here to view code image

Sales Pct All Products and Customers :=
VAR CurrentCategorySales =
    [Sales Amount]
VAR AllProductAndCustomersSales =
    CALCULATE (
        [Sales Amount],
        ALL ( 'Product' ),
        ALL ( Customer )
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllProductAndCustomersSales
    )
RETURN
    Ratio

By using ALL on two tables, now CALCULATE removes the filters from both tables. The result, as expected, is a percentage that adds up correctly, as you can appreciate in Figure 5-16.

As with two columns, the same challenge comes up with two tables. If a user adds another column from a third table to the context, the measure will not remove the filter from the third table. One possible solution when they want to remove the filter from any table that might affect the calculation is to remove any filter from the fact table itself. In our model the fact table is Sales. Here is a measure that computes an additive percentage no matter what filter is interacting with the Sales table:

Click here to view code image

Pct All Sales :=
VAR CurrentCategorySales =
    [Sales Amount]
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALL ( Sales )
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllSales
    )
RETURN
    Ratio

This measure leverages relationships to remove the filter from any table that might filter Sales. At this stage, we cannot explain the details of how it works because it leverages expanded tables, which we introduce in Chapter 14, “Advanced DAX concepts.” You can appreciate its behavior by inspecting Figure 5-17, where we removed the amount from the report and added the calendar year on the columns. Please note that the Calendar Year belongs to the Date table, which is not used in the measure. Nevertheless, the filter on Date is removed as part of the removal of filters from Sales.

Before leaving this long exercise with percentages, we want to show another final example of filter context manipulation. As you can see in Figure 5-17, the percentage is always against the grand total, exactly as expected. What if the goal is to compute a percentage over the grand total of only the current year? In that case, the new filter context created by CALCULATE needs to be prepared carefully. Indeed, the denominator needs to compute the total of sales regardless of any filter apart from the current year. This requires two actions:

• Removing all filters from the fact table

• Restoring the filter for the year

Beware that the two conditions are applied at the same time, although it might look like the two steps come one after the other. You have already learned how to remove all the filters from the fact table. The last step is learning how to restore an existing filter.

In Chapter 3, “Using basic table functions,” you learned the VALUES function. VALUES returns the list of values of a column in the current filter context. Because the result of VALUES is a table, it can be used as a filter argument for CALCULATE. As a result, CALCULATE applies a filter on the given column, restricting its values to those returned by VALUES. Look at the following code:

Click here to view code image

Pct All Sales CY :=
VAR CurrentCategorySales =
    [Sales Amount]
VAR AllSalesInCurrentYear =
    CALCULATE (
        [Sales Amount],
        ALL ( Sales ),
        VALUES ( 'Date'[Calendar Year] )
    )
VAR Ratio =
    DIVIDE (
        CurrentCategorySales,
        AllSalesInCurrentYear
    )
RETURN
    Ratio

Once used in the report the measure accounts for 100% for every year, still computing the percentage against any other filter apart from the year. You see this in Figure 5-18.

Figure 5-19 depicts the full behavior of this complex formula.

Here is a review of the diagram:

• The cell containing 4.22% (sales of Cell Phones for Calendar Year 2007) has a filter context that filters Cell phones for CY 2007.

• CALCULATE has two filter arguments: ALL ( Sales ) and VALUES ( Date[Calendar Year] ).

  • ALL ( Sales ) removes the filter from the Sales table.

  • VALUES ( Date[Calendar Year] ) evaluates the VALUES function in the original filter context, still affected by the presence of CY 2007 on the columns. As such, it returns the only year visible in the current filter context—that is, CY 2007.

The two filter arguments of CALCULATE are applied to the current filter context, resulting in a filter context that only contains a filter on Calendar Year. The denominator computes the total sales in a filter context with CY 2007 only.

It is of paramount importance to understand clearly that the filter arguments of CALCULATE are evaluated in the original filter context where CALCULATE is called. In fact, CALCULATE changes the filter context, but this only happens after the filter arguments are evaluated.

Using ALL over a table followed by VALUES over a column is a technique used to replace the filter context with a filter over that same column.

As you have seen in these examples, CALCULATE, in itself, is not a complex function. Its behavior is simple to describe. At the same time, as soon as you start using CALCULATE, the complexity of the code becomes much higher. Indeed, you need to focus on the filter context and understand exactly how CALCULATE generates the new filter context. A simple percentage hides a lot of complexity, and that complexity is all in the details. Before one really masters the handling of evaluation contexts, DAX is a bit of a mystery. The key to unlocking the full power of the language is all in mastering evaluation contexts. Moreover, in all these examples we only had to manage one CALCULATE. In a complex formula, having four or five different contexts in the same code is not unusual because of the presence of many instances of CALCULATE.

It is a good idea to read this whole section about percentages at least twice. In our experience, a second read is much easier and lets you focus on the important aspects of the code. We wanted to show this example to stress the importance of theory, when it comes to CALCULATE. A small change in the code has an important effect on the numbers computed by the formula. After your second read, proceed with the next sections where we focus more on theory than on practical examples.

Introducing KEEPFILTERS

You learned in the previous sections that the filter arguments of CALCULATE overwrite any previously existing filter on the same column. Thus, the following measure returns the sales of Audio regardless of any previously existing filter on Product[Category]:

Click here to view code image

Audio Sales :=
CALCULATE (
    [Sales Amount],
    'Product'[Category] = "Audio"
)

As you can see in Figure 5-20, the value of Audio is repeated on all the rows of the report.

CALCULATE overwrites the existing filters on the columns where a new filter is applied. All the remaining columns of the filter context are left intact. In case you do not want to overwrite existing filters, you can wrap the filter argument with KEEPFILTERS. For example, if you want to show the amount of Audio sales when Audio is present in the filter context and a blank value if Audio is not present in the filter context, you can write the following measure:

Click here to view code image

Audio Sales KeepFilters :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Category] = "Audio" )
)

KEEPFILTERS is the second CALCULATE modifier that you learn—the first one was ALL. We further cover CALCULATE modifiers later in this chapter. KEEPFILTERS alters the way CALCULATE applies a filter to the new filter context. Instead of overwriting an existing filter over the same column, it adds the new filter to the existing ones. Therefore, only the cells where the filtered category was already included in the filter context will produce a visible result. You see this in Figure 5-21.

KEEPFILTERS does exactly what its name implies. Instead of overwriting the existing filter, it keeps the existing filter and adds the new filter to the filter context. We can depict the behavior with Figure 5-22.

Because KEEPFILTERS avoids overwriting, the new filter generated by the filter argument of CALCULATE is added to the context. If we look at the cell for the Audio Sales KeepFilters measure in the Cell Phones row, there the resulting filter context contains two filters: one filters Cell Phones; the other filters Audio. The intersection of the two conditions results in an empty set, which produces a blank result.

The behavior of KEEPFILTERS is clearer when there are multiple elements selected in a column. For example, consider the following measures; they filter Audio and Computers with and without KEEPFILTERS:

Click here to view code image

Always Audio-Computers :=
CALCULATE (
    [Sales Amount],
    'Product'[Category] IN { "Audio", "Computers" }
)

KeepFilters Audio-Computers :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Category] IN { "Audio", "Computers" } )
)

The report in Figure 5-23 shows that the version with KEEPFILTERS only computes the sales amount values for Audio and for Computers, leaving all other categories blank. The Total row only takes Audio and Computers into account.

KEEPFILTERS can be used either with a predicate or with a table. Indeed, the previous code could also be written in a more verbose way:

Click here to view code image

KeepFilters Audio-Computers :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( 'Product'[Category] ),
            'Product'[Category] IN { "Audio", "Computers" }
        )
    )
)

This is just an example for educational purposes. You should use the simplest predicate syntax available for a filter argument. When filtering a single column, you can avoid writing the FILTER explicitly. Later however, you will see that more complex filter conditions require an explicit FILTER. In those cases, the KEEPFILTERS modifier can be used around the explicit FILTER function, as you see in the next section.

Filtering a single column

In the previous section, we introduced filter arguments referencing a single column in CALCULATE. It is important to note that you can have multiple references to the same column in one expression. For example, the following is a valid syntax because it references the same column (Sales[Net Price]) twice.

Click here to view code image

Sales 10-100 :=
CALCULATE (
    [Sales Amount],
    Sales[Net Price] >= 10 && Sales[Net Price] <= 100
)

In fact, this is converted into the following syntax:

Click here to view code image

Sales 10-100 :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( Sales[Net Price] ),
        Sales[Net Price] >= 10 && Sales[Net Price] <= 100
    )
)

The resulting filter context produced by CALCULATE only adds one filter over the Sales[Net Price] column. One important note about predicates as filter arguments in CALCULATE is that although they look like conditions, they are tables. If you read the first of the last two code snippets, it looks as though CALCULATE evaluates a condition. Instead, CALCULATE evaluates the list of all the values of Sales[Net Price] that satisfy the condition. Then, CALCULATE uses this table of values to apply a filter to the model.

When two conditions are in a logical AND, they can be represented as two separate filters. Indeed, the previous expression is equivalent to the following one:

Sales 10-100 :=
CALCULATE (
    [Sales Amount],
    Sales[Net Price] >= 10,
    Sales[Net Price] <= 100
)

However, keep in mind that the multiple filter arguments of CALCULATE are always merged with a logical AND. Thus, you must use a single filter in case of a logical OR statement, such as in the following measure:

Click here to view code image

Sales Blue+Red :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red" || 'Product'[Color] = "Blue"
)

By writing multiple filters, you would combine two independent filters in a single filter context. The following measure always produces a blank result because there are no products that are both Blue and Red at the same time:

Sales Blue and Red :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red",
    'Product'[Color] = "Blue"
)

In fact, the previous measure corresponds to the following measure with a single filter:

Click here to view code image

Sales Blue and Red :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red" && 'Product'[Color] = "Blue"
)

The filter argument always returns an empty list of colors allowed in the filter context. Therefore, the measure always returns a blank value.

Whenever a filter argument refers to a single column, you can use a predicate. We suggest you do so because the resulting code is much easier to read. You should do so for logical AND conditions too. Nevertheless, never forget that you are relying on syntax-sugaring only. CALCULATE always works with tables, although the compact syntax might suggest otherwise.

On the other hand, whenever there are two or more different column references in a filter argument, it is necessary to write the FILTER condition as a table expression. You learn this in the following section.

Filtering with complex conditions

A filter argument referencing multiple columns requires an explicit table expression. It is important to understand the different techniques available to write such filters. Remember that creating a filter with the minimum number of columns required by the predicate is usually a best practice.

Consider a measure that sums the sales for only the transactions with an amount greater than or equal to 1,000. Getting the amount of each transaction requires the multiplication of the Quantity and Net Price columns. This is because you do not have a column that stores that amount for each row of the Sales table in the sample Contoso database. You might be tempted to write something like the following expression, which unfortunately will not work:

Click here to view code image

Sales Large Amount :=
CALCULATE (
    [Sales Amount],
    Sales[Quantity] * Sales[Net Price] >= 1000
)

This code is not valid because the filter argument references two different columns in the same expression. As such, it cannot be converted automatically by DAX into a suitable FILTER condition. The best way to write the required filter is by using a table that only has the existing combinations of the columns referenced in the predicate:

Click here to view code image

Sales Large Amount :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( Sales[Quantity], Sales[Net Price] ),
        Sales[Quantity] * Sales[Net Price] >= 1000
    )
)

This results in a filter context that has a filter with two columns and a number of rows that correspond to the unique combinations of Quantity and Net Price that satisfy the filter condition. This is shown in Figure 5-24.

This filter produces the result in Figure 5-25.

Be mindful that the slicer in Figure 5-25 is not filtering any value: The two displayed values are the minimum and the maximum values of Net Price. The next step is showing how the measure is interacting with the slicer. In a measure like Sales Large Amount, you need to pay attention when you overwrite existing filters over Quantity or Net Price. Indeed, because the filter argument uses ALL on the two columns, it ignores any previously existing filter on the same columns including, in this example, the filter of the slicer. The report in Figure 5-26 is the same as Figure 5-25 but, this time, the slicer filters for net prices between 500 and 3,000. The result is surprising.

The presence of value of Sales Large Amount for Audio and Music, Movies and Audio Books is unexpected. Indeed, for these two categories there are no sales in the net price range between 500 and 3,000, which is the filter context generated by the slicer. Still, the Sales Large Amount measure is showing a result.

The reason is that the filter context of Net Price created by the slicer is ignored by the Sales Large Amount measure, which overwrites the existing filter over both Quantity and Net Price. If you carefully compare figures 5-25 and 5-26, you will notice that the value of Sales Large Amount is identical, as if the slicer was not added to the report. Indeed, Sales Large Amount is completely ignoring the slicer.

If you focus on a cell, like the value of Sales Large Amount for Audio, the code executed to compute its value is the following:

Click here to view code image

Sales Large Amount :=
CALCULATE (
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALL ( Sales[Quantity], Sales[Net Price] ),
            Sales[Quantity] * Sales[Net Price] >= 1000
        )
    ),
    'Product'[Category] = "Audio",
    Sales[Net Price] >= 500
)

From the code, you can see that the innermost ALL ignores the filter on Sales[Net Price] set by the outer CALCULATE. In that scenario, you can use KEEPFILTERS to avoid the overwrite of existing filters:

Click here to view code image

Sales Large Amount KeepFilter :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS (
        FILTER (
            ALL ( Sales[Quantity], Sales[Net Price] ),
            Sales[Quantity] * Sales[Net Price] >= 1000
        )
    )
)

The new Sales Large Amount KeepFilter measure produces the result shown in Figure 5-27.

Another way of specifying a complex filter is by using a table filter instead of a column filter. This is one of the preferred techniques of DAX newbies, although it is very dangerous to use. In fact, the previous measure can be written using a table filter:

Click here to view code image

Sales Large Amount Table :=
CALCULATE (
    [Sales Amount],
    FILTER (
        Sales,
        Sales[Quantity] * Sales[Net Price] >= 1000
    )
)

As you may remember, all the filter arguments of CALCULATE are evaluated in the filter context that exists outside of the CALCULATE itself. Thus, the iteration over Sales only considers the rows filtered in the existing filter context, which contains a filter on Net Price. Therefore, the semantic of the Sales Large Amount Table measure corresponds to the Sales Large Amount KeepFilter measure.

Although this technique looks easy, you should be careful in using it because it could have serious consequences on performance and on result accuracy. We will cover the details of these issues in Chapter 14. For now, just remember that the best practice is to always use a filter with the smallest possible number of columns.

Moreover, you should avoid table filters because they usually are more expensive. The Sales table might be very large, and scanning it row by row to evaluate a predicate can be a time-consuming operation. The filter in Sales Large Amount KeepFilter, on the other hand, only iterates the number of unique combinations of Quantity and Net Price. That number is usually much smaller than the number of rows of the entire Sales table.

Evaluation order in CALCULATE

Whenever you look at DAX code, the natural order of evaluation is innermost first. For example, look at the following expression:

Click here to view code image

Sales Amount Large :=
SUMX (
    FILTER ( Sales, Sales[Quantity] >= 100 ),
    Sales[Quantity] * Sales[Net Price]
)

DAX needs to evaluate the result of FILTER before starting the evaluation of SUMX. In fact, SUMX iterates a table. Because that table is the result of FILTER, SUMX cannot start executing before FILTER has finished its job. This rule is true for all DAX functions, except for CALCULATE and CALCULATETABLE. Indeed, CALCULATE evaluates its filter arguments first and only at the end does it evaluate the first parameter, which is the expression to evaluate to provide the CALCULATE result.

Moreover, things are a bit more intricate because CALCULATE changes the filter context. All the filter arguments are executed in the filter context outside of CALCULATE, and each filter is evaluated independently. The order of filters within the same CALCULATE does not matter. Consequently, all the following measures are completely equivalent:

Click here to view code image

Sales Red Contoso :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red",
    KEEPFILTERS ( 'Product'[Brand] = "Contoso" )
)

Sales Red Contoso :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Brand] = "Contoso" ),
    'Product'[Color] = "Red"
)

Sales Red Contoso :=
VAR ColorRed =
        FILTER (
            ALL ( 'Product'[Color] ),
            'Product'[Color] = "Red"
        )
VAR BrandContoso =
        FILTER (
            ALL ( 'Product'[Brand] ),
            'Product'[Brand] = "Contoso"
        )
VAR SalesRedContoso =
    CALCULATE (
        [Sales Amount],
        ColorRed,
        KEEPFILTERS ( BrandContoso )
    )
RETURN
    SalesRedContoso

The version of Sales Red Contoso defined using variables is more verbose than the other versions, but you might want to use it in case the filters are complex expressions with explicit filters. This way, it is easier to understand that the filter is evaluated “before” CALCULATE.

This rule becomes more important in case of nested CALCULATE statements. In fact, the outermost filters are applied first, and the innermost are applied later. Understanding the behavior of nested CALCULATE statements is important, because you encounter this situation every time you nest measures calls. For example, consider the following measures, where Sales Green calls Sales Red:

Click here to view code image

Sales Red :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red"
)

Green calling Red :=
CALCULATE (
    [Sales Red],
    'Product'[Color] = "Green"
)

To make the nested measure call more evident, we can expand Sales Green this way:

Click here to view code image

Green calling Red Exp :=
CALCULATE (
    CALCULATE (
        [Sales Amount],
        'Product'[Color] = "Red"
    ),
    'Product'[Color] = "Green"
)

The order of evaluation is the following:

1. First, the outer CALCULATE applies the filter, Product[Color] = “Green”.

2. Second, the inner CALCULATE applies the filter, Product[Color] = “Red”. This filter overwrites the previous filter.

3. Last, DAX computes [Sales Amount] with a filter for Product[Color] = “Red”.

Therefore, the result of both Red and Green calling Red is still Red, as shown in Figure 5-28.

We can review the order of the evaluation and how the filter context is evaluated with another example. Consider the following measure:

Click here to view code image

Sales YB :=
CALCULATE (
    CALCULATE (
        [Sales Amount],
        'Product'[Color] IN { "Yellow", "Black" }
    ),
    'Product'[Color] IN { "Black", "Blue" }
)

The evaluation of the filter context produced by Sales YB is visible in Figure 5-29.

As seen before, the innermost filter over Product[Color] overwrites the outermost filters. Therefore, the result of the measure shows the sum of products that are Yellow or Black. By using KEEPFILTERS in the innermost CALCULATE, the filter context is built by keeping the two filters instead of overwriting the existing filter:

Click here to view code image

Sales YB KeepFilters :=
CALCULATE (
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS ( 'Product'[Color] IN { "Yellow", "Black" } )
    ),
    'Product'[Color] IN { "Black", "Blue" }
)

The evaluation of the filter context produced by Sales YB KeepFilters is visible in Figure 5-30.

Because the two filters are kept together, they are intersected. Therefore, in the new filter context the only visible color is Black because it is the only value present in both filters.

However, the order of the filter arguments within the same CALCULATE is irrelevant because they are applied to the filter context independently.

Row context and filter context recap

We can recap some important facts about row context and filter context with the aid of Figure 5-31, which shows a report with the Brand on the rows and a diagram describing the evaluation process. Products and Sales in the diagram are not displaying real data. They only contain a few rows to make the points clearer.

The following comments on Figure 5-31 are helpful to monitor your understanding of the whole process for evaluating the Sales Amount measure for the Contoso row:

• The report creates a filter context containing a filter for Product[Brand] = “Contoso”.

• The filter works on the entire model, filtering both the Product and the Sales tables.

• The filter context reduces the number of rows iterated by SUMX while scanning Sales. SUMX only iterates the Sales rows that are related to a Contoso product.

• In the figure there are two rows in Sales with product A, which is branded Contoso.

• Consequently, SUMX iterates two rows. In the first row it computes 1*11.00 with a partial result of 11.00. In the second row it computes 2*10.99 with a partial result of 21.98.

• SUMX returns the sum of the partial results gathered during the iteration.

• During the iteration of Sales, SUMX only scans the visible portion of the Sales table, generating a row context for each visible row.

• When SUMX iterates the first row, Sales[Quantity] equals 1, whereas Sales[Net Price] equals 11. On the second row, the values are different. Columns have a current value that depends on the iterated row. Potentially, each row iterated has a different value for all the columns.

• During the iteration, there is a row context and a filter context. The filter context is still the same that filters Contoso because no CALCULATE has been executed to modify it.

Speaking about context transition, the last statement is the most important. During the iteration the filter context is still active, and it filters Contoso. The row context, on the other hand, is currently iterating the Sales table. Each column of Sales has a given value. The row context is providing the value via the current row. Remember that the row context iterates; the filter context does not.

This is an important detail. We invite you to double-check your understanding in the following scenario. Imagine you create a measure that simply counts the number of rows in the Sales table, with the following code:

Click here to view code image

NumOfSales := COUNTROWS ( Sales )

Once used in the report, the measure counts the number of Sales rows that are visible in the current filter context. The result shown in Figure 5-32 is as expected: a different number for each brand.

Because there are 37,984 rows in Sales for the Contoso brand, this means that an iteration over Sales for Contoso will iterate exactly 37,984 rows. The Sales Amount measure we used so far would complete its execution after 37,984 multiplications.

With the understanding you have obtained so far, can you guess the result of the following measure on the Contoso row?

Click here to view code image

Sum Num Of Sales := SUMX ( Sales, COUNTROWS ( Sales ) )

Do not rush in deciding your answer. Take your time, study this simple code carefully, and make an educated guess. In the following paragraph we provide the correct answer.

The filter context is filtering Contoso. From the previous examples, it is understood that SUMX iterates 37,984 times. For each of these 37,984 rows, SUMX computes the number of rows visible in Sales in the current filter context. The filter context is still the same, so for each row the result of COUNTROWS is always 37,984. Consequently, SUMX sums the value of 37,984 for 37,984 times. The result is 37,984 squared. You can confirm this by looking at Figure 5-33, where the measure is displayed in the report.

Now that we have refreshed the main ideas about row context and filter context, we can further discuss the impact of context transition.

Introducing context transition

A row context exists whenever an iteration is happening on a table. Inside an iteration are expressions that depend on the row context itself. The following expression, which you have studied multiple times by now, comes in handy:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    Sales[Quantity] * Sales[Unit Price]
)

The two columns Quantity and Unit Price have a value in the current row context. In the previous section we showed that if the expression used inside an iteration is not strictly bound to the row context, then it is evaluated in the filter context. As such the results are surprising, at least for beginners. Nevertheless, one is completely free to use any function inside a row context. Among the many functions available, one appears to be more special: CALCULATE.

If executed in a row context, CALCULATE invalidates the row context before evaluating its expression. Inside the expression evaluated by CALCULATE, all the previous row contexts will no longer be valid. Thus, the following code produces a syntax error:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    CALCULATE ( Sales[Quantity] )   -- No row context inside CALCULATE, ERROR !
)

The reason is that the value of the Sales[Quantity] column cannot be retrieved inside CALCULATE because CALCULATE invalidates the row context that exists outside of CALCULATE itself. Nevertheless, this is only part of what context transition performs. The second—and most relevant—operation is that CALCULATE adds as filter arguments all the columns of the current row context with their current value. For example, look at the following code:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    CALCULATE ( SUM ( Sales[Quantity] ) ) -- SUM does not require a row context
)

There are no filter arguments in CALCULATE. The only CALCULATE argument is the expression to evaluate. Thus, it looks like CALCULATE will not overwrite the existing filter context. The point is that CALCULATE, because of context transition, is silently creating many filter arguments. It creates a filter for each column in the iterated table. You can use Figure 5-34 to obtain a first look at the behavior of context transition. We used a reduced set of columns for visual purposes.

During the iteration CALCULATE starts on the first row, and it computes SUM ( Sales[Quantity] ). Even though there are no filter arguments, CALCULATE adds one filter argument for each of the columns of the iterated table. Namely, there are three columns in the example: Product, Quantity, and Net Price. As a result, the filter context generated by the context transition contains the current value (A, 1, 11.00) for each of the columns (Product, Quantity, Net Price). The process, of course, continues for each one of the three rows during the iteration made by SUMX.

In other words, the execution of the previous SUMX results in these three CALCULATE executions:

Click here to view code image

CALCULATE (
    SUM ( Sales[Quantity] ),
    Sales[Product] = "A",
    Sales[Quantity] = 1,
    Sales[Net Price] = 11
) +
CALCULATE (
    SUM ( Sales[Quantity] ),
    Sales[Product] = "B",
    Sales[Quantity] = 2,
    Sales[Net Price] = 25
) +
CALCULATE (
    SUM ( Sales[Quantity] ),
    Sales[Product] = "A",
    Sales[Quantity] = 2,
    Sales[Net Price] = 10.99
)

These filter arguments are hidden. They are added by the engine automatically, and there is no way to avoid them. In the beginning, context transition seems very strange. Nevertheless, once one gets used to context transition, it is an extremely powerful feature. Hard to master, but extremely powerful.

We summarize the considerations presented earlier, before we further discuss a few of them specifically:

• Context transition is expensive. If context transition is used during an iteration on a table with 10 columns and one million rows, then CALCULATE needs to apply 10 filters, one million times. No matter what, it will be a slow operation. This is not to say that relying on context transition should be avoided. However, it does make CALCULATE a feature that needs to be used carefully.

• Context transition does not only filter one row. The original row context existing outside of CALCULATE always only points to one row. The row context iterates on a row-by-row basis. When the row context is moved to a filter context through context transition, the newly created filter context filters all the rows with the same set of values. Thus, you should not assume that the context transition creates a filter context with one row only. This is very important, and we will return to this topic in the next sections.

• Context transition uses columns that are not present in the formula. Although the columns used in the filter are hidden, they are part of the expression. This makes any formula with CALCULATE much more complex than it first seems. If a context transition is used, then all the columns of the table are part of the expression as hidden filter arguments. This behavior might create unexpected dependencies. This topic is also described later in this section.

• Context transition creates a filter context out of a row context. You might remember the evaluation context mantra, “the row context iterates a table, whereas the filter context filters the model.” Once context transition transforms a row context into a filter context, it changes the nature of the filter. Instead of iterating a single row, DAX filters the whole model; relationships become part of the equation. In other words, context transition happening on one table might propagate its filtering effects far from the table the row context originated from.

• Context transition is invoked whenever there is a row context. For example, if one uses CALCULATE in a calculated column, context transition occurs. There is an automatic row context inside a calculated column, and this is enough for context transition to occur.

• Context transition transforms all the row contexts. When nested iterations are being performed on multiple tables, context transition considers all the row contexts. It invalidates all of them and adds filter arguments for all the columns that are currently being iterated by all the active row contexts.

• Context transition invalidates the row contexts. Though we have repeated this concept multiple times, it is worth bringing to your attention again. None of the outer row contexts are valid inside the expression evaluated by CALCULATE. All the outer row contexts are transformed into equivalent filter contexts.

As anticipated earlier in this section, most of these considerations require further explanation. In the remaining part of this section about context transition, we provide a deeper analysis of these main points. Although all these considerations are shown as warnings, in reality they are important features. Being ignorant of certain behaviors can ensure surprising results. Nevertheless, once you master the behavior, you start leveraging it as you see fit. The only difference between a strange behavior and a useful feature—at least in DAX—is your level of knowledge.

Context transition in calculated columns

A calculated column is evaluated in a row context. Therefore, using CALCULATE in a calculated column triggers a context transition. We use this feature to create a calculated column in Product that marks as “High Performance” all the products that—alone—sold more than 1% of the total sales of all the products.

To produce this calculated column, we need two values: the sales of the current product and the total sales of all the products. The former requires filtering the Sales table so that it only computes sales amount for the current product, whereas the latter requires scanning the Sales table with no active filters. Here is the code:

Click here to view code image

'Product'[Performance] =
VAR TotalSales =                               -- Sales of all the products
    SUMX (
        Sales,                                 -- Sales is not filtered
        Sales[Quantity] * Sales[Net Price]     -- thus here we compute all sales
    )
VAR CurrentSales =
    CALCULATE (                                -- Performs context transition
        SUMX (
            Sales,                             -- Sales of the current product only
            Sales[Quantity] * Sales[Net Price] -- thus here we compute sales of the
        )                                      -- current product only
    )
VAR Ratio = 0.01                               -- 1% expressed as a real number
VAR Result =
    IF (
        CurrentSales >= TotalSales * Ratio,
        "High Performance product",
        "Regular product"
    )
RETURN
    Result

You note that there is only one difference between the two variables: TotalSales is executed as a regular iteration, whereas CurrentSales computes the same DAX code within a CALCULATE function. Because this is a calculated column, the row context is transformed into a filter context. The filter context propagates through the model and it reaches Sales, only filtering the sales of the current product.

Thus, even though the two variables look similar, their content is completely different. TotalSales computes the sales of all the products because the filter context in a calculated column is empty and does not filter anything. CurrentSales computes the sales of the current product only thanks to the context transition performed by CALCULATE.

The remaining part of the code is a simple IF statement that checks whether the condition is met and marks the product appropriately. One can use the resulting calculated column in a report like the one visible in Figure 5-35.

In the code of the Performance calculated column, we used CALCULATE and context transition as a feature. Before moving on, we must check that we considered all the implications. The Product table is small, containing just a few thousand rows. Thus, performance is not an issue. The filter context generated by CALCULATE filters all the columns. Do we have a guarantee that CurrentSales only contains the sales of the current product? In this special case, the answer is yes. The reason is that each row of Product is unique because Product contains a column with a different value for each row—ProductKey. Consequently, the filter context generated by the context transition is guaranteed to only filter one product.

In this case, we could rely on context transition because each row of the iterated table is unique. Beware that this is not always true. We want to demonstrate that with an example that is purposely wrong. We create a calculated column, in Sales, containing this code:

Click here to view code image

Sales[Wrong Amt] =
CALCULATE (
    SUMX (
        Sales,
        Sales[Quantity] * Sales[Net Price]
    )
)

Being a calculated column, it runs in a row context. CALCULATE performs the context transition, so SUMX iterates all the rows in Sales with an identical set of values corresponding to the current row in Sales. The problem is that the Sales table does not have any column with unique values. Therefore, there is a chance that multiple identical rows exist and, if they exist, they will be filtered together. In other words, there is no guarantee that SUMX always iterates only one row in the Wrong Amt column.

If you are lucky, there are many duplicated rows, and the value computed by this calculated column is totally wrong. This way, the problem would be clearly visible and immediately recognized. In many real-world scenarios, the number of duplicated rows in tables is tiny, making these inaccurate calculations hard to spot and debug. The sample database we use in this book is no exception. Look at the report in Figure 5-36 showing the correct value for Sales Amount and the wrong value computed by summing the Wrong Amt calculated column.

You can see that the difference only exists at the total level and for the Fabrikam brand. There are some duplicates in the Sales table—related to some Fabrikam product—that perform the calculation twice. The presence of these rows might be legitimate: The same customer bought the same product in the same store on the same day in the morning and in the afternoon, but the Sales table only stores the date and not the time of the transaction. Because the number of duplicates is small, most numbers look correct. However, the calculation is wrong because it depends on the content of the table. Inaccurate numbers might appear at any time because of duplicated rows. The more duplicates there are, the worse the result turns out.

In this case, relying on context transition is the wrong choice. Because the table is not guaranteed to only have unique rows, context transition is not safe to use. An expert DAX coder should know this in advance. Besides, the Sales table might contain millions of rows; thus, this calculated column is not only wrong, it is also very slow.

Context transition with measures

Understanding context transition is very important because of another important aspect of DAX.

Every measure reference always has an implicit CALCULATE surrounding it.

Because of CALCULATE, a measure reference generates an implicit context transition if executed in the presence of any row context. This is why in DAX, it is important to use the correct naming convention when writing column references (always including the table name) and measure references (always without the table name). You want to be aware of any implicit context transition writing and reading a DAX expression.

This simple initial definition deserves a longer explanation with several examples. The first one is that translating a measure reference always requires wrapping the expression of the measure within a CALCULATE function. For example, consider the following definition of the Sales Amount measure and of the Product Sales calculated column in the Product table:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

'Product'[Product Sales] = [Sales Amount]

The Product Sales column correctly computes the sum of Sales Amount only for the current product in the Product table. Indeed, expanding the Sales Amount measure in the definition of Product Sales requires the CALCULATE function that wraps the definition of Sales Amount:

Click here to view code image

'Product'[Product Sales] =
CALCULATE
    SUMX (
        Sales,
        Sales[Quantity] * Sales[Net Price]
    )
)

Without CALCULATE, the result of the calculated column would produce the same value for all the products. This would correspond to the sales amount of all the rows in Sales without any filtering by product. The presence of CALCULATE means that context transition occurs, producing in this case the desired result. A measure reference always calls CALCULATE. This is very important and can be used to write short and powerful DAX expressions. However, it could also lead to big mistakes if you forget that the context transition takes place every time the measure is called in a row context.

As a rule of thumb, you can always replace a measure reference with the expression that defines the measure wrapped inside CALCULATE. Consider the following definition of a measure called Max Daily Sales, which computes the maximum value of Sales Amount computed day by day:

Max Daily Sales :=
MAXX (
    'Date',
    [Sales Amount]
)

This formula is intuitive to read. However, Sales Amount must be computed for each date, only filtering the sales of that day. This is exactly what context transition performs. Internally, DAX replaced the Sales Amount measure reference with its definition wrapped by CALCULATE, as in the following example:

Click here to view code image

Max Daily Sales :=
MAXX (
    'Date',
    CALCULATE (
        SUMX (
            Sales,
            Sales[Quantity] * Sales[Net Price]
        )
    )
)

We will use this feature extensively in Chapter 7, “Working with iterators and CALCULATE,” when we start writing complex DAX code to solve specific scenarios. This initial description just completes the explanation of context transition, which happens in these cases:

• When a CALCULATE or CALCULATETABLE function is called in the presence of any row context.

• When there is a measure reference in the presence of any row context because the measure reference internally executes its DAX code within a CALCULATE function.

This powerful behavior might lead to mistakes, mainly due to the incorrect assumption that you can replace a measure reference with the DAX code of its definition. You cannot. This could work when there are no row contexts, like in a measure, but this is not possible when the measure reference appears within a row context. It is easy to forget this rule, so we provide an example of what could happen by making an incorrect assumption.

You may have noticed that in the previous example, we wrote the code for a calculated column repeating the iteration over Sales twice. Here is the code we already presented in the previous example:

Click here to view code image

'Product'[Performance] =
VAR TotalSales =                               -- Sales of all the products
    SUMX (
        Sales,                                 -- Sales is not filtered
        Sales[Quantity] * Sales[Net Price]     -- thus here we compute all sales
    )
VAR CurrentSales =
    CALCULATE (                                -- Performs the context transition
        SUMX (
            Sales,                             -- Sales of the current product only
            Sales[Quantity] * Sales[Net Price] -- thus here we compute sales of the
        )                                      -- current product only
    )
VAR Ratio = 0.01                               -- 1% expressed as a real number
VAR Result =
    IF (
        CurrentSales >= TotalSales * Ratio,
        "High Performance product",
        "Regular product"
    )
RETURN
    Result

The iteration executed by SUMX is the same code for the two variables: One is surrounded by CALCULATE, whereas the other is not. It might seem like a good idea to rewrite the code and use a measure to host the code of the iteration. This could be even more relevant in case the expression is not a simple SUMX but, rather, some more complex code. Unfortunately, this approach will not work because the measure reference will always include a CALCULATE around the expression that the measure replaced.

Imagine creating a measure, Sales Amount, and then a calculated column that calls the measure surrounding it—once with CALCULATE and once without CALCULATE.

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

'Product'[Performance] =
VAR TotalSales = [Sales Amount]
VAR CurrentSales = CALCULATE ( [Sales Amount] )
VAR Ratio = 0.01
VAR Result =
    IF (
        CurrentSales >= TotalSales * Ratio,
        "High Performance product",
        "Regular product"
    )
RETURN
    Result

Though it looked like a good idea, this calculated column does not compute the expected result. The reason is that both measure references will have their own implicit CALCULATE around them. Thus, TotalSales does not compute the sales of all the products. Instead, it only computes the sales of the current product because the hidden CALCULATE performs a context transition. CurrentSales computes the same value. In CurrentSales, the extra CALCULATE is redundant. Indeed, CALCULATE is already there, only because it is referencing a measure. This is more evident by looking at the code resulting by expanding the Sales Amount measure:

Click here to view code image

'Product'[Performance] =
VAR TotalSales =
CALCULATE (
    SUMX (
        Sales,
        Sales[Quantity] * Sales[Net Price]
    )
)
VAR CurrentSales =
CALCULATE (
    CALCULATE (
        SUMX (
             Sales,
            Sales[Quantity] * Sales[Net Price]
        )
    )
)
VAR Ratio = 0.01
VAR Result =
    IF (
        CurrentSales >= TotalSales * Ratio,
        "High Performance product",
        "Regular product"
    )
RETURN
    Result

Whenever you read a measure call in DAX, you should always read it as if CALCULATE were there. Because it is there. We introduced a rule in Chapter 2, “Introducing DAX,” where we said that it is a best practice to always use the table name in front of columns, and never use the table name in front of measures. The reason is what we are discussing now.

When reading DAX code, it is of paramount importance that the user be immediately able to understand whether the code is referencing a measure or a column. The de facto standard that nearly every DAX coder adopts is to omit the table name in front of measures.

The automatic CALCULATE makes it easy to author formulas that perform complex calculations with iterations. We will use this feature extensively in Chapter 7 when we start writing complex DAX code to solve specific scenarios.

Understanding USERELATIONSHIP

The first CALCULATE modifier you learn is USERELATIONSHIP. CALCULATE can activate a relationship during the evaluation of its expression by using this modifier. A data model might contain both active and inactive relationships. One might have inactive relationships in the model because there are several relationships between two tables, and only one of them can be active.

As an example, one might have order date and delivery date stored in the Sales table for each order. Typically, the requirement is to perform sales analysis based on the order date, but one might need to consider the delivery date for some specific measures. In that scenario, an option is to create two relationships between Sales and Date: one based on Order Date and another one based on Delivery Date. The model looks like the one in Figure 5-37.

Only one of the two relationships can be active at a time. For example, in this demo model the relationship with Order Date is active, whereas the one linked to Delivery Date is kept inactive. To author a measure that shows the delivered value in a given time period, the relationship with Delivery Date needs to be activated for the duration of the calculation. In this scenario, USERELATIONSHIP is of great help as in the following code:

Click here to view code image

Delivered Amount:=
CALCULATE (
    [Sales Amount],
    USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)

The relationship between Delivery Date and Date is activated during the evaluation of Sales Amount. In the meantime, the relationship with Order Date is deactivated. Keep in mind that at a given point in time, only one relationship can be active between any two tables. Thus, USERELATIONSHIP temporarily activates one relationship, deactivating the one active outside of CALCULATE.

Figure 5-38 shows the difference between Sales Amount based on the Order Date, and the new Delivered Amount measure.

When using USERELATIONSHIP to activate a relationship, you need to be aware of an important aspect: Relationships are defined when a table reference is used, not when RELATED or other relational functions are invoked. We will cover the details of this in Chapter 14 by using expanded tables. For now, an example should suffice. To compute all amounts delivered in 2007, the following formula will not work:

Click here to view code image

Delivered Amount 2007 v1 :=
CALCULATE (
    [Sales Amount],
    FILTER (
        Sales,
        CALCULATE (
            RELATED ( 'Date'[Calendar Year] ),
            USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
        ) = "CY 2007"
    )
)

In fact, CALCULATE would inactivate the row context generated by the FILTER iteration. Thus, inside the CALCULATE expression, one cannot use the RELATED function at all. One option to author the code would be the following:

Click here to view code image

Delivered Amount 2007 v2 :=
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        FILTER (
            Sales,
            RELATED ( 'Date'[Calendar Year] ) = "CY 2007"
        ),
        USERELATIONSHIP (
            Sales[Delivery Date],
            'Date'[Date]
        )
    )
)

In this latter formulation, Sales is referenced after CALCULATE has activated the required relationship. Therefore, the use of RELATED inside FILTER happens with the relationship with Delivery Date active. The Delivered Amount 2007 v2 measure works, but a much better formulation of the same measure relies on default filter context propagation rather than relying on RELATED:

Click here to view code image

Delivered Amount 2007 v3 :=
CALCULATE (
    [Sales Amount],
    'Date'[Calendar Year] = "CY 2007",
    USERELATIONSHIP (
        Sales[Delivery Date],
        'Date'[Date]
    )
)

When you use USERELATIONSHIP in a CALCULATE statement, all the filter arguments are evaluated using the relationship modifiers that appear in the same CALCULATE statement—regardless of their order. For example, in the Delivered Amount 2007 v3 measure, the USERELATIONSHIP modifier affects the predicate filtering Calendar Year, although it is the previous parameter within the same CALCULATE function call.

This behavior makes the use of nondefault relationships a complex operation in calculated column expressions. The invocation of the table is implicit in a calculated column definition. Therefore, you do not have control over it, and you cannot change that behavior by using CALCULATE and USERELATIONSHIP.

One important note is the fact that USERELATIONSHIP does not introduce any filter by itself. Indeed, USERELATIONSHIP is not a filter argument. It is a CALCULATE modifier. It only changes the way other filters are applied to the model. If you carefully look at the definition of Delivered Amount in 2007 v3, you might notice that the filter argument applies a filter on the year 2007, but it does not indicate which relationship to use. Is it using Order Date or Delivery Date? The relationship to use is defined by USERELATIONSHIP.

Thus, CALCULATE first modifies the structure of the model by activating the relationship, and only later does it apply the filter argument. If that were not the case—that is, if the filter argument were always evaluated on the current relationship architecture—then the calculation would not work.

There are precedence rules in the application of filter arguments and of CALCULATE modifiers. The first rule is that CALCULATE modifiers are always applied before any filter argument, so that the effect of filter arguments is applied on the modified version of the model. We discuss precedence of CALCULATE arguments in more detail later.

Understanding CROSSFILTER

The next CALCULATE modifier you learn is CROSSFILTER. CROSSFILTER is somewhat similar to USERELATIONSHIP because it manipulates the architecture of the relationships in the model. Nevertheless, CROSSFILTER can perform two different operations:

• It can change the cross-filter direction of a relationship.

• It can disable a relationship.

USERELATIONSHIP lets you activate a relationship while disabling the active relationship, but it cannot disable a relationship without activating another one between the same tables. CROSSFILTER works in a different way. CROSSFILTER accepts two parameters, which are the columns involved in the relationship, and a third parameter that can be either NONE, ONEWAY, or BOTH. For example, the following measure computes the distinct count of product colors after activating the relationship between Sales and Product as a bidirectional one:

Click here to view code image

NumOfColors :=
CALCULATE (
    DISTINCTCOUNT ( 'Product'[Color] ),
    CROSSFILTER ( Sales[ProductKey], 'Product'[ProductKey], BOTH )
)

As is the case with USERELATIONSHIP, CROSSFILTER does not introduce filters by itself. It only changes the structure of the relationships, leaving to other filter arguments the task of applying filters. In the previous example, the effect of the relationship only affects the DISTINCTCOUNT function because CALCULATE has no further filter arguments.

Understanding KEEPFILTERS

We introduced KEEPFILTERS earlier in this chapter as a CALCULATE modifier. Technically, KEEPFILTERS is not a CALCULATE modifier, it is a filter argument modifier. Indeed, it does not change the entire evaluation of CALCULATE. Instead, it changes the way one individual filter argument is applied to the final filter context generated by CALCULATE.

We already discussed in depth the behavior of CALCULATE in the presence of calculations like the following one:

Click here to view code image

Contoso Sales :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Brand] = "Contoso" )
)

The presence of KEEPFILTERS means that the filter on Brand does not overwrite a previously existing filter on the same column. Instead, the new filter is added to the filter context, leaving the previous one intact. KEEPFILTERS is applied to the individual filter argument where it is used, and it does not change the semantic of the whole CALCULATE function.

There is another way to use KEEPFILTERS that is less obvious. One can use KEEPFILTERS as a modifier for the table used for an iteration, like in the following code:

Click here to view code image

ColorBrandSales :=
SUMX (
    KEEPFILTERS ( ALL ( 'Product'[Color], 'Product'[Brand] ) ),
    [Sales Amount]
)

The presence of KEEPFILTERS as the top-level function used in an iteration forces DAX to use KEEPFILTERS on the implicit filter arguments added by CALCULATE during a context transition. In fact, during the iteration over the values of Product[Color] and Product[Brand], SUMX invokes CALCULATE as part of the evaluation of the Sales Amount measure. At that point, the context transition occurs, and the row context becomes a filter context by adding a filter argument for Color and Brand.

Because the iteration started with KEEPFILTERS, context transition will not overwrite existing filters. It will intersect the existing filters instead. It is uncommon to use KEEPFILTERS as the top-level function in an iteration. We will cover some examples of this advanced use later in Chapter 10.

Understanding ALL in CALCULATE

ALL is a table function, as you learned in Chapter 3. Nevertheless, ALL acts as a CALCULATE modifier when used as a filter argument in CALCULATE. The function name is the same, but the semantics of ALL as a CALCULATE modifier is slightly different than what one would expect.

Looking at the following code, one might think that ALL returns all the years, and that it changes the filter context making all years visible:

Click here to view code image

All Years Sales :=
CALCULATE (
    [Sales Amount],
    ALL ( 'Date'[Year] )
)

However, this is not true. When used as a top-level function in a filter argument of CALCULATE, ALL removes an existing filter instead of creating a new one. A proper name for ALL would have been REMOVEFILTER. For historical reasons, the name remained ALL and it is a good idea to know exactly how the function behaves.

If one considers ALL as a table function, they would interpret the CALCULATE behavior like in Figure 5-39.

The innermost ALL over Date[Year] is a top-level ALL function call in CALCULATE. As such, it does not behave as a table function. It should really be read as REMOVEFILTER. In fact, instead of returning all the years, in that case ALL acts as a CALCULATE modifier that removes any filter from its argument. What really happens inside CALCULATE is the diagram of Figure 5-40.

The difference between the two behaviors is subtle. In most calculations, the slight difference in semantics will go unnoticed. Nevertheless, when we start authoring more advanced code, this small difference will make a big impact. For now, the important detail is that when ALL is used as REMOVEFILTER, it acts as a CALCULATE modifier instead of acting as a table function.

This is important because of the order of precedence of filters in CALCULATE. The CALCULATE modifiers are applied to the final filter context before explicit filter arguments. Thus, consider the presence of ALL on a column where KEEPFILTERS is being used on another explicit filter over that column; it produces the same result as a filter applied to that same column without KEEPFILTERS. In other words, the following definitions of the Sales Red measure produce the same result:

Click here to view code image

Sales Red :=
CALCULATE (
    [Sales Amount],
    'Product'[Color] = "Red"
)

Sales Red :=
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Product'[Color] = "Red" ),
    ALL ( 'Product'[Color] )
)

The reason is that ALL is a CALCULATE modifier. Therefore, ALL is applied before KEEPFILTERS. Moreover, the same precedence rule of ALL is shared by other functions with the same ALL prefix: These are ALL, ALLSELECTED, ALLNOBLANKROW, ALLCROSSFILTERED, and ALLEXCEPT. We generally refer to these functions as the ALL* functions. As a rule, ALL* functions are CALCULATE modifiers when used as top-level functions in CALCULATE filter arguments.

Introducing ALL and ALLSELECTED with no parameters

We introduced ALLSELECTED in Chapter 3. We introduced it early on, mainly because of how useful it is. Like all the ALL* functions, ALLSELECTED acts as a CALCULATE modifier when used as a top-level function in CALCULATE. Moreover, when introducing ALLSELECTED, we described it as a table function that can return the values of either a column or a table.

The following code computes a percentage over the total number of colors selected outside of the current visual. The reason is that ALLSELECTED restores the filter context outside of the current visual on the Product[Color] column.

Click here to view code image

SalesPct :=
DIVIDE (
    [Sales],
    CALCULATE (
        [Sales],
        ALLSELECTED ( 'Product'[Color] )
    )
)

One achieves a similar result using ALLSELECTED ( Product ), which executes ALLSELECTED on top of a whole table. Nevertheless, when used as a CALCULATE modifier, both ALL and ALLSELECTED can also work without any parameter.

Thus, the following is a valid syntax:

Click here to view code image

SalesPct :=
DIVIDE (
    [Sales],
    CALCULATE (
        [Sales],
        ALLSELECTED ( )
    )
)

As you can easily notice, in this case ALLSELECTED cannot be a table function. It is a CALCULATE modifier that instructs CALCULATE to restore the filter context that was active outside of the current visual. The way this whole calculation works is rather complex. We will take the behavior of ALL-SELECTED to the next level in Chapter 14. Similarly, ALL with no parameters clears the filter context from all the tables in the model, restoring a filter context with no filters active.

Now that we have completed the overall structure of CALCULATE, we can finally discuss in detail the order of evaluation of all the elements involving CALCULATE.

Understanding iterator cardinality

The first important concept to understand about iterators is the iterator cardinality. The cardinality of an iterator is the number of rows being iterated. For example, in the following iteration if Sales has one million rows, then the cardinality is one million:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,                              -- Sales has 1M rows, as a consequence
    Sales[Quantity] * Sales[Net Price]  -- the expression is evaluated one million times
)

When speaking about cardinality, we seldom use numbers. In fact, the cardinality of the previous example depends on the number of rows of the Sales table. Thus, we prefer to say that the cardinality of the iterator is the same as the cardinality of Sales. The more rows in Sales, the higher the number of iterated rows.

In the presence of nested iterators, the resulting cardinality is a combination of the cardinality of the two iterators—up to the product of the two original tables. For example, consider the following formula:

Click here to view code image

Sales at List Price 1 :=
SUMX (
    'Product',
    SUMX (
        RELATEDTABLE ( Sales ),
        'Product'[Unit Price] * Sales[Quantity]
    )
)

In this example there are two iterators. The outer iterates Product. As such, its cardinality is the cardinality of Product. Then for each product the inner iteration scans the Sales table, limiting its iteration to the rows in Sales that have a relationship with the given product. In this case, because each row in Sales is pertinent to only one product, the full cardinality is the cardinality of Sales. If the inner table expression is not related to the outer table expression, then the cardinality becomes much higher. For example, consider the following code. It computes the same value as the previous code, but instead of relying on relationships, it uses an IF function to filter the sales of the current product:

Click here to view code image

Sales at List Price High Cardinality :=
SUMX (
    VALUES ( 'Product' ),
    SUMX (
        Sales,
        IF (
             Sales[ProductKey] = 'Product'[ProductKey],
             'Product'[Unit Price] * Sales[Quantity],
             0
        )
    )
)

In this example the inner SUMX always iterates over the whole Sales table, relying on the internal IF statement to check whether the product should be considered or not for the calculation. In this case, the outer SUMX has the cardinality of Product, whereas the inner SUMX has the cardinality of Sales. The cardinality of the whole expression is Product times Sales; much higher than the first example. Be mindful that this example is for educational purposes only. It would result in bad performance if one ever used such a pattern in a DAX expression.

A better way to express this code is the following:

Click here to view code image

Sales at List Price 2 :=
SUMX (
    Sales,
    RELATED ( 'Product'[Unit Price] ) * Sales[Quantity]
)

The cardinality of the entire expression is the same as in the Sales at List Price 1 measure, but the latter has a better execution plan. Indeed, it avoids nested iterators. Nested iterations mostly happen because of context transition. In fact, by looking at the following code, one might think that there are no nested iterators:

Click here to view code image

Sales at List Price 3 :=
SUMX (
    'Product',
    'Product'[Unit Price] * [Total Quantity]
)

However, inside the iteration there is a reference to a measure (Total Quantity) which we need to consider. In fact, here is the expanded definition of Total Quantity:

Click here to view code image

Total Quantity :=
SUM ( Sales[Quantity] )    -- Internally translated into SUMX ( Sales, Sales[Quantity] )

Sales at List Price 4 :=
SUMX (
    'Product',
    'Product'[Unit Price] *
        CALCULATE (
            SUMX (
                Sales,
                Sales[Quantity]
            )
        )
)

You can now see that there is a nested iteration—that is, a SUMX inside another SUMX. Moreover, the presence of CALCULATE, which performs a context transition, is also made visible.

From a performance point of view, when there are nested iterators, only the innermost iterator can be optimized with the more efficient query plan. The presence of outer iterators requires the creation of temporary tables in memory. These temporary tables store the intermediate result produced by the innermost iterator. This results in slower performance and higher memory consumption. As a consequence, nested iterators should be avoided if the cardinality of the outer iterators is very large—in the order of several million rows.

Please note that in the presence of context transition, unfolding nested iterations is not as easy as it might seem. In fact, a typical mistake is to obtain nested iterators by writing a measure that is supposed to reuse an existing measure. This could be dangerous when the existing logic of a measure is reused within an iterator. For example, consider the following calculation:

Click here to view code image

Sales at List Price 5 :=
SUMX (
    'Sales',
    RELATED ( 'Product'[Unit Price] ) * [Total Quantity]
)

The Sales at List Price 5 measure seems identical to Sales at List Price 3. Unfortunately, Sales at List Price 5 violates several of the rules of context transition outlined in Chapter 5, “Understanding CALCULATE and CALCULATETABLE”: It performs context transition on a large table (Sales), and worse, it performs context transition on a table where the rows are not guaranteed to be unique. Consequently, the formula is slow and likely to produce incorrect results.

This is not to say that nested iterations are always bad. There are various scenarios where the use of nested iterations is convenient. In fact, in the rest of this chapter we show many examples where nested iterators are a powerful tool to use.

Leveraging context transition in iterators

A calculation might require nested iterators, usually when it needs to compute a measure in different contexts. These are the scenarios where using context transition is powerful and allows for the concise, efficient writing of complex calculations.

For example, consider a measure that computes the maximum daily sales in a time period. The definition of the measure is important because it defines the granularity right away. Indeed, one needs to first compute the daily sales in the given period, then find the maximum value in the list of computed values. Even though it would seem intuitive to create a table containing daily sales and then use MAX on it, in DAX you are not required to build such a table. Instead, iterators are a convenient way of obtaining the desired result without any additional table.

The idea of the algorithm is the following:

• Iterate over the Date table.

• Compute the sales amount for each day.

• Find the maximum of all the values computed in the previous step.

You can write this measure by using the following approach:

Click here to view code image

Max Daily Sales 1 :=
MAXX (
    'Date',
    VAR DailyTransactions =
        RELATEDTABLE ( Sales )
    VAR DailySales =
        SUMX (
            DailyTransactions,
            Sales[Quantity] * Sales[Net Price]
        )
    RETURN
        DailySales
)

However, a simpler approach is the following, which leverages the implicit context transition of the measure Sales Amount:

Click here to view code image

Sales Amount :=
SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
)

Max Daily Sales 2 :=
MAXX (
    'Date',
    [Sales Amount]
)

In both cases there are two nested iterators. The outer iteration happens on the Date table, which is expected to contain a few hundred rows. Moreover, each row in Date is unique. Thus, both calculations are safe and quick. The former version is more complete, as it outlines the full algorithm. On the other hand, the second version of Max Daily Sales hides many details and makes the code more readable, leveraging context transition to move the filter from Date over to Sales.

You can view the result of this measure in Figure 7-1 that shows the maximum daily sales for each month.

By leveraging context transition and an iteration, the code is usually more elegant and intuitive to write. The only issue you should be aware of is the cost involved in context transition: it is a good idea to avoid measure references in large iterators.

By looking at the report in Figure 7-1, a logical question is: When did sales hit their maximum? For example, the report is indicating that in one certain day in January 2007, Contoso sold 92,244.07 USD. But in which day did it happen? Iterators and context transition are powerful tools to answer this question. Look at the following code:

Click here to view code image

Date of Max =
VAR MaxDailySales = [Max Daily Sales]
VAR DatesWithMax =
    FILTER (
        VALUES ( 'Date'[Date] ),
        [Sales Amount] = MaxDailySales
    )
VAR Result =
    IF (
        COUNTROWS ( DatesWithMax ) = 1,
        DatesWithMax,
        BLANK ()
    )
RETURN
    Result

The formula first stores the value of the Max Daily Sales measure into a variable. Then, it creates a temporary table containing the dates where sales equals MaxDailySales. If there is only one date when this happened, then the result is the only row which passed the filter. If there are multiple dates, then the formula blanks its result, showing that a single date cannot be determined. You can look at the result of this code in Figure 7-2.

The use of iterators in DAX requires you to always define, in this order:

• The granularity at which you want the calculation to happen,

• The expression to evaluate at the given granularity,

• The kind of aggregation to use.

In the previous example (Max Daily Sales 2) the granularity is the date, the expression is the amount of sales, and the aggregation to use is MAX. The result is the maximum daily sales.

There are several scenarios where the same pattern can be useful. Another example could be displaying the average customer sales. If you think about it in terms of iterators using the pattern described above, you obtain the following: Granularity is the individual customer, the expression to use is sales amount, and the aggregation is AVERAGE.

Once you follow this mental process, the formula is short and easy:

Click here to view code image

Avg Sales by Customer :=
AVERAGEX ( Customer, [Sales Amount] )

With this simple formula, one can easily build powerful reports like the one in Figure 7-3 that shows the average sales per customer by continent and year.

Context transition in iterators is a powerful tool. It can also be expensive, so always checking the cardinality of the outer iterator is a good practice. This will result in more efficient DAX code.

Using CONCATENATEX

In this section, we show a convenient usage of CONCATENATEX to display the filters applied to a report in a user-friendly way. Suppose you build a simple visual that shows sales sliced by year and continent, and you put it in a more complex report where the user has the option of filtering colors using a slicer. The slicer might be near the visual or it might be in a different page.

If the slicer is in a different page, then looking at the visual, it is not clear whether the numbers displayed are a subset of the whole dataset or not. In that case it would be useful to add a label to the report, showing the selection made by the user in textual form as in Figure 7-4.

One can inspect the values of the selected colors by querying the VALUES function. Nevertheless, CONCATENATEX is required to convert the resulting table into a string. Look at the definition of the Selected Colors measure, which we used to show the colors in Figure 7-4:

Click here to view code image

Selected Colors :=
"Showing " &
CONCATENATEX (
    VALUES ( 'Product'[Color] ),
    'Product'[Color],
    ", ",
    'Product'[Color],
    ASC
) & " colors."

CONCATENATEX iterates over the values of product color and creates a string containing the list of these colors separated by a comma. As you can see, CONCATENATEX accepts multiple parameters. As usual, the first two are the table to scan and the expression to evaluate. The third parameter is the string to use as the separator between expressions. The fourth and the fifth parameters indicate the sort order and its direction (ASC or DESC).

The only drawback of this measure is that if there is no selection on the color, it produces a long list with all the colors. Moreover, in the case where there are more than five colors, the list would be too long anyway and the user experience sub-optimal. Nevertheless, it is easy to fix both problems by making the code slightly more complex to detect these situations:

Click here to view code image

Selected Colors :=
VAR Colors =
    VALUES ( 'Product'[Color] )
VAR NumOfColors =
    COUNTROWS ( Colors )
VAR NumOfAllColors =
    COUNTROWS (
        ALL ( 'Product'[Color] )
    )
VAR AllColorsSelected = NumOfColors = NumOfAllColors
VAR SelectedColors =
    CONCATENATEX (
        Colors,
        'Product'[Color],
        ", ",
        'Product'[Color], ASC
    )
VAR Result =
    IF (
        AllColorsSelected,
        "Showing all colors.",
        IF (
            NumOfColors > 5,
            "More than 5 colors selected, see slicer page for details.",
            "Showing " & SelectedColors & " colors."
        )
    )
RETURN
    Result

In Figure 7-5 you can see two results for the same visual, with different selections for the colors. With this latter version, it is much clearer whether the user needs to look at more details or not about the color selection.

This latter version of the measure is not perfect yet. In the case where the user selects five colors, but only four are present in the current selection because other filters hide some colors, then the measure does not report the complete list of colors. It only reports the existing list. In Chapter 10, “Working with the filter context,” we describe a different version of this measure that addresses this last detail. In fact, to author the final version, we first need to describe a set of new functions that aim at investigating the content of the current filter context.

Iterators returning tables

So far, we have described iterators that aggregate an expression. There are also iterators that return a table produced by merging a source table with one or more expressions evaluated in the row context of the iteration. ADDCOLUMNS and SELECTCOLUMNS are the most interesting and useful. They are the topic of this section.

As its name implies, ADDCOLUMNS adds new columns to the table expression provided as the first parameter. For each added column, ADDCOLUMNS requires knowing the column name and the expression that defines it.

For example, you can add two columns to the list of colors, including for each color the number of products and the value of Sales Amount in two new columns:

Click here to view code image

Colors =
ADDCOLUMNS (
    VALUES ( 'Product'[Color] ),
    "Products", CALCULATE ( COUNTROWS ( 'Product' ) ),
    "Sales Amount", [Sales Amount]
)

The result of this code is a table with three columns: the product color, which is coming from the values of Product[Color], and the two new columns added by ADDCOLUMNS as you can see in Figure 7-6.

ADDCOLUMNS returns all the columns of the table expression it iterates, adding the requested columns. To keep only a subset of the columns of the original table expression, an option is to use SELECTCOLUMNS, which only returns the requested columns. For instance, you can rewrite the previous example of ADDCOLUMNS by using the following query:

Click here to view code image

Colors =
SELECTCOLUMNS (
    VALUES ( 'Product'[Color] ),
    "Color", 'Product'[Color],
    "Products", CALCULATE ( COUNTROWS ( 'Product' ) ),
    "Sales Amount", [Sales Amount]
)

The result is the same, but you need to explicitly include the Color column of the original table to obtain the same result. SELECTCOLUMNS is useful whenever you need to reduce the number of columns of a table, oftentimes resulting from some partial calculations.

ADDCOLUMNS and SELECTCOLUMNS are useful to create new tables, as you have seen in this first example. These functions are also often used when authoring measures to make the code easier and faster. As an example, look at the measure, defined earlier in this chapter, that aims at finding the date with the maximum daily sales:

Click here to view code image

Max Daily Sales :=
MAXX (
    'Date',
    [Sales Amount]
)

Date of Max :=
VAR MaxDailySales = [Max Daily Sales]
VAR DatesWithMax =
    FILTER (
        VALUES ( 'Date'[Date] ),
        [Sales Amount] = MaxDailySales
    )
VAR Result =
    IF (
        COUNTROWS ( DatesWithMax ) = 1,
        DatesWithMax,
        BLANK ()
    )
RETURN
    Result

If you look carefully at the code, you will notice that it is not optimal in terms of performance. In fact, as part of the calculation of the variable MaxDailySales, the engine needs to compute the daily sales to find the maximum value. Then, as part of the second variable evaluation, it needs to compute the daily sales again to find the dates when the maximum sales happened. Thus, the engine performs two iterations on the Date table, and each time it computes the sales amount for each date. The DAX optimizer might be smart enough to understand that it can compute the daily sales only once, and then use the previous result the second time you need it, but this is not guaranteed to happen. Nevertheless, by refactoring the code leveraging ADDCOLUMNS, one can write a faster version of the same measure. This is achieved by first preparing a table with the daily sales and storing it into a variable, then using this first—partial—result to compute both the maximum daily sales and the date with the maximum sales:

Click here to view code image

Date of Max :=
VAR DailySales =
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "Daily Sales", [Sales Amount]
    )
VAR MaxDailySales = MAXX ( DailySales, [Daily Sales] )
VAR DatesWithMax =
    SELECTCOLUMNS (
        FILTER (
            DailySales,
            [Daily Sales] = MaxDailySales
        ),
        "Date", 'Date'[Date]
    )
VAR Result =
IF (
    COUNTROWS ( DatesWithMax ) = 1,
    DatesWithMax,
    BLANK ()
)
RETURN
    Result

The algorithm is close to the previous one, with some noticeable differences:

• The DailySales variable contains a table with date, and sales amount on each given date. This table is created by using ADDCOLUMNS.

• MaxDailySales no longer computes the daily sales. It scans the precomputed DailySales variable, resulting in faster execution time.

• The same happens with DatesWithMax, which scans the DailySales variable. Because after that point the code only needs the date and no longer the daily sales, we used SELECTCOLUMNS to remove the daily sales from the result.

This latter version of the code is more complex than the original version. This is often the price to pay when optimizing code: Worrying about performance means having to write more complex code.

You will see ADDCOLUMNS and SELECTCOLUMNS in more detail in Chapter 12, “Working with tables,” and in Chapter 13, “Authoring queries.” There are many details that are important there, especially if you want to use the result of SELECTCOLUMNS in other iterators that perform context transition.

Computing averages and moving averages

You can calculate the mean (arithmetic average) of a set of values by using one of the following DAX functions:

• AVERAGE: returns the average of all the numbers in a numeric column.

• AVERAGEX: calculates the average on an expression evaluated over a table.

We discussed how to compute regular averages over a table earlier in this chapter. Here we want to show a more advanced usage, that is a moving average. For example, imagine that you want to analyze the daily sales of Contoso. If you just build a report that plots the sales amount sliced by day, the result is hard to analyze. As you can see in Figure 7-7, the value obtained has strong daily variations.

To smooth out the chart, a common technique is to compute the average over a certain period greater than just the day level. In our example, we decided to use 30 days as our period. Thus, on each day the chart shows the average over the last 30 days. This technique helps in removing peaks from the chart, making it easier to detect a trend.

The following calculation provides the average at the date cardinality, over the last 30 days:

Click here to view code image

AvgXSales30 :=
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR NumberOfDays = 30
VAR PeriodToUse =
    FILTER (
        ALL ( 'Date' ),
        AND (
            'Date'[Date] > LastVisibleDate - NumberOfDays,
            'Date'[Date] <= LastVisibleDate
        )
    )
VAR Result =
    CALCULATE (
        AVERAGEX ( 'Date', [Sales Amount] ) ,
        PeriodToUse
    )
RETURN
    Result

The formula first determines the last visible date; in the chart, because the filter context set by the visual is at the date level, it returns the selected date. The formula then creates a set of all the dates between the last date and the last date minus 30 days. Finally, the last step is to use this period as a filter in CALCULATE so that the final AVERAGEX iterates over the 30-day period, computing the average of the daily sales.

The result of this calculation is visible in Figure 7-8. As you can see, the line is much smoother than the daily sales, making it possible to analyze trends.

When the user relies on average functions like AVERAGEX, they need to pay special attention to the desired result. In fact, when computing an average, DAX ignores blank values. If on a given day there are no sales, then that day will not be considered as part of the average. Beware that this is a correct behavior. AVERAGEX cannot assume that if there are no sales in a day then we might want to use zero instead. This behavior might not be desirable when averaging over dates.

If the requirement is to compute the average over dates counting days with no sales as zeroes, then the formula to use is almost always a simple division instead of AVERAGEX. A simple division is also faster because the context transition within AVERAGEX requires more memory and increased execution time. Look at the following variation of the moving average, where the only difference from the previous formula is the expression inside CALCULATE:

Click here to view code image

AvgSales30 :=
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR NumberOfDays = 30
VAR PeriodToUse =
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] > LastVisibleDate - NumberOfDays &&
        'Date'[Date] <= LastVisibleDate
    )
VAR Result =
CALCULATE (
    DIVIDE ( [Sales Amount], COUNTROWS ( 'Date' ) ),
    PeriodToUse
)
RETURN
    Result

Not leveraging AVERAGEX, this latter version of the code considers a day with no sales as a zero. This is reflected in the resulting value whose behavior is similar to the previous one, though slightly different. Moreover, the result of this latter calculation is always a bit smaller than the previous one because the denominator is nearly always a higher value, as you can appreciate in Figure 7-9.

As is often the case with business calculations, it is not that one is better than the other. It all depends on your specific requirements. DAX offers different ways of obtaining the result. It is up to you to choose the right one. For example, by using COUNTROWS the formula now accounts for days with no sales considering them as zeroes, but it also counts holidays and weekends as days with no sales. Whether this is correct or not depends on the specific requirements and the formula needs to be updated in order to reflect the correct average.

Using RANKX

The RANKX function is used to show the ranking value of an element according to a specific sort order. For example, a typical use of RANKX is to provide a ranking of products or customers based on their sales volumes. RANKX accepts several parameters, though most frequently only the first two are used. All the others are optional and seldom used.

For example, imagine wanting to build the report in Figure 7-10 that shows the ranking of a category against all others based on respective sales amounts.

In this scenario, RANKX is the function to use. RANKX is an iterator and it is a simple function. Nevertheless, its use hides some complexities that are worth a deeper explanation.

The code of Rank Cat on Sales is the following:

Click here to view code image

Rank Cat on Sales :=
RANKX (
    ALL ( 'Product'[Category] ),
    [Sales Amount]
)

RANKX operates in three steps:

1. RANKX builds a lookup table by iterating over the table provided as the first parameter. During the iteration it evaluates its second parameter in the row context of the iteration. At the end, it sorts the lookup table.

2. RANKX evaluates its second parameter in the original evaluation context.

3. RANKX returns the position of the value computed in the second step by searching its place in the sorted lookup table.

The algorithm is outlined in Figure 7-11, where we show the steps needed to compute the value of 2, the ranking of Cameras and camcorders according to Sales Amount.

Here is a more detailed description of the behavior of RANKX in our example:

• The lookup table is built during the iteration. In the code, we had to use ALL on the product category to ignore the current filter context that would otherwise filter the only category visible, producing a lookup table with only one row.

• The value of Sales Amount is a different one for each category because of context transition. Indeed, during the iteration there is a row context. Because the expression to evaluate is a measure that contains a hidden CALCULATE, context transition makes DAX compute the value of Sales Amount only for the given category.

• The lookup table only contains values. Any reference to the category is lost: Ranking takes place only on values, once they are sorted correctly.

• The value determined in step 2 comes from the evaluation of the Sales Amount measure outside of the iteration, in the original evaluation context. The original filter context is filtering Cameras and camcorders. Therefore, the result is the amount of sales of cameras and camcorders.

• The value of 2 is the result of finding the place of Sales Amount of cameras and camcorders in the sorted lookup table.

You might have noticed that at the grand total, RANKX shows 1. This value does not make any sense from a human point of view because a ranking should not have any total at all. Nevertheless, this value is the result of the same process of evaluation, which at the grand total always shows a meaningless value. In Figure 7-12 you can see the evaluation process at the grand total.

The value computed during step 2 is the grand total of sales, which is always greater than the sum of individual categories. Thus, the value shown at the grand total is not a bug or a defect; it is the standard RANKX behavior that loses its intended meaning at the grand total level. The correct way of handling the total is to hide it by using DAX code. Indeed, the ranking of a category against all other categories has meaning if (and only if) the current filter context only filters one category. Consequently, a better formulation of the measure relies on HASONEVALUE in order to avoid computing the ranking in a filter context that produces a meaningless result:

Click here to view code image

Rank Cat on Sales :=
IF (
    HASONEVALUE ( 'Product'[Category] ),
    RANKX (
        ALL ( 'Product'[Category] ),
        [Sales Amount]
    )
)

This code produces a blank whenever there are multiple categories in the current filter context, removing the total row. Whenever one uses RANKX or, in more general terms, whenever the measure computed depends on specific characteristics of the filter context, one should protect the measure with a conditional expression that ensures that the calculation only happens when it should, providing a blank or an error message in any other case. This is exactly what the previous measure does.

As we mentioned earlier RANKX accepts many arguments, not only the first two. There are three remaining arguments, which we introduce here. We describe them later in this section.

• The third parameter is the value expression, which might be useful when different expressions are being used to evaluate respectively the lookup table and the value to use for the ranking.

• The fourth parameter is the sort order of the lookup table. It can be ASC or DESC. The default is DESC, with the highest values on top—that is, higher value results in lower ranking.

• The fifth parameter defines how to compute values in case of ties. It can be DENSE or SKIP. If it is DENSE, then ties are removed from the lookup table; otherwise they are kept.

Let us describe the remaining parameters with some examples.

The third parameter is useful whenever one needs to use a different expression respectively to build the lookup table and to compute the value to rank. For example, consider the requirement of a custom table for the ranking, like the one depicted in Figure 7-13.

If one wants to use this table to compute the lookup table, then the expression used to build it should be different from the Sales Amount measure. In such a case, the third parameter becomes useful. To rank the sales amount against this specific lookup table—which is named Sales Ranking—the code is the following:

Click here to view code image

Rank On Fixed Table :=
RANKX (
    'Sales Ranking',
    'Sales Ranking'[Sales],
    [Sales Amount]
)

In this case, the lookup table is built by getting the value of {'Sales Ranking{'[Sale] in the row context of Sales Ranking. Once the lookup table is built, RANKX evaluates [Sales Amount] in the original evaluation context.

The result of this calculation is visible in Figure 7-14.

The full process is depicted in Figure 7-15, where you can also appreciate that the lookup table is sorted before being used.

The fourth parameter can be ASC or DESC. It changes the sort order of the lookup table. By default it is DESC, meaning that a lower ranking is assigned to the highest value. If one uses ASC, then the lower value will be assigned the lower ranking because the lookup table is sorted the opposite way.

The fifth parameter, on the other hand, is useful in the presence of ties. To introduce ties in the calculation, we use a different measure—Rounded Sales. Rounded Sales rounds values to the nearest multiple of one million, and we will slice it by brand:

Click here to view code image

Rounded Sales := MROUND ( [Sales Amount], 1000000 )

Then, we define two different rankings: One uses the default ranking (which is SKIP), whereas the other one uses DENSE for the ranking:

Click here to view code image

Rank On Rounded Sales :=
RANKX (
    ALL ( 'Product'[Brand] ),
    [Rounded Sales]
)

Rank On Rounded Sales Dense :=
RANKX (
    ALL ( 'Product'[Brand] ),
    [Rounded Sales],
    ,
    ,
    DENSE
)

The result of the two measures is different. In fact, the default behavior considers the number of ties and it increases the ranking accordingly. When using DENSE, the ranking increases by one regardless of ties. You can appreciate the different result in Figure 7-16.

Basically, DENSE performs a DISTINCT on the lookup table before using it. SKIP does not, and it uses the lookup table as it is generated during the iteration.

When using RANKX, it is important to consider which table to use as the first parameter to obtain the desired result. In the previous queries, it was necessary to specify ALL ( Product[Brand] ) because we wanted to obtain the ranking of each brand. For brevity, we omitted the usual test with HASONEVALUE. In practice you should never skip it; otherwise the measure is at risk of computing unexpected results. For example, a measure like the following one produces an error if not used in a report that slices by Brand:

Click here to view code image

Rank On Sales :=
RANKX (
    ALL ( 'Product'[Brand] ),
    [Sales Amount]
)

In Figure 7-17 we slice the measure by product color and the result is always 1.

The reason is that the lookup table contains the sales amount sliced by brand and by color, whereas the values to search in the lookup table contain the total only by color. As such, the total by color will always be larger than any of its subsets by brand, resulting in a ranking of 1. Adding the protection code with IF HASONEVALUE ensures that—if the evaluation context does not filter a single brand—the result will be blank.

Finally, ALLSELECTED is oftentimes used with RANKX. If a user performs a selection of some brands out of the entire set of brands, ranking over ALL might produce gaps in the ranking. This is because ALL returns all the brands, regardless of the filter coming from the slicer. For example, consider the following measures:

Click here to view code image

Rank On Selected Brands :=
RANKX (
    ALLSELECTED ( 'Product'[Brand] ),
    [Sales Amount]
)

Rank On All Brands :=
RANKX (
    ALL ( 'Product'[Brand] ),
    [Sales Amount]
)

In Figure 7-18, you can see the comparison between the two measures in the presence of a slicer filtering certain brands.

RANK.EQ ( <value>, <column> [, <order>] )

Product[Price Rank] =
RANK.EQ ( Product[Unit Price], Product[Unit Price] )

Changing calculation granularity

There are several scenarios where a formula cannot be easily computed at the total level. Instead, the same calculation could be performed at a higher granularity and then aggregated later.

Imagine needing to compute the sales amount per working day. The number of working days in every month is different because of the number of Saturdays and Sundays or because of the holidays in a month. For the sake of simplicity, in this example we only consider Saturdays and Sundays, but our readers can easily extend the concept to also considering holidays.

The Date table contains an IsWorkingDay column that contains 1 or 0 depending on whether that day is a working day or not. It is useful to store the information as an integer because it makes the calculation of days and working days very simple. Indeed, the two following measures compute the number of days in the current filter context and the corresponding number of working days:

Click here to view code image

NumOfDays := COUNTROWS ( 'Date' )
NumOfWorkingDays := SUM ( 'Date'[IsWorkingDay] )

In Figure 7-19 you can see a report with the two measures.

Based on these measures, we might want to compute the sales per working day. That is a simple division of the sales amount by the number of working days. This calculation is useful to produce a performance indicator for each month, considering both the gross amount of sales and the number of days in which sales were possible. Though the calculation looks simple, it hides some complexity that we solve by leveraging iterators. As we sometimes do in this book, we show this solution step-by-step, highlighting possible errors in the writing process. The goal of this demo is not to show a pattern. Instead, it is a showcase of different mistakes that a developer might make when authoring a DAX expression.

As anticipated, a simple division of Sales Amount by the number of working days produces correct results only at the month level. At the grand total, the result is surprisingly lower than any other month:

Click here to view code image

SalesPerWorkingDay := DIVIDE ( [Sales Amount], [NumOfWorkingDays] )

In Figure 7-20 you can look at the result.

If you focus your attention to the total of 2007, it shows 17,985.16. It is surprisingly low considering that all monthly values are above 37,000.00. The reason is that the number of working days at the year level is 261, including the months where there are no sales at all. In this model, sales started in August 2007 so it would be wrong to consider previous months where there cannot be other sales. The same issue also happens in the period containing the last day with data. For example, the total of the working days in the current year will likely consider future months as working days.

There are multiple ways of fixing the formula. We choose a simple one: if there are no sales in a month, then the formula should not consider the days in that month. This formula assumes that all the months between the oldest transaction and the last transaction available have transactions associated.

Because the calculation must work on a month-by-month basis, it needs to iterate over months and check if there are sales in each month. If there are sales, then it adds the number of working days. If there are no sales in the given month, then it skips it. SUMX can implement this algorithm:

Click here to view code image

SalesPerWorkingDay :=
VAR WorkingDays =
    SUMX (
        VALUES ( 'Date'[Month] ),
        IF (
            [Sales Amount] > 0,
            [NumOfWorkingDays]
        )
    )
VAR Result =
    DIVIDE (
        [Sales Amount],
        WorkingDays
    )
RETURN
    Result

This new version of the code provides an accurate result at the year level, as shown in Figure 7-21, though it is still not perfect.

When performing the calculation at a different granularity, one needs to ensure the correct level of granularity. The iteration started by SUMX iterates the values of the month column, which are January through December. At the year level everything is working correctly, but the value is still incorrect at the grand total. You can observe this behavior in Figure 7-22.

When the filter context contains the year, an iteration of months works fine because—after the context transition—the new filter context contains both a year and a month. However, at the grand total level, the year is no longer part of the filter context. Consequently, the filter context only contains the currently iterated month, and the formula does not check if there are sales in that year and month. Instead, it checks if there are sales in that month for any year.

The problem of this formula is the iteration over the month column. The correct granularity of the iteration is not the month; it is the pair of year and month together. The best solution is to iterate over a column containing a different value for each year and month. It turns out that we have such a column in the data model: the Calendar Year Month column. To fix the code, it is enough to iterate over the Calendar Year Month column instead of over Month:

Click here to view code image

SalesPerWorkingDay :=
VAR WorkingDays =
    SUMX (
        VALUES ( 'Date'[Calendar Year Month] ),
        IF (
            [Sales Amount] > 0,
            [NumOfWorkingDays]
        )
    )
VAR Result =
    DIVIDE (
        [Sales Amount],
        WorkingDays
    )
RETURN
    Result

This final version of the code works fine because it computes the total using an iteration at the correct level of granularity. You can see the result in Figure 7-23.

Automatic Date/Time in Power BI

Power BI has a feature called Auto Date/Time, which can be configured through the options in the Data Load section (see Figure 8-1).

When the setting is enabled—it is by default—Power BI automatically creates a date table for each Date or DateTime column in the model. We will call it a “date column” from here on. This makes it possible to slice each date by year, quarter, month, and day. These automatically created tables are hidden to the user and cannot be modified. Connecting to the Power BI Desktop file with DAX Studio makes them visible to any developers curious about their structure.

The Auto Date/Time feature comes with two major drawbacks:

• Power BI Desktop generates one table per date column. This creates an unnecessarily high number of date tables in the model, unrelated to one another. Building a simple report presenting the amount ordered and the amount sold in the same matrix proves to be a real challenge.

• The tables are hidden and cannot be modified by the developer. Consequently, if one needs to add a column for the weekday, they cannot.

Building a proper date table for complete freedom is a skill that you learn in the next few pages, and it only requires a few lines of DAX code. Forcing your model to follow bad practices in data modeling just to save a couple of minutes when building the model for the first time is definitely a bad choice.

Automatic date columns in Power Pivot for Excel

Power Pivot for Excel also has a feature to handle the automatic creation of data structures, making it easier to browse dates. However, it uses a different technique that is even worse than that of Power BI. In fact, when one uses a date column in a pivot table, Power Pivot automatically creates a set of calculated columns in the same table that contains the date column. Thus, it creates one calculated column for the year, one for the month name, one for the quarter, and one for the month number—required for sorting. In total, it adds four columns to your table.

As a bad practice, it shares all the bad features of Power BI and it adds a new one. In fact, if there are multiple date columns in a single table, then the number of these calculated columns will start to increase. There is no way to use the same set of columns to slice different dates, as is the case with Power BI. Finally, if the date column is in a table with millions of rows—as is often the case—these calculated columns increase the file size and the memory footprint of the model.

This feature can be disabled in the Excel options, as you can see in Figure 8-2.

Date table template in Power Pivot for Excel

Excel offers another feature that works much better than the previous feature. Indeed, since 2017 there is an option in Power Pivot for Excel to create a date table, which can be activated through the Power Pivot window, as shown in Figure 8-3.

In Power Pivot, clicking on New creates a new table in the model with a set of calculated columns that include year, month, and weekday. It is up to the developer to create the correct set of relationships in the model. Also, if needed, one has the option to modify the names and the formulas of the calculated columns, as well as adding new ones.

There is also the option of saving the current table as a new template, which will be used in the future for newly created date tables. Overall, this technique works well. The table generated by Power Pivot is a regular date table that fulfills all the requirements of a good date table. This, in conjunction with the fact that Power Pivot for Excel does not support calculated tables, makes the feature useful.

Using CALENDAR and CALENDARAUTO

If you do not have a date table in your data source, you can create the date table by using either CALENDAR or CALENDARAUTO. These functions return a table of one column, of DateTime data type. CALENDAR requires you to provide the upper and lower boundaries of the set of dates. CALENDAR-AUTO scans all the date columns across the entire data model, finds the minimum and maximum years referenced, and finally generates the set of dates between these years.

For example, a simple calendar table containing all the dates in the Sales table can be created using the following code:

Click here to view code image

Date =
CALENDAR (
    DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Sales[Order Date] ) ), 12, 31 )
)

In order to force all dates from the first of January up to the end of December, the code only extracts the minimum and maximum years, forcing day and month to be the first and last of the year. A similar result can be obtained by using the simpler CALENDARAUTO:

Date = CALENDARAUTO ( )

CALENDARAUTO scans all the date columns, except for calculated columns. For example, if one uses CALENDARAUTO to create a Date table in a model that contains sales between 2007 and 2011 and has an AvailableForSaleDate column in the Product table starting in 2004, the result is the set of all the days between January 1, 2004, and December 31, 2011. However, if the data model contains other date columns, they affect the date range considered by CALENDARAUTO. Storing dates that are not useful to slice and dice is very common. For example, if among the many dates a model also contains the customers’ birthdates, then the result of CALENDARAUTO starts from the oldest year of birth of any customer. This produces a large date table, which in turn negatively affects performance.

CALENDARAUTO accepts an optional parameter that represents the final month number of a fiscal year. If provided, CALENDARAUTO generates dates from the first day of the following month to the last day of the month indicated as an argument. This is useful when you have a fiscal year that ends in a month other than December. For example, the following expression generates a Date table for fiscal years starting on July 1 and ending on June 30:

Date = CALENDARAUTO ( 6 )

CALENDARAUTO is slightly easier to use than CALENDAR because it automatically determines the boundaries of the set of dates. However, it might extend this set by considering unwanted columns. One can obtain the best of both worlds by restricting the result of CALENDARAUTO to only the desired set of dates, as follows:

Click here to view code image

Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
FILTER (
    CALENDARAUTO ( ),
    YEAR ( [Date] ) >= MinYear &&
    YEAR ( [Date] ) <= MaxYear
)

The resulting table only contains the useful dates. Finding the first and last day of the year is not that important because CALENDARAUTO handles this internally.

Once the developer has obtained the correct list of dates, they still must create additional columns using DAX expressions. Following is a list of commonly used expressions for this scope, with an example of their results in Figure 8-4:

Click here to view code image

Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
     FILTER (
         CALENDARAUTO ( ),
         YEAR ( [Date] ) >= MinYear &&
         YEAR ( [Date] ) <= MaxYear
     ),
     "Year", YEAR ( [Date] ),
     "Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
     "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
     "Month Number", MONTH ( [Date] ),
     "Month", FORMAT ( [Date], "mmmm" ),
     "Week Day Number", WEEKDAY ( [Date] ),
     "Week Day", FORMAT ( [Date], "dddd" ),
     "Year Month Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
     "Year Month", FORMAT ( [Date], "mmmm" ) & " " & YEAR ( [Date] ),
     "Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q" ) ),
     "Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-" & YEAR ( [Date] )
)

Instead of using a single ADDCOLUMNS function, one could achieve the same result by creating several calculated columns through the user interface. The main advantage of using ADDCOLUMNS is the ability to reuse the same DAX expression to create a date table in other projects.

Working with multiple dates

When there are multiple date columns in the model, you should consider two design options: creating multiple relationships to the same date table or creating multiple date tables. Choosing between the two options is an important decision because it affects the required DAX code and also the kind of analysis that is possible later on.

Consider a Sales table with the following three dates for every sales transaction:

• Order Date: the date when an order was received.

• Due Date: the date when the order is expected to be delivered.

• Delivery Date: the actual delivery date.

The developer can relate the three dates to the same date table, knowing that only one of the three relationships can be active. Or, they can create three date tables in order to be able to slice by any of the three freely. Besides, it is likely that other tables contain other dates. For example, a Purchase table might contain other dates about the purchase process, a Budget table contains other dates in turn, and so on. In the end, every data model typically contains several dates, and one needs to understand the best way to handle all these dates.

In the next sections, we show two design options to handle this scenario and how this affects the DAX code.

Handling multiple relationships to the Date table

One can create multiple relationships between two tables. Nevertheless, only one relationship can be active. The other relationships need to be kept inactive. Inactive relationships can be activated in CALCULATE through the USERELATIONSHIP modifier introduced in Chapter 5, “Understanding CALCULATE and CALCULATETABLE.”

For example, consider the data model shown in Figure 8-5. There are two different relationships between Sales and Date, but only one can be active. In the example, the active relationship is the one between Sales[Order Date] and Date[Date].

You can create two measures for the sales amount based on a different relationship to the Date table:

Click here to view code image

Ordered Amount :=
SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )

Delivered Amount :=
CALCULATE (
    SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),
    USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)

The first measure, Ordered Amount, uses the active relationship between Sales and Date, based on Sales[Order Date]. The second measure, Delivered Amount, executes the same DAX expression using the relationship based on Sales[Delivery Date]. USERELATIONSHIP changes the active relationship between Sales and Date in the filter context defined by CALCULATE. You can see in Figure 8-6 an example of a report using these measures.

Using multiple relationships with a single date table increases the number of measures in the data model. Generally, one only defines the measures that are meaningful with certain dates. If you do not want to handle a large number of measures, or if you want complete freedom of using any measure with any date, then you might consider implementing calculation groups as explained in the following chapter.

Handling multiple date tables

Instead of duplicating every measure, an alternative approach is to create different date tables—one for each date in the model—so that every measure aggregates data according to the date selected in the report. From a maintenance point of view, this might seem like a better solution because it lowers the number of measures, and it allows for the selecting of sales that intersect between two months, but it produces a model that is harder to use. For example, one can easily produce a report with the total number of orders received in January and delivered in February of the same year—but it is harder to show in the same chart the amounts ordered and delivered by month.

This approach is also known as the role-playing dimension approach. The date table is a dimension that you duplicate once for each relationship—that is, once for each of its roles. These two options (using inactive relationships and duplicating the date table) are complementary to each other.

To create a Delivery Date table and an Order Date table, you add the same table twice in the data model. You must at least modify the table name when doing so. You can see in Figure 8-7 the data model containing two different date tables related to Sales.

Figure 8-8 shows an example of a matrix using multiple date tables. Such a report cannot be created using multiple relationships with a single Date table. You can see that renaming column names and content is important to produce a readable result. In order to avoid confusion between order and delivery dates, we used CY as a prefix for order years and DY as a prefix for delivery years.

Using multiple date tables, the same measure displays different results depending on the columns used to slice and dice. However, it would be wrong to choose multiple date tables just to reduce the number of measures because this makes it impossible to create a report with the same measures grouped by two dates. For example, consider a single line chart showing Sales Amount by Order Date and Delivery Date. One needs a single Date table in the date axis of the chart, and this would be extremely complex to achieve with the multiple date tables pattern.

If your first priority is to reduce the number of measures in a model, enabling the user to browse any measure by any date, you should consider using the calculation groups described in Chapter 9, “Calculation groups,” implementing a single date table in the model. The main scenario where multiple date tables are useful is to intersect the same measure by different dates in the same visualization, as demonstrated in Figure 8-8. In most other scenarios, a single date table with multiple relationships is a better choice.

Using Mark as Date Table

Applying a filter on the date column of a calendar table works fine if the date column also defines the relationship. However, one might have a relationship based on another column. Many existing date tables use an integer column—typically in the format YYYYMMDD—to create the relationship with other tables.

In order to demonstrate the behavior, we created the DateKey column in both the Date and Sales tables. We then linked the two using the DateKey column instead of the date column. The resulting model is visible in Figure 8-11.

Using the same code that worked in previous examples to compute the YTD in Figure 8-11 would result in an incorrect calculation. You see this in Figure 8-12.

As you can see, now the report shows the same value for Sales Amount and for Sales Amount YTD. Indeed, since the relationship is no longer based on a DateTime column, DAX does not add the automatic ALL function to the date table. As such, the filter with the date is intersecting with the previous filter, vanishing the effect of the measure.

In such cases there are two possible solutions: one is to manually add ALL to all the time intelligence calculations. This solution is somewhat cumbersome because it requires the DAX coder to always remember to add ALL to all of the calculations. The other possible solution is much more convenient: mark the Date table as a date table.

If the date table is marked as such, then DAX will automatically add ALL to the table even if the relationship was not based on a date column. Be mindful that once the table is marked as a date table, the automatic ALL on the table is always added whenever one modifies the filter context on the date column. There are scenarios where this effect is undesirable, and in such cases, one would need to write complex code to build the correct filter. We cover this later in this chapter.

Using year-to-date, quarter-to-date, and month-to-date

The calculations of year-to-date (YTD), quarter-to-date (QTD), and month-to-date (MTD) are all very similar. Month-to-date is meaningful only when you are looking at data at the day level, whereas year-to-date and quarter-to-date calculations are often used to look at data at the month level.

You can calculate the year-to-date value of sales for each month by modifying the filter context on dates for a range that starts on January 1 and ends on the month corresponding to the calculated cell. You see this in the following DAX formula:

Click here to view code image

Sales Amount YTD :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

DATESYTD is a function that returns a table with all the dates from the beginning of the year until the last date included in the current filter context. This table is used as a filter argument in CALCULATE to set the new filter for the Sales Amount calculation. Similar to DATESYTD, there are another two functions that return the month-to-date (DATESMTD) and quarter-to-date (DATESQTD) sets. For example, you can see measures based on DATESYTD and DATESQTD in Figure 8-13.

This approach requires the use of CALCULATE. DAX also offers a set of functions to simplify the syntax of to-date calculations: TOTALYTD, TOTALQTD, and TOTALMTD. In the following code, you can see the year-to-date calculation expressed using TOTALYTD:

YTD Sales :=
TOTALYTD (
    [Sales Amount],
    'Date'[Date]
)

The syntax is somewhat different, as TOTALYTD requires the expression to aggregate as its first parameter and the date column as its second parameter. Nevertheless, the behavior is identical to the original measure. The name TOTALYTD hides the underlying CALCULATE function, which is a good reason to limit its use. In fact, whenever CALCULATE is present in the code, making it evident is always a good practice—for example, for the context transition it implies.

Similar to year-to-date, you can also define quarter-to-date and month-to-date with built-in functions, as in these measures:

Click here to view code image

QTD Sales := TOTALQTD ( [Sales Amount], 'Date'[Date] )
QTD Sales := CALCULATE ( [Sales Amount], DATESQTD ( 'Date'[Date] ) )
MTD Sales := TOTALMTD ( [Sales Amount], 'Date'[Date] )
MTD Sales := CALCULATE ( [Sales Amount], DATESMTD ( 'Date'[Date] ) )

Calculating a year-to-date measure over a fiscal year that does not end on December 31 requires an optional third parameter that specifies the end day of the fiscal year. For example, both the following measures calculate the fiscal year-to-date for Sales:

Click here to view code image

Fiscal YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date], "06-30" )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'Date], "06-30" ) )

The last parameter corresponds to June 30—that is, the end of the fiscal year. There are several time intelligence functions that have a last, optional year-end date parameter for this purpose: STARTOFYEAR, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR, DATESYTD, TOTALYTD, OPENINGBALANCEYEAR, and CLOSINGBALANCEYEAR.

Fiscal   YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date], "30-06"  )
Fiscal  YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "30-06" ) )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ('Date'[Date],"2018-06-30" ) )

Computing time periods from prior periods

Several calculations are required to get a value from the same period in the prior year (PY). This can be useful for making comparisons of trends during a time period this year to the same time period last year. In that case SAMEPERIODLASTYEAR comes in handy:

Click here to view code image

PY Sales := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

SAMEPERIODLASTYEAR returns a set of dates shifted one year back in time. SAMEPERIODLASTYEAR is a specialized version of the more generic DATEADD function, which accepts the number and type of period to shift. The types of periods supported are YEAR, QUARTER, MONTH, and DAY. For example, you can define the same PY Sales measure using this equivalent expression, which uses DATEADD to shift the current filter context one year back in time:

Click here to view code image

PY Sales := CALCULATE( [Sales Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )

DATEADD is more powerful than SAMEPERIODLASTYEAR because, in a similar way, DATEADD can compute the value from a previous quarter (PQ), month (PM), or day (PD):

Click here to view code image

PQ Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, QUARTER ) )
PM Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, MONTH ) )
PD Sales := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, DAY ) )

In Figure 8-14 you can see the result of some of these measures.

Another useful function is PARALLELPERIOD, which is similar to DATEADD, but returns the full period specified in the third parameter instead of the partial period returned by DATEADD. Thus, although a single month is selected in the current filter context, the following measure using PARALLEPERIOD calculates the amount of sales for the whole previous year:

Click here to view code image

PY Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

In a similar way, using different parameters, one can obtain different periods:

Click here to view code image

PQ Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ) )

In Figure 8-15 you can see PARALLELPERIOD used to compute the previous year and quarter.

There are functions similar but not identical to PARALLELPERIOD, which are PREVIOUSYEAR, PREVIOUSQUARTER, PREVIOUSMONTH, PREVIOUSDAY, NEXTYEAR, NEXTQUARTER, NEXTMONTH, and NEXTDAY. These functions behave like PARALLELPERIOD when the selection has a single element selected corresponding to the function name—year, quarter, month, and day. If multiple periods are selected, then PARALLELPERIOD returns a shifted result of all of them. On the other hand, the specific functions (year, quarter, month, and day, respectively) return a single element that is contiguous to the selected period regardless of length. For example, the following code returns March, April, and May 2008 in case the second quarter of 2008 (April, May, and June) is selected:

Click here to view code image

PM Total Sales :=
CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )

Conversely, the following code only returns March 2008 in case the second quarter of 2008 (April, May, and June) is selected.

Click here to view code image

Last PM Sales :=
CALCULATE ( [Sales Amount], PREVIOUSMONTH( 'Date'[Date] ) )

The difference between the two measures is visible in Figure 8-16. The Last PM Sales measure returns the value of December 2007 for both 2008 and Q1 2008, whereas PM Total Sales always returns the value for the number of months of the selection—three for a quarter and twelve for a year. This occurs even though the initial selection is shifted back one month.

Mixing time intelligence functions

One useful feature of time intelligence functions is the capability of composing more complex formulas by using time intelligence functions together. The first parameter of most time intelligence functions is the date column in the date table. However, this is just syntax sugar for the complete syntax. In fact, the full syntax of time intelligence functions requires a table as its first parameter, as you can see in the following two equivalent versions of the same measure. When used, the date column referenced is translated into a table with the unique values active in the filter context after a context transition, if a row context exists:

Click here to view code image

PY Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] )
)

-- is equivalent to

PY Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) )
)

Time intelligence functions accept a table as their first parameter, and they act as time shifters. These functions take the content of the table, and they shift it back and forth over time by any number of years, quarters, months, or days. Because time intelligence functions accept a table, any table expression can be used in place of the table—including another time intelligence function. This makes it possible to combine multiple time intelligence functions, by cascading their results one into the other.

For example, the following code compares the year-to-date with the corresponding value in the previous year. It does so by combining SAMEPERIODLASTYEAR and DATESYTD. It is interesting to note that exchanging the order of the function calls does not change the result:

Click here to view code image

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) )
)

-- is equivalent to

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

It is also possible to use CALCULATE to move the current filter context to a different time period and then invoke a function that, in turn, analyzes the filter context and moves it to a different time period. The following two definitions of PY YTD Sales are equivalent to the previous two; YTD Sales and PY Sales measures are defined earlier in this chapter:

Click here to view code image

PY YTD Sales :=
CALCULATE (
    [YTD Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
-- is equivalent to

PY YTD Sales :=
CALCULATE (
    [PY Sales],
    DATESYTD ( 'Date'[Date] )
)

You can see the results of PY YTD Sales in Figure 8-17. The values of YTD Sales are reported for PY YTD Sales, shifted one year ahead.

All the examples seen in this section can operate at the year, quarter, month, and day levels, but not at the week level. Time intelligence functions are not available for week-based calculations because there are too many variations of years/quarters/months based on weeks. For this reason, you must implement DAX expressions to handle week-based calculations. You can find more details and an example of this approach in the “Working with custom calendars” section, later in this chapter.

Computing a difference over previous periods

A common operation is calculating the difference between a measure and its value in the prior year. You can express that difference as an absolute value or as a percentage. You have already seen how to obtain the value for the prior year with the PY Sales measure:

Click here to view code image

PY Sales := CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

For Sales Amount, the absolute difference over the previous year (year-over-year or YOY) is a simple subtraction. However, you need to add a failsafe if you want to only show the difference when both values are available. In that case, variables are important to avoid calculating the same measure twice. You can define a YOY Sales measure with the following expression:

Click here to view code image

YOY Sales :=
VAR CySales = [Sales Amount]
VAR PySales = [PY Sales]
VAR YoySales =
    IF (
        NOT ISBLANK ( CySales ) && NOT ISBLANK ( PySales ),
        CySales - PySales
    )
RETURN
    YoySales

The equivalent calculation for comparing the year-to-date measure with a corresponding value in the prior year is a simple subtraction of two measures, YTD Sales and PY YTD Sales. You learned those in the previous section:

Click here to view code image

YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date] )

PY YTD Sales :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

YOY YTD Sales :=
VAR CyYtdSales = [YTD Sales]
VAR PyYtdSales = [PY YTD Sales]
VAR YoyYtdSales =
    IF (
        NOT ISBLANK ( CyYtdSales ) && NOT ISBLANK ( PyYtdSales ),
        CyYtdSales - PyYtdSales
    )
RETURN
    YoyYtdSales

Often, the year-over-year difference is better expressed as a percentage in a report. You can define this calculation by dividing YOY Sales by PY Sales; this way, the difference uses the previous year value as a reference for the percentage difference (100 percent corresponds to a value that is doubled in one year). In the following expressions that define the YOY Sales% measure, the DIVIDE function avoids a divide-by-zero error if there is no corresponding data in the prior year:

Click here to view code image

YOY Sales% := DIVIDE ( [YOY Sales], [PY Sales] )

A similar calculation displays the percentage difference of a year-over-year comparison for the year-to-date aggregation. The following definition of YOY YTD Sales% implements this calculation:

Click here to view code image

YOY YTD Sales% := DIVIDE ( [YOY YTD Sales], [PY YTD Sales] )

In Figure 8-18, you can see the results of these measures in a report.

Computing a moving annual total

Another common calculation that eliminates seasonal changes in sales is the moving annual total (MAT), which considers the sales aggregation over the past 12 months. You learned a technique to compute a moving average in Chapter 7, “Working with iterators and with CALCULATE.” Here we want to describe a formula to compute a similar average by using time intelligence functions.

For example, summing the range of dates from April 2007 to March 2008 calculates the value of MAT Sales for March 2008. The easiest approach is to use the DATESINPERIOD function. DATESINPERIOD returns all the dates included within a period that can be a number of years, quarters, months, or days.

Click here to view code image

MAT Sales :=
CALCULATE (                           -- Compute the sales amount in a new filter
    [Sales Amount],                   -- context modified by the next argument.
    DATESINPERIOD (                   -- Returns a table containing
        'Date'[Date],                 -- Date[Date] values,
        MAX ( 'Date'[Date] ),         -- starting from the last visible date
       -1,                            -- and going back 1
       YEAR                           -- year.
    )
)

Using DATESINPERIOD is usually the best option for the moving annual total calculation. For educational purposes, it is useful to see other techniques to obtain the same filter. Consider this alternative MAT Sales definition, which calculates the moving annual total for sales:

Click here to view code image

MAT Sales :=
CALCULATE (
    [Sales Amount],
    DATESBETWEEN (
        'Date'[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

The implementation of this measure requires some attention. The formula uses the DATESBETWEEN function, which returns the dates from a column included between two specified dates. Because DATESBETWEEN works at the day level, even if the report is querying data at the month level, the code must calculate the first day and the last day of the required interval. A way to obtain the last day is by using the LASTDATE function. LASTDATE is like MAX, but instead of returning a value, it returns a table. Being a table, it can be used as a parameter to other time intelligence functions. Starting from that date, the first day of the interval is computed by requesting the following day (by calling NEXTDAY) of the corresponding date one year before (by using SAMEPERIODLASTYEAR).

One problem with moving annual totals is that they compute the aggregated value—the sum. Dividing this value by the number of months included in the period averages it over the time frame. This gives you a moving annual average (MAA):

Click here to view code image

MAA Sales :=
CALCULATE (
    DIVIDE ( [Sales Amount], DISTINCTCOUNT ( 'Date'[Year Month] ) ),
    DATESINPERIOD (
        'Date'[Date],
        MAX ( 'Date'[Date] ),
       -1,
       YEAR
    )
)

As you have seen, using time intelligence functions results in powerful measures. In Figure 8-19, you can see a report that includes the moving annual total and average calculations.

Using the right call order for nested time intelligence functions

When nesting time intelligence functions, it is important to pay attention to the order used in the nesting. In the previous example, we used the following DAX expression to retrieve the first day of the moving annual total:

Click here to view code image

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )

You would obtain the same behavior by inverting the call order between NEXTDAY and SAMEPERIODLASTYEAR, as in the following code:

Click here to view code image

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) )

The result is almost always the same, but this order of evaluation presents a risk of producing incorrect results at the end of the period. In fact, authoring the MAT code using this order would result in this version, which is wrong:

Click here to view code image

MAT Sales Wrong :=
CALCULATE (
    [Sales Amount],
    DATESBETWEEN (
        'Date'[Date],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    )
)

This version of the formula computes the wrong result at the upper boundary of the date range. You can see this happening in a report like the one in Figure 8-20.

The measure computes the correct value up to December 30, 2009. Then, on December 31 the result is surprisingly high. The reason for this is that on December 31, 2009 NEXTDAY should return a table containing January 1, 2010. Unfortunately, the date table does not contain a row with January 1, 2010; thus, NEXTDAY cannot build its result. Consequently, not being able to return a valid result, NEXTDAY returns an empty table. A similar behavior happens with the following function: SAMEPERIODLASTYEAR. It receives an empty table, and as the result, it returns an empty table too. Because DATESBETWEEN requires a scalar value, the empty result of SAMEPERIODLASTYEAR is considered as a blank value. Blank—as a DateTime value—equals zero, which is December 30, 1899. Thus, on December 31, 2009, DATESBETWEEN returns the whole set of dates in the Date table; indeed, the blank as a starting date defines no boundaries for the initial date, and this results in an incorrect result.

The solution is straightforward. It simply involves using the correct order of evaluation. If SAMEPERIODLASTYEAR is the first function called, then on December 31, 2009, it will return a valid date, which is December 31, 2008. Then, NEXTDAY returns January 1, 2009, that this time does exist in the Date table.

In general, all time intelligence functions return sets of existing dates. If a date does not belong to the Date table, then these functions return an empty table that corresponds to a blank scalar value. In some scenarios this behavior might produce unexpected results, as explained in this section. For the specific example of the moving annual total, using DATESINPERIOD is simpler and safer, but this concept is important in case time intelligence functions are combined for other custom calculations.

Using LASTDATE and LASTNONBLANK

DAX offers several functions to handle semi-additive calculations. However, writing the correct code to handle semi-additive calculations is not just a matter of finding the correct function to use. Many subtle details might break a calculation if the author is not paying attention. In this section, we demonstrate different versions of the same code, which will or will not work depending on the data. The purpose of showing “wrong” solutions is educational because the “right” solution depends on the data present in the data model. Also, the solution of more complex scenarios requires some step-by-step reasoning.

The first function we describe is LASTDATE. We used the LASTDATE function earlier, when describing how to compute the moving annual total. LASTDATE returns a table only containing one row, which represents the last date visible in the current filter context. When used as a filter argument of CALCULATE, LASTDATE overrides the filter context on the date table so that only the last day of the selected period remains visible. The following code computes the last balance by using LASTDATE to overwrite the filter context on Date:

Click here to view code image

LastBalance :=
CALCULATE (
    SUM ( Balances[Balance] ),
    LASTDATE ( 'Date'[Date] )
)

LASTDATE is simple to use; unfortunately, LASTDATE is not the correct solution for many semi-additive calculations. In fact, LASTDATE scans the date table always returning the last date in the date table. For example, at the month level it always returns the last day of the month, and at the quarter level it returns the last date of the quarter. If the data is not available on the specific date returned by LASTDATE, the result of the calculation is blank. You see this in Figure 8-24 where the total of Q3 and the grand total are not visible. Because the total of Q3 is empty, the report does not even show Q3, resulting in a confusing result.

If, instead of using the month to slice data at the lowest level, we use the date, then the problem of LASTDATE becomes even more evident, as you can see in Figure 8-25. The Q3 row now is visible, even though its result is still blank.

If there are values on dates prior to the last day of the Date table, and that last day has no data available, then a better solution is to use the LASTNONBLANK function. LASTNONBLANK is an iterator that scans a table and returns the last value of the table for which the second parameter does not evaluate to BLANK. In our example, we use LASTNONBLANK to scan the Date table searching for the last date for which there are rows in the Balances table:

Click here to view code image

LastBalanceNonBlank :=
CALCULATE (
    SUM ( Balances[Balance] ),
    LASTNONBLANK (
        'Date'[Date],
        COUNTROWS ( RELATEDTABLE ( Balances ) )
    )
)

When used at the month level, LASTNONBLANK iterates over each date in the month, and for each date it checks whether the related table with the balances is empty. The innermost RELATEDTABLE function is executed in the row context of the LASTNONBLANK iterator, so that RELATEDTABLE only returns the balances of the given date. If there is no data, then RELATEDTABLE returns an empty table and COUNTROWS returns a blank. At the end of the iteration, LASTNONBLANK returns the last date that computed a nonblank result.

If all customer balances are gathered on the same date, then LASTNONBLANK solves the problem. In our example, we have different dates for different customers within the same month and this creates another issue. As we anticipated at the beginning of this section, with semi-additive calculations the devil is in the details. With our sample data LASTNONBLANK works much better than LASTDATE because it actively searches for the last date. However, it fails in computing correct totals, as you can see in Figure 8-26.

The result for each individual customer looks correct. Indeed, the last known balance for Katie Jordan is 2,531.00, which the formula correctly reports as her total. The same behavior produces correct results for Luis Bonifaz and Maurizio Macagno. Nevertheless, the grand total seems wrong. Indeed, the grand total is 1,950.00, which is the value of Maurizio Macagno only. It is confusing for a report to show a total composed in theory of three values (2,531.00, 2,205.00, 1,950.00) that only sums up the last value.

The reason is not hard to explain. When the filter context filters Katie Jordan, the last date with some values is July 15. When the filter context filters Maurizio Macagno, the last date becomes July 18. Nevertheless, when the filter context no longer filters the customer name, then the last date is Maurizio Macagno’s, which is July 18. Neither Katie Jordan nor Luis Bonifaz have any data on July 18. Therefore, for the month of July the formula only reports the value of Maurizio Macagno.

As it often happens, there is nothing wrong with the behavior of DAX. The problem is that our code is not complete yet because it does not consider the fact that different customers might have different last dates in our data model.

Depending on the requirements, the formula can be corrected in different ways. Indeed, one needs to define exactly what to show at the total level. Given the fact that there is some data on July 18, the idea is to either

• Consider July 18 the last date to use for all the customers, regardless of their individual last date. Therefore, the customers not reported at a certain date have a zero balance at that date.

• Consider each customer’s own last date, then aggregate the grand total using as the last date, the last date of each customer. Thus, the balance account of a customer is always the last balance available for that customer.

Both these definitions are correct, and it all depends on the requirements of the report. Because both are interesting to learn, we demonstrate how to write the code for both. The easier of the two is considering the last date for which there is some data regardless of the customer. The correct formula only requires changing the way LASTNONBLANK computes its result:

Click here to view code image

LastBalanceAllCustomers :=
VAR LastDateAllCustomers =
    CALCULATETABLE (
        LASTNONBLANK (
            'Date'[Date],
            COUNTROWS ( RELATEDTABLE ( Balances ) )
        ),
        ALL ( Balances[Name] )
    )
VAR Result =
    CALCULATE (
        SUM( Balances[Balance] ),
        LastDateAllCustomers
    )
RETURN
    Result

In this code we used CALCULATETABLE to remove the filter from the customer name during the evaluation of LASTNONBLANK. In this case, at the grand total LASTNONBLANK always returns July 18 regardless of the customer in the filter context. As a result, now the grand total adds up correctly, and the end balance of Katie Jordan and Luis Bonifaz is blank, as you can see in Figure 8-27.

The second option requires more complex reasoning. When using a different date for each customer, the grand total cannot be computed by simply using the filter context at the grand total. The formula needs to compute the subtotal of each customer and then aggregate the results. This is one of the scenarios where iterators are a simple and effective solution. Indeed, the following measure uses an outer SUMX to produce the total by summing the individual values of each customer:

Click here to view code image

LastBalanceIndividualCustomer :=
SUMX (
    VALUES ( Balances[Name] ),
    CALCULATE (
        SUM ( Balances[Balance] ),
        LASTNONBLANK (
            'Date'[Date],
            COUNTROWS ( RELATEDTABLE ( Balances ) )
        )
    )
)

The result of this latter measure computes for each customer the value on their own last date. It then aggregates the grand total by summing individual values. You see the result in Figure 8-28.

As you have learned, the complexity of semi-additive calculations is not in the code, but rather in the definition of its desired behavior. Once the behavior is clear, the choice between one pattern and the other is simple.

In this section we showed the most commonly used LASTDATE and LASTNONBLANK functions. There are two similar functions available to obtain the first date instead of the last date within a time period. These functions are FIRSTDATE and FIRSTNONBLANK. Moreover, there are further functions whose goal is to simplify calculations like the one demonstrated so far. We discuss them in the next section.

Working with opening and closing balances

DAX offers many functions like LASTDATE that simplify calculations retrieving the value of a measure at the opening or closing date of a time period. Although useful, these additional functions suffer from the same limitations as LASTDATE. That is, they work well if and only if the dataset contains values for all the dates.

These functions are STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH, and the corresponding closing functions: ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH. Intuitively, STARTOFYEAR always returns January 1 of the currently selected year in the filter context. In a similar way STARTOFQUARTER and STARTOFMONTH return the beginning of the quarter or of the month, respectively.

As an example, we prepared a different dataset that is aimed at resolving a different scenario where semi-additive calculations are useful. The demo file contains the prices of the Microsoft stock between 2013 and 2018. The value is well known at the day level. But what should a report show at an aggregated level—for example, at the quarter level? In this case, the most commonly used value is the last value of the stock price. In other words, stock prices are another example where the semi-additive pattern becomes useful.

A simple implementation of the last value of a stock works well for simple reports. The following formula computes the last value of the Microsoft stock, considering an average of the prices in case there are multiple rows for the same day:

Last Value :=
CALCULATE (
    AVERAGE ( MSFT[Value] ),
    LASTDATE ( 'Date'[Date] )
)

The result is correct when used in a daily chart like Figure 8-29.