Data Analysis Expressions: PowerPivot’s Secret Weapon
In my last blog post, I expounded on the value of Excel 2013’s new feature: PowerPivot. However, this new feature has a secret weapon that makes PowerPivot even more valuable for the power Excel user. DAX is easily the best feature of PowerPivot. DAX stands for Data Analysis Expressions. DAX formulas are created in the PowerPivot window using the xVelocity in-memory analytical engine, which executes DAX code. DAX enables the Excel user to create formulas that consist of more advanced calculations that work on data stored in multiple tables. The use of DAX formulas also supports the creation of self-service business intelligence solutions in Excel.
There are two types of DAX formulas: calculated column and calculated fields (also known as measures). In order to understand DAX formulas and their capabilities you have to understand the term evaluation context. In any DAX formula, the evaluation context is applied first in the formula and then the aggregation takes place on the evaluated context. Context is what makes it possible to perform dynamic analysis with DAX formulas.
The other DAX formula of calculated fields (also known as measures) is the real game changer of PowerPivot. The evaluation context of calculated fields is the filter context. Filter context is the set of values allowed in each column, based on either filter constraints that were applied to the row by the user or that are defined by filter expressions within the DAX formula. DAX formulas that honor the filter constraint of the user can have different results based on the filter selected by the user. . DAX formulas defined by the filter expression within the DAX formula itself allows the formula to overwrite the filter context of the user. These two methods of defining filter context in DAX, allow for pivot tables to have measures that both honor and ignore the filter context of the user side by side.
These calculated fields can be saved and are available for selection in the field list of the PowerPivot report. The ability to create DAX formulas that respond to the filter contexts of the user and filter expressions of the DAX formula gives the Excel power user the ability to create business intelligence solutions within the Excel application, such as dashboards and data marts which serves as business intelligence solutions for smaller companies.
Learning PowerPivot and DAX formulas requires a certain commitment of time on the Excel user’s part, but the benefits they will obtain from doing so will far outweigh the commitment of time. Learning DAX will enable Excel users to create analytical solutions for small companies while learning a technology that is currently being used by large companies for their analytical and business intelligence needs.
Join the AICPA’s Excel for Accounting Professionals webcast series, June 3 to 12, to learn more than 30 Excel features, functions and techniques.
Virginia Avrutin, CPA, CFE, Director, Marks Paneth & Shron LLP. Virginia specializes in employee benefit plans and the real estate industry, with more than 30 years of accounting, auditing and tax experience.