Data Analysis Expressions - Introducing One of Excel's Most Power Features
Data Analysis Expressions open up new opportunities for Excel users!
With the introduction of Power Pivot in 2010, Microsoft took what many consider to be Excel’s most powerful feature – PivotTables – and provided tools to make them even more powerful. One of these tools is Data Analysis Expressions (DAX). Since their introduction, Data Analysis Expressions have become even more powerful and now provide Excel users with extraordinary opportunities to summarize large quantities of data that in ways that might have been impossible prior to the advent of Data Analysis Expressions. Read on and in this article, you will learn how to begin working with Data Analysis Expressions.
What Are Data Analysis Expressions?
In sum, we can think of DAX as functions in Power Pivot used to create calculated fields and calculated columns. A calculated column is the logical equivalent of another column in the data model feeding into your Power Pivot-based PivotTable. A calculated field – also known as a measure – is a bit different in that it provides a summarization of data that is used only in the values area of a PivotTable; consequently, if you find that you need to insert the value returned by a measure elsewhere in the PivotTable, you should construct a calculated column instead of a measure. A major advantage of creating measures using DAX is that they are only calculated when it is necessary to do so. For instance, if you create a measure but the measure is not added to the values area of the PivotTable, then Power Pivot does not recalculate the measure every time the PivotTable changes, such as when adding or removing filters. Thus, when working with very large data models, DAX measures are very efficient and do not impact performance as much as a calculated column created using one or more DAX measures.
Presently, there are over 200 DAX functions available and the use of DAX now extends beyond Power Pivot in Excel to include Power BI and SQL Server Analysis Services. For a complete listing of all DAX functions, you should visit the Microsoft reference provided at https://docs.microsoft.com/en-us/dax/dax-function-reference.
Many of the DAX functions are virtually identical in operation to functions that you would use in Excel. For example, the following five DAX functions operate in a Power Pivot-based PivotTable the same way they operate in a traditional Excel environment.
- SUM
- AVERAGE
- MAX
- MIN
- COUNTA
Other DAX functions provide similar calculation results as functions found in Excel but use different nomenclature. To illustrate, DAX’ CALCULATE function allows you to perform calculations that depend upon multiple conditions or filters; this functionality is like Excel’s SUMIFS, COUNTIFS, and AVERAGEIFS functions.
Additionally, there are numerous DAX functions that provide functionality that is not otherwise natively available in Excel and many of these DAX functions focus on “time intelligence.” For instance, you could use DAX’ ENDOFMONTH, ENDOFQUARTER, or ENDOFYEAR functions to facilitate summarizing data by the appropriate period – month, quarter, or year. Likewise, you could use DAX’ PREVIOUSMONTH, PREVIOUSQUARTER, or PREVIOUSYEAR functions to create comparative values for prior periods.
Three Data Analysis Expressions Examples
With a quick overview of DAX completed, let us now examine three simple examples of how you can use DAX to enhance your Power Pivot-based PivotTables, Power BI reports and dashboards, or SQL Server Analysis Services reports. The data for this example is based on the two tables shown in Figure 1. The data on the left side of the screen capture represents sales transactions stored in a table named Transactions, and the data on the right side of the screen capture represents masterfile data stored in a table named Product. The two tables have been related based on the common field of Product ID to create a data model in Power Pivot.
Using TOTALYTD to Filter to Year-To-Date Transactions
As indicated previously, DAX provides numerous time intelligence functions; one such function is that of TOTALYTD, which is used to create a year-to-date total of all transactions. As shown in Figure 2, a DAX measure was created to identify the sum of the Extension field for all year-to-date transactions, based on the Order Date field. Figure 3 provides an illustration of a simple PivotTable that uses the YTDSales measure calculated in Figure 2; note that numerous rows have been hidden in the PivotTable for presentation purposes.
Using CALCULATE to Filter Data
Discussed previously, CALCULATE is used to determine the result of an expression, inclusive of applying a filter. Figure 4 illustrates how to use the CALCULATE DAX to sum sales for all products named Triumph Pro Helmet, and the PivotTable in Figure 5 shows total sales by customer based on the measure constructed using CALCULATE. Again, numerous rows have been hidden for presentation purposes.
Using Calculate to Filter Data
Discussed previously, CALCULATE is used to determine the result of an expression, inclusive of applying a filter. Figure 4 illustrates how to use the CALCULATE DAX to sum sales for all products named Triumph Pro Helmet, and the PivotTable in Figure 5 shows total sales by customer based on the measure constructed using CALCULATE. Again, numerous rows have been hidden for presentation purposes.
Counting the Number of Distinct Products that Were Purchased
The final simple example of using DAX functions is pictured in Figure 6, where the DISTINCTCOUNT DAX function is used to count the number of different products purchased by customers. Note that this is NOT a sum of the total quantity purchased, but rather, a count of the unique items purchased. The PivotTable in Figure 7 illustrates the results of using this DAX expression to summarize unique products purchased by customer.
Summary
Regardless of whether you work with Power Pivot-based PivotTables, Power BI, or SQL Server Analysis Service, one of the biggest benefits associated with each of these tools is their amazing capability to summarize large volumes of data in the absence of formulas. Yet, sometimes their capabilities do not provide the results we need as end users. In these cases, turn to the library of over 200 DAX functions to see how you can make your reports even more powerful.
The author of this article, Tommy Stephens, is a shareholder in K2 Enterprises, where he develops and presents continuing professional education programs to accounting, financial, and other business professionals across North America. You may contact him at tommy@k2e.com.