DAX, or Data Analysis Expressions to give it its full name, is a collection of constants, operators, and functions that are used to build expressions that return one or more values. It was originally developed by the SQL Server Analysis Services team as part of their Project Gemini, the development of a new in-memory database technology that would let Excel users work with massive amounts of data. It was introduced initially in 2009 as an add-in to Microsoft Excel 2010 and eventually went on to become the Power Pivot add-in for Excel that we have today.

Today, DAX consists of more than 250 functions, and regularly receives updates to existing functions as well as receiving new functions. It is a language used by the following products in Microsoft's business intelligence stack:

• Excel Power Pivot
• Power BI
• SQL Server Analysis Services (SSAS) Tabular
• Azure Analysis Services

DAX is not a programming language in the traditional sense but is instead a functional language, which means that it makes calls to a function as part of an expression. The result of an expression will, depending on the function, return either a single value or a table as output. The output from an expression can be used to nest functions, by using it as the input parameter to another function.

DAX can only be used to filter or query a physical table; it cannot add, delete, or update data in a table. However, if you are using Power BI or SSAS Tabular, it can use the result of a DAX expression to add a new table to a data model. Unfortunately, this method cannot be used to add tables to an Excel Power Pivot data model without using a workaround, which itself has limitations.

As Power Pivot was originally built as an add-in to Excel, many of the DAX functions are very similar to functions in Excel, which creates a level of familiarity for BI professionals who are already using Excel.

In Table 1-1, you will see that while some functions are almost identical in syntax, others are not. In Excel, the AND function can compare up to 255 logical conditions, while the equivalent function in DAX is limited to just two. Even where functions are identical, the ones in Excel will work with a range of cells, whereas the DAX equivalent will work with columns in a table:

If you are already working with formulas in Excel, then you will be accustomed to working with cells and ranges of cells. However, if you are to successfully transition to working with DAX, you will need to learn to work with the rows and columns of data in tables.

DAX consists of the following function groups:

• Aggregate
• Count
• Date and Time
• Time intelligence
• Information
• Logical
• Mathematical
• Statistical
• Text
• Parent/Child

While DAX functions appear similar to functions found in Excel, they have their own unique characteristics, such as being able to perform calculations that vary by context. They can also return tables as well as values and they can work across the relationships of a data model.

As a BI professional, you may be asking whether it's necessary to learn DAX to be able to use tools such as Power BI or Excel Power Pivot; and the simple answer is no. If you have a well-designed data model filled with good quality data and your reporting requirements are simple, you can get started by dragging and dropping a numeric field onto the report canvas in Power BI, or by adding it to a pivot table in Excel. Behind the scenes, a DAX measure is automatically created, and this is known as an implicit measure.

However, when you want to add columns to existing tables, based on data already in those tables, or you want to create some summary tables, you will probably have to go back to your IT department to get them to add these to an existing database or data warehouse.

The power of DAX is that it enables you, as a BI professional, to add these elements to your data model yourself. Using DAX functions, you can add new columns to an existing table, such as an age range field, based on a person's age.

You can also create explicit measures, which allow you to create aggregated summaries of data, such as record counts. Furthermore, these measures will be dynamically calculated based on any filters or slicers that you add to your Power BI dashboard or Power Pivot worksheet. As you make changes to these filters and slicers, the measures are recalculated dynamically.

With Power BI and Analysis Services, DAX can even be used to create new tables in your data model. Unfortunately, this feature is not available with Excel Power Pivot models.

Quite simply, DAX gives you, as a BI professional, the power to gain deeper insights into your data that you wouldn't otherwise be able to get. When you start to look at the more powerful DAX functions, such as the time-intelligence functions, you can start to carry out some truly amazing analysis of your data. It becomes easy to look at a year-on-year comparison of sales or to look at percentage growth across product ranges for different dates.

While the syntax of DAX is simple, mastering its use can be a challenge. If you are coming from an Excel background, you should be prepared to adopt a different mindset. You will need to study the theory that will be delivered in the following chapters and gain a solid understanding of the following fundamental concepts:

• Calculated columns and measures
• Context
• Syntax
• Functions

Each of these will be looked at in detail throughout this book, with plenty of hands-on examples to help you to understand each concept. When you have done this, you will be ready to put what you have learned into practice. Ultimately, the key to truly mastering the art of using DAX is down to lots of practice and experience.

In DAX, you define the data type for columns of data in a table. In this section, we will look at the different data types that are available and delve into the implicit data type conversions that take place when data is used in a DAX expression. We will also look at the different groups of available operators.

Choosing the correct data type when building your data model helps to ensure that the size of your model is kept to a minimum. It can also help with performance when it comes to refreshing the data in your model.

When you load new data into your model, the modeling engine will attempt to pick the most efficient data type for a column, based on the values that it is importing for that column. However, it is worth checking the data types that it selects, as it may not always choose the most appropriate data type for your data needs. For example, if a column currently contains only integer numbers, the modeling engine will pick the Whole Number data type. If this column subsequently contains fractional values, then the fractional part of these numbers will be lost when the data is imported. Worse still, if the column subsequently contains non-numeric data in the column, then you will get errors when the data is refreshed.

Implicit conversions are described later in this article. Table 1-2 gives details of the different data types available in DAX:

DAX functions have specific requirements for the type of data used for inputs and outputs. If the data in a column passed as an argument is not compatible with the data type required by the function, DAX will try to implicitly convert it into the required data type. If this is not possible, it will return an error.

The type of implicit conversion that DAX performs is determined by the operator, it will convert the data into the type required before it performs the requested operation. Tables 1-3 through to 1-6 list the operators and show the implicit conversion that takes place when the data type in the row is combined with the data type in the column.

Table 1-3 shows the implicit conversion that takes place when a value with the data type in the row is added to a value with the data type in the column:

Table 1-4 shows the implicit conversion that takes place when a value with the data type in the row is subtracted from a value with the data type in the column:

Table 1-5 shows the implicit conversion that takes place when a value with the data type in the row is multiplied by a value with the data type in the column:

Table 1-6 shows the implicit conversion that takes place when a value with the data type in the row is divided by a value with the data type in the column:

In addition to the implicit conversions of numeric types shown in the preceding tables, DAX will automatically convert numbers into strings and strings into numbers depending on the requirements of the operator.

For the concatenation operator (&), DAX will convert numeric values into string values:

Measure 1-1 = 2 & 3

In this example, the DAX measure will evaluate to the string value, "23".

For an arithmetic operator such as addition (+), string values will be converted into a numeric value where possible:

Measure 1-2 = "2" + "3"

In this example, the DAX measure will evaluate to the numeric value 5.

However, there is the potential for errors to occur when allowing for automatic conversion as described. For example, where you are passing string values to an arithmetic operator that cannot be converted into a number, your expression will generate an error. Therefore, you must ensure that correct data types are used for the columns that are used with operators. Exception handling should be done if there is any possibility of errors occurring.

There are four groups of operators in DAX:

• Arithmetic
• Comparison
• Concatenation
• Logical

Table 1-7 shows the different types of operator available within the arithmetic group, along with an example illustrating typical use:

When using arithmetic operators it is important to consider the order in which they need to be applied. If necessary, use parentheses to override the precedence of an operator. Table 1-8 shows the order of precedence for each of the different DAX arithmetic operators:

The following gives an example of where parenthesis can be used to override the precedence of an operator:

5*2+6 = 16

Here, the 5 is multiplied by the 2 to give 10, before the 6 is added to give 16. The multiplication operator (*) has higher precedence than the addition operator (+), so that part of the calculation is calculated first.

However, take a look at this example:

5*(2+6) = 40

Here, the use of the parentheses around 2+6 gives it higher precedence and causes it to be calculated before the result is multiplied by 5.

Table 1-9 shows the different types of comparison operators available, with an example illustrating the operator being used:

Table 1-10 shows the concatenation operator, with some examples illustrating how it is used:

It is important to note that, as we have seen in the previous section on data types, when using the concatenation operator, DAX will implicitly convert numeric values to string values.

Table 1-11 shows the different types of logical operators available, with examples of each operator being used:

In addition to the preceding logical operators, DAX also has the logical AND and OR functions that replicate the functionality of the AND operator (&&) and OR operator (||) respectively.

The advantage of using these functions over the equivalent operators in a complex expression is that it is easier to format and read the code. However, one drawback is that the functions only accept two arguments, restricting you to comparing two conditions only. To be able to compare multiple conditions, you will need to nest the functions. In this case, it might be better to use the AND operator (&&) instead.

The following gives an example of the syntax for the AND function:

Measure 1-3 =
IF (
    AND (
        20 > 10,
        -20 < -10
    ),
    "All true",
    "One or more false"
)

The following gives an example showing the syntax of the AND function nested to compare three conditions:

Measure 1-4 =
IF (
    AND (
        AND (
            10 > 9,
            5 < 10
        ),
        20 > 10
    ),
    "All true",
    "One or more false"
)

The following gives an alternative example of the one given, using the equivalent AND operator (&&):

Measure 1-5 =
IF (
    10 > 9
        && 5 < 10
        && 20 > 10,
    "All true",
    "One or more false"
)

Any column in a table can have blank values, which are the result of the data source containing NULL in values. How a blank value affects the result of a DAX expression depends on the data type expected and the operator being used. In some instances, a blank value will be converted into a zero or an empty string, while in others, it will propagate through as a blank. Table 1-12 shows how different DAX operators handle blank values:

The BLANK data type represents nulls, blank values, empty cells, and missing values. The BLANK function is used to generate blanks, while the ISBLANK function is used to verify a blank value.

Understanding the difference between a calculated column and a measure (also known as a calculated field) is an important concept that you will need to learn to begin mastering DAX. At first, they may seem very similar, and indeed there are some instances where both can be used to obtain the same result. However, they are different and serve different purposes. Likewise, they also impact resources in different ways. Calculated columns allow you to extend a table in your data model by creating additional columns. Measures allow you to aggregate the values of rows in a table and take into account any current filters or slicers that are applied.

You can create new columns by using DAX expressions if you want to extend a table in your Power BI, Excel Power Pivot, or Analysis Services Tabular data model. These are referred to as calculated columns. In Excel, each row of a column in a worksheet can be defined by using a different expression. However, calculated columns evaluate the same expression throughout the column of a table, calculating the appropriate value on a row-by-row basis.

To create a new calculated column in Power BI Desktop, follow these steps:

1. Start on the report page and highlight the table that you want to add a new column to from the list of tables shown in the Fields pane on the right-hand side.
2. Right-click on the table name and select New column from the menu, as shown in Figure 1-1:

Alternatively, you can highlight the table and click on the New Column button on the Calculations section of the Home ribbon or the Calculations section of the Modeling ribbon, as shown in Figure 1-2:

Or you can highlight the table and click on the New Column button on the Calculations section of the Modeling ribbon, as shown in Figure 1-3:

3. Open the formula editor, and enter the DAX expression that will define your New Column. Figure 1-4 shows the formula editor in Power BI Desktop:

To create a new column in Excel Power Pivot and Analysis Services, we do the following:

1. Go into your data model and select the table you want to add the new column to.
2. Select a cell in the last column labeled Add Column.

3. In the formula editor, write the expression that defines your new column. Figure 1-5 shows the formula editor in Excel. Unlike Power BI, in Excel Power Pivot and Analysis Services, the DAX expression begins with the assignment symbol (=) and not the column name:

4. To rename a new column, once you have entered the expression, right-click on the column name and select Rename Column from the menu, as shown in Figure 1-6:

Calculated columns are computed during a data refresh and stored in memory with the rest of your data model. This is an important point to note when you are planning and building your data model. On the one hand, with complex expressions, the time taken to compute them is at the point you refresh the data and not when you are querying the data. This can improve the user experience, especially with complex expressions, but you need to remember that each calculated column will take up space in memory. Although this might not be an issue with a smaller table, it could have a significant impact on memory use when you are dealing with large tables. If you have complex expressions behind your calculated columns, then this could also slow down the time it takes to refresh the data in your data model.

You would be well advised not to have too many calculated columns in your data model and to consider whether it would be possible to use a measure instead, especially if it does not impact the user experience too adversely.

The other way you can extend your data model is by using measures (also referred to as calculated fields in Excel 2013). Unlike calculated columns, which are evaluated row by row using the context of the current row, measures are used to summarize data by aggregating the values of rows in a table. They work within the current filter context, which means they are affected by the current filters, slicers applied, and the highlighted sections of charts or visuals.

There are two types of measures: implicit and explicit. Implicit measures are created behind the scenes when you drag a field to the Values area of the PivotTable Fields list, as shown in Figure 1-7:

They are also created when you drag a visual on to the desktop of Power BI, as shown in Figure 1-8:

An explicit measure, on the other hand, is a measure that is specifically created by you.

A measure must be created if you want to conduct an operation on aggregate values instead of values on a row-by-row basis. For example, if you need to calculate the percentage ratio of two columns, you will need to create a measure that calculates the ratio based on the sum of each column. The following measure calculates the percentage of returns to sales by dividing the sum of items returned by the sum of items sold:

Return % = 
DIVIDE (
    SUM ( Sales[ReturnQuantity] ), 
    SUM ( Sales[SalesQuantity] )
)

Measures are calculated once for everywhere they are used in a report. They are re-calculated every time a report page loads or a user changes a filter or slicer or highlights part of a chart:

In Figure 1-9, a measure is used to calculate the number of returns over the number of sales. This is used for the value in the column chart, with the sales channel used for the axis. In this example, the measure is calculated four times, once for each time the filter context changes to reflect each of the four different stores.

To create a new measure in Power BI Desktop, follow these steps:

1. Start on the report page and highlight the table that you want to add the new measure to, from the list of tables shown in the Fields pane on the right-hand side.
2. Next, right-click on the table name and select New measure from the menu, as shown in Figure 1-10:

To create a new measure in Excel Power Pivot and Analysis Services, we do the following:

1. Go into your data model and select the table you want to add the measure to.
2. Select a blank cell in the calculation area.
3. In the formula editor, write the expression that defines your new measure. Figure 1-11 shows a couple of measures in the calculation area of an Excel table:

The syntax used to create a measure differs slightly depending on the tool you are using. With Power BI, you use the = assignment operator, whereas with Excel and Analysis Services, you use the := assignment operator. If you use the := assignment operator in Power BI, it will automatically be converted into the = operator.

So, for example, Figure 1-12 shows an example of the syntax used to create a measure in Power BI Desktop:

On the other hand, Figure 1-13 shows an example of the syntax used to create a similar measure in Excel:

Although DAX requires measures to be defined within a table, they can be moved between tables without affecting their functionality. In fact, it is good practice to keep general measures under one table with a name such as Key Measures.

Although they may look similar, calculated columns and measures operate very differently. They both use DAX expressions, but they differ in the point at which they are calculated and in the context of their evaluation:

• The values of calculated columns are calculated during a data refresh and they are evaluated using the current row context. They also take up memory and disk space and can slow down data loading times during data refreshes. However, once loaded, they do not impact performance.
• A measure is executed every time a value uses it in a report or chart. Measures are re-calculated every time a page loads. They are also re-calculated when filters or slicers are changed or a user highlights different parts of a chart or visual. A measure does not add to the space used by a data model, but it may impact the speed of user interactions. Measures operate on aggregates that are defined by the current filter context.

You will need to use a calculated column whenever you want to do the following:

• Use the value in a slicer.
• Use the value in rows or columns of a pivot table.
• Use the value on the axes of a chart.
• Use the value as a filter condition in a DAX query.
• Define an expression that is bound to the current row.

You will need to define a measure whenever you want to do the following:

• Use a value that reflects a user's selection of filters, slicers, or highlighted visuals.
• Calculate a ratio.
• Calculate a percentage.

It is sometimes possible to calculate the same value using either a calculated column or a measure, using different DAX expressions. In most cases, where this is possible, you should use a measure, as this will not increase the size of your data model and use extra memory or disk space. This is especially important if you are working with a table that contains a large number of records.

When naming measures, you should not include the table name in the measure name. Although a measure is created under a table, it does not strictly belong to that table. If you do not include the table name, it can easily be moved between tables if necessary. It also makes it easier to identify as a measure. On the other hand, calculated columns should include the table name.

Understanding the concept of the evaluation context in DAX is probably the most important concept you will need to learn, if you are to master the use of DAX. In this section, we will have a brief introduction to the concept and will take a more in-depth look in later chapters.

Evaluation contexts are the basis of advanced DAX functionality. They are used to determine the evaluation of a DAX formula and the corresponding result that's given, which will vary depending on the current context. It is this ability that enables you to perform dynamic analysis, in which the results of a DAX formula can change to reflect the current row or a cell selection, or any filters or slicers that may be applied. Understanding context and using context effectively is essential for building powerful DAX formulas and being able to effectively troubleshoot problems with DAX expressions.

There are two types of evaluation contexts in DAX:

• Row context
• Filter context

You may also see references to a query context in Microsoft documentation, but this is essentially another form of filter context.

The easiest way to think of row context is as the current row in a table. It applies when you add a calculated column to a table. When you use an expression to define your calculated column, it is executed for every row in the table. For example, if you have a table with a thousand rows in it, the expression will be evaluated one thousand times, once for every row in the table, each with a different row context.

The row context can use values from the same row of the table or rows from related tables:

Figure 1-14 shows a calculated column called Sale amount that multiplies the value in the Quantity column by the value in the Unit Price column. Once the data is loaded into the data model from the data source, the calculated column is populated by iterating through each row of the table and calculating the value based on the values contained in the Quantity column and the Unit Price column, for that row. In other words, the value of the calculated column is generated based on the row context as defined by that individual row.

If you have a relationship between tables, the expression used to define a calculated column can also access the columns of a related table by using the RELATED function:

In Figure 1-15, we can see that there is a one-to-many relationship between the Product table and the Sales table. By creating a calculated column with the following expression, it's possible to add the total weight to the Sales table by multiplying the value of the Quantity column by the value of the Weight column in the related Product table:

=
IF (
    ISBLANK ( RELATED ( 'Product'[Weight] ) ),
    0,
    [Quantity] * RELATED ( 'Product'[Weight] )
)

The following screenshot, Figure 1-16, shows the new total weight column added to the Sales table, with values generated for each row:

In the preceding example, the ISBLANK function has been used in conjunction with the IF function to return a zero when a value is not returned from the related table. This would happen when a product in the Sales table does not exist in the related Product table.

The filter context is more complex to understand than the row context, but it can be defined simply as the set of filters that are applied to a data model before the evaluation of a DAX expression begins, which will alter the value returned.

The easiest way to illustrate the filter context is by using a PivotTable:

In Figure 1-17, the PivotTable shows the total sales amount of products by calendar year for each product category. The highlighted cell, showing $310,194.59, has a filter context for the calendar year 2008 and the product category of computers.

The filter context has the following sources of filter:

• Row selection
• Column selection
• Slicer selection
• Filter selection
• A PivotTable filter

Figure 1-18 shows a Power BI report that has slicers for product category and channel. The total sales amount shown in the card visual is the total sales amount with a filter context for the product category of Cell phones and where the sales channel is equal to Catalog:

The filter context will automatically propagate through relationships defined in the data model. In Excel Power Pivot and SQL Analysis Services, only the one-to-many direction is supported, but Power BI has the facility for relationships to be bi-directional.

With a one-to-many relationship, a filter applied to the one side of the relationship automatically filters the rows of the table on the many side of the relationship. If the table on the many side has another table that has a one-to-many relationship with it, the filters do not affect that table, unless you set the relationship to be bi-directional (in Power BI only). We will look at relationships between tables in more detail when we come to looking at data modeling.

So far, we have only looked at implicit filter context, a context created when you use filters or slicers on a Power BI report or add rows and columns to a PivotTable in Excel. However, it is also possible to create an explicit filter context using the DAX CALCULATE function.

The CALCULATE function in DAX evaluates an expression, as an argument, with a context that is modified by the filters that are passed in one or more additional arguments to the function. It is possibly the most important and complex function in the whole of the DAX language. Although it appears very simple when you first look at it, how it can be used and how it can alter an existing filter context can quickly become confusing.

While other functions can remove either part or all of an existing filter context, the CALCULATE function, along with the associated CALCULATETABLE function, are unique in DAX in that they are the only functions that can alter the context. It is this ability that makes them so powerful and so useful to you as a BI professional.

The following is the syntax of the CALCULATE function:

CALCULATE ( <expression>, <filter1>, <filter2>, … )

The function has only one mandatory argument: the expression that is to be evaluated. It will then take one to many optional filter arguments. These optional filter arguments are combined to form the overall filter, which is applied to the expression given as the first argument.

Some restrictions apply to Boolean expressions used as arguments:

• Expressions cannot reference a measure.
• Expressions cannot use a nested CALCULATE function.
• Expressions cannot use any function that scans a table or returns a table, including aggregation functions.

However, expressions can use functions that look up single values or calculate a scalar value.

The power of the CALCULATE function comes from its ability to alter the existing filter context of the expression passed in the first argument, by the n number of filter conditions specified by the following arguments. This is done according to the following:

• If the filter context specified by a filter condition already exists, it will override the existing filter context with the new one specified in the expression.
• If the filter context does not exist at all, it will add a new one according to the filter conditions specified.

As you can see, the syntax for the CALCULATE function is straightforward but following what it is doing is more complex. The best way to show this is through a hands-on example.

In the following example, we have what is possibly the most common scenario for using the CALCULATE function, which is to take a value and calculate what percentage it is of an overall total.

Let's start by creating a new measure to calculate the sum of a column called SaleQuantity in a table called Sales, by using the following DAX expression:

SumOfSalesQuantity =
SUM ( Sales[SalesQuantity] )

In the screenshot shown in Figure 1-19, the measure has been added to a table in Power BI, along with the manufacturer. The manufacturer becomes the filter context for the measure, giving a breakdown of sales quantity by manufacturer:

Now, to be able to calculate the sales quantity of each manufacturer as a percentage of the overall sales quantity, each row will need to know what the overall sales quantity is. To do this, you need an expression that will amend the filter context by removing the manufacturer from the filter. This is where the CALCULATE function comes in.

The next step is to create another measure, which again will calculate the sum of the SalesQuantity column, but uses the ALL function to amend the current filter context:

TotalSalesQuantity =
CALCULATE (
    SUM ( Sales[SalesQuantity] ),
    ALL ( 'Product'[Manufacturer] )
)

In this code, we see the following:

• The first argument calculates the total sum of values in the SalesQuantity column of the Sales table.
• The next argument, the first filter argument, will effectively amend the current filter context by using the ALL function to remove any existing filters on the Manufacturer column of the Product table.

Figure 1-20 shows this measure added to the Power BI table:

As you can see, for each row, the filter context has been altered by the TotalSalesQuantity measure and returns the overall sales quantity, regardless of the manufacturer.

With these two new measures, it is possible to create a measure to calculate the sales quantity of each manufacturer as a percentage of the overall sales quantity:

%SalesQuantity =
DIVIDE (
    // The sum of sales quantity measure - current filter context
    [SumOfSalesQuantity],
    // The sum of sales quantity measure - current filter context altered
    // to include ALL manufacturers
    [TotalSalesQuantity]
)

In this example, we use the DIVIDE function. This function divides the value returned by the measure passed as the first argument (the numerator), by the value returned by the measure passed as the second argument (the denominator). The DIVIDE function also allows for an optional third argument that specifies the alternative value to be returned when division by zero results in an error. When this third argument is not provided, as in this example, the default alternative of BLANK is returned.

Figure 1-21 shows this percentage measure added to the Power BI table:

Finally, it's possible to rewrite this measure as a self-contained measure that doesn't require the intermediate measures of SumOfSalesQuantity and TotalSalesQuantity.

Let's have a look at the following example, which demonstrates this:

%SalesQuantity2 =
DIVIDE (
    // The sum of sales quantity - current filter context
    SUM ( Sales[SalesQuantity] ),
    // The sum of sales quantity - current filter context altered
    // to include ALL manufacturers
    CALCULATE (
        SUM ( Sales[SalesQuantity] ),
        ALL ( 'Product'[Manufacturer] )
    )
)

This is a relatively simple example of the CALCULATE function being used. In Chapter 5, Getting it into Context, there will be some more complex examples when we look at evaluation contexts in more detail.

In this chapter, we covered a brief introduction to the DAX language, looking at what it is and why learning it is important if you want to get the most out of Excel Power Pivot, SSAS Tabular, or Power BI. You should now have an understanding of the different data types and operators available in DAX and how these operators implicitly convert data to the required type. You have learned about calculated columns and measures, including the differences between them and how and when you can make use of them.

You have also learned how to make use of these features in DAX to expand your data model, creating new information from existing data and giving you even greater insights into your data. Finally, you have learned about the evaluation context, with the row context and the filter context, and how you can modify an existing filter context using the CALCULATE function.

In the next chapter, we will move on to look at using variables in DAX formulas and how these can make your DAX code easier to read, as well as potentially more efficient.