Excel DAX - Overview
DAX stands for Data Analysis Expressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Excel Power Pivot.
It is not a programming language, but is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the data that is already present in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making.
DAX includes some of the functions that are used in Excel formulas, but with modified functionality and additional functions that are designed to work with relational data and perform dynamic aggregation.
Importance of DAX
The foundation of DAX is the Data Model that is the Power Pivot database in Excel. Data Model consists of tables between which relationships can be defined so as to combine the data from different sources. The data connections to the Data Model can be refreshed as and when the source data changes. Data Model makes the use of the Power Pivot xVelocity in-memory analytics engine (VertiPaq) that makes the data operations to be as quick as possible in addition to accommodating several thousands of rows of data. For more information on Data Model, refer to the tutorial – Power Pivot.
DAX in conjunction with Data Model enables several power features in Excel – Power Pivot, Power PivotTables, Power PivotCharts and Power View. You can use DAX to solve a number of basic calculations and data analysis problems.
DAX is also useful in Power BI to create a new Power BI Desktop file and import some data into it. Further, DAX formulas provide capabilities such as analyzing growth percentage across product categories and for different date ranges, calculating year-over-year growth compared to market trends and many others as well.
Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is the power in Power BI and DAX will help you get there.
Prerequisites for this Tutorial
This tutorial is an extension to Excel Power Pivot tutorial, where you have learnt about the Power Pivot feature, Data Model, Relationships, Power PivotTables, Power Pivot Charts, etc. It would be a good idea to brush up on this tutorial before you delve into DAX as this tutorial is more on the DAX language wherein you write formulas for the analysis of data in the Data Model and report those results.
This tutorial also introduces DAX Functions that are like Excel Functions, but with some variations. A comparison of Excel Functions and DAX Functions is provided to help you distinguish both. Similarly, Excel formulas and DAX formulas are compared and the similarities and differences are discussed. A good understanding of these differences would help you in writing effective DAX formulas efficiently.
Knowledge of Excel Functions and Excel Formulas is not necessary for this tutorial, as DAX is entirely for the Data Model in the Power Pivot window. You will get into an Excel worksheet only to view the Power PivotTables, Power Pivot Charts and Power View visualizations that are based on Data Model. However, if you are an Excel professional with good amount of knowledge in Excel Functions and Formulas, better make a note of what is mentioned in the previous section and the details given in the course of this tutorial.
Calculated Columns
Calculated columns are the columns that you can add to a table in the Data Model, by means of a DAX formula. You have already learnt about them in Excel Power Pivot tutorial, but you will learn in detail in the chapter – Calculated Columns as DAX is all about calculated columns, calculated fields, and DAX functions.
Calculated Fields / Measures
You cannot change the values in the tables in the Data Model by editing. However, you can add calculated fields to a table that can be used in the Power PivotTables. The calculated fields are defined by giving a name and by defining a DAX formula. For details, refer to the chapter – Calculated Fields.
The calculated fields were named as measures in the Excel versions prior to Excel 2013. They are renamed back to measures in Excel 2016. In this tutorial, we will refer them as calculated fields. But, note that the terms - calculated fields and measures - are synonymous and refer to the same in all aspects.
You can edit a calculated field after it is defined and stored. You can change the DAX formula used in the definition or you can rename the calculated field. You will learn about this in the chapter – Editing a Calculated Field. You can delete a calculated field. Refer to the chapter – Deleting a Calculated Field.
DAX Formulas
DAX formulas form the heart of the DAX language. You can create calculated fields and calculated columns by defining them with DAX formulas. You can write DAX formulas for the data analysis operations. DAX formulas do not refer to the individual cells or range of cells in the table, but refer to the tables and columns in the Data Model. A column in a table in the Data Model must contain the same data type.
DAX formulas contain the tables, columns, calculated columns, calculated fields, DAX operators, and DAX functions. Refer to the chapter – DAX Formulas to learn in detail.
DAX Syntax
As is the case with any language, DAX, the formula language also has a syntax. Your DAX formulas should follow DAX syntax, or else, you will either get errors at design time or at run time or you will receive incorrect results.
You will learn the following in the chapter – DAX Syntax −
- DAX naming requirements for Tables, Columns
- DAX operators
- DAX special values
- DAX data types
- DAX implicit data type conversions
DAX Operators
DAX is a formula language and hence makes the use of the operators in defining the formulas. DAX has the following types of operators −
- DAX Arithmetic Operators
- DAX Comparison Operators
- DAX Text Concatenation Operator
- DAX Logical Operators
DAX operator precedence order is also defined and varies from Excel operator precedence order. Refer to the chapter – DAX Operators.
DAX Standard Parameters
DAX Function syntax has certain requirements on parameters. This is because the DAX function arguments can be tables or columns or calculated fields or other DAX functions. Refer to the chapter - DAX Standard Parameters.
DAX Functions
Excel 2013 has 246 DAX functions that you can use in DAX formulas. You will learn about these functions at the category level in the chapter – DAX Functions. However, for details on each DAX function syntax, parameters, usage and return values, you have to refer to our tutorial on – DAX Functions. The section names used for the description of each DAX function is given in the chapter – Understanding DAX Functions.
As DAX functions are required in writing the DAX formulas and the results of the DAX functions used depend on the context they are used, you might have to go back and forth between these two tutorials to get a grasp on DAX that you will use in Data Modeling with DAX and Power BI.
DAX Special Functions
DAX has some functions that make DAX powerful. These DAX functions come under the categories – DAX time intelligence functions and DAX filter functions and require a special mention. You will learn about DAX time intelligence functions in the chapter – Understanding DAX Time Intelligence. You will learn about the usage of DAX filter functions in the chapter – DAX Filter Functions.
DAX Evaluation Context
The results of a DAX formula can vary based on the context that is used for evaluation. DAX has two types of evaluation context – Row Context and Filter Context. Refer to the chapter - DAX Evaluation Context.
DAX Formulas
DAX is a formula language and you have to get the most of it in writing the DAX formulas. Refer to the chapter - DAX Formulas to learn about the formula syntax and how to create them easily and correctly.
The results of the DAX formulas change whenever the data is refreshed and whenever the DAX formulas are recalculated. You have to understand the difference between data refresh and recalculation. Refer to the chapter - Updating the Results of DAX Formulas.
Data in the Data Model is expected and subjected to change from time to time. This is because the data is used for data analysis activities that require up-to-date data at any point of time. To understand the different ways of refreshing data, refer to the chapter - Updating Data in Data Model.
You will understand the different types of DAX formula recalculation in the chapter - Recalculating DAX Formulas.
DAX formula recalculations have to consider data dependencies and follow a specific order. Otherwise, you might get errors or erroneous results. Refer to the chapter - Troubleshooting DAX Formula Recalculation for details.
You will get an insight into some of the common DAX formula errors and you will learn how to fix those errors, in the chapter - DAX Formula Errors.
DAX Scenarios
If you start learning a new language, the best way of getting acquainted to the language is by understanding where to use what. Similarly, DAX being a formula language meant for data analysis, you need to understand the various scenarios where it can be used.
Refer to the following chapters to get details on this.
