What is DAX?

DAX (for Data Analysis expression) is a language used in Analysis Services, Power BI, and Power Pivot applications in Excel. It is a query language designed to solve basic data analysis and calculation problems, inspired in part by Excel functions but adapted to the reality of database analysis. It provides great flexibility for data analysis tasks. It is indeed a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.

Why is DAX so important?

It’s easy to create a workbook and import some data into it. You can even create PivotTables or PivotCharts that display important information without using any DAX formulas. But, what if you need to analyze critical sales data across several product categories and for different date ranges? Or, you need combine important inventory data from several tables in different data sources? DAX formulas provide this capability and many other important capabilities 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 Business Intelligence, and DAX will help you get there.

How to learn DAX?

We will tour DAX around three very important fundamental concepts: syntax, functions and context, understanding them will provide the best basis on which to develop your DAX skills.

• Syntax

Syntax includes the various elements that make up a formula, or more simply, how the formula is written.

This formula includes the following syntax elements:

1. (A) The measure name Sum of Sales Amount. Formulas for measures can include the measure name, followed by a colon, followed by the calculation formula.
2. (B) The equals sign operator (=) indicates the beginning of the calculation formula. When calculated, it will return a result.
3. (C) The function SUM adds up all of the numbers in the [SalesAmount] column. You will learn more about functions later.
4. (D) Parenthesis () surround one or more arguments. All functions require at least one argument. An argument passes a value to a function.
5. (E) The referenced table FactSales.
6. (F) The referenced column [SalesAmount] in the FactSales table. With this argument, the SUM function knows on which column to aggregate a SUM.

• Functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order or structure. Arguments can be other functions, another formula, column references, numbers, text, logical values such as TRUE or FALSE, or constants.

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

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

In the syntax above SUM is the function referenced by the letter C.

• Context

Context is one of the most important DAX concepts to understand. There are two types of context in DAX; row context and filter context.

Row context : is most easily thought of as the current row. The formula =[SalesAmount] – [TotalCost] calculates a value in a column for each row in the table. Values for each row are calculated from values in two other columns, [SalesAmount] and [TotalCost] in the same row. DAX can calculate the values for each row in the column because it has the context: For each row, it takes values in the [TotalCost] column and subtracts them from values in the [SalesAmount] column. Row context doesn’t just apply to calculated columns. Row context also applies whenever a formula has a function that applies filters to identify a single row in a table. The function will inherently apply a row context for each row of the table over which it is filtering. This type of row context most often applies to measures.

Filter context : is a little more difficult to understand than row context. You can most easily think of filter context as: One or more filters applied in a calculation that determines a result or value. Filter context does not exist in place of row context; rather, it applies in addition to row context. For example, to further narrow down the values to include in a calculation, you can apply a filter context which not only specifies the row context, but also specifies only a particular value (filter) in that row context. Filter context is easily seen in PivotTables. For example, when you add TotalCost to the Values area, and then add Year and Region to the Row or Columns, you are defining a filter context that selects a subset of data based on a given year and region.

Why is filter context so important to DAX? Because, while filter context can most easily be applied by adding column and row labels and slicers in a PivotTable, filter context can also be applied in a DAX formula by defining a filter using functions such as ALL, RELATED, FILTER, CALCULATE, by relationships, and by other measures and columns. For example, let’s look at the following formula in a measure named StoreSales:

Clearly this formula is more complex than some of the other formulas you’ve seen. However, to better understand this formula, we can break it down, much like we’ve done with other formulas.

This formula includes the following syntax elements:

1. (A) The measure name StoreSales, followed by a colon :.
2. (B) The equals sign operator (=) indicates the beginning of the formula.
3. (C) The CALCULATE function evaluates an expression, as an argument, in a context that is modified by the specified filters.
4. (D) Parenthesis () surround one or more arguments.
5. (E) A measure [Sales] in the same table as an expression. The Sales measure has the formula: =SUM(FactSales[SalesAmount]).
6. (F) A comma (,) separates each filter.
7. (G) The referenced column and a particular value, DimChannel[ChannelName] =”Store”, as a filter.

This formula will ensure only sales values, defined by the Sales measure, as a filter, are calculated only for rows in the DimChannel[ChannelName] column with the value “Store”, as a filter.

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

Conclusion

DAX can indeed be a little tricky to learn, but there are many resources available to you. After reading through this topic a few times, and experimenting with a few of your own formulas, you can learn more about other DAX concepts and formulas that can help you solve your own business problems. There are many DAX resources available to you in Power Pivot Help, SQL Server Books Online, whitepapers, and blogs from both Microsoft and leading BI professionals. The DAX Resource Center Wiki (http://social.technet.microsoft.com/wiki/contents/articles/dax-resource-center.aspx) is a great place to start. The Data Analysis Expressions (DAX) Reference is also a great resource. Be sure to save it in your Favorites.

Enjoy with DAX