Introducing DAX

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.