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.
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.
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 includes the various elements that make up a formula, or more simply, how the formula is written.
This formula includes the following syntax elements:
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:
In the syntax above SUM is the function referenced by the letter C.
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:
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.
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