How to Use DAX in Excel's PowerPivot Add-In

By Greg Harvey from Excel 2013 All-in-One For Dummies

DAX stands for Data Analysis Expression and is the name of the language that PowerPivot for Excel 2013 uses to create calculations between the columns (fields) in your Excel Data Model. Fortunately, creating a calculation with DAX is more like creating an Excel formula that uses a built-in function than it is like using a programming language such as VBA or HTML.

This similarity is underscored by the fact that all DAX expressions start with an equal sign just like all standard Excel formulas and that as soon as you start typing the first letters of the name of a DAX function you want to use in the expression you’re building, an Insert Function–like drop-down menu with all the DAX functions whose names start with those same letters appears.

And as soon as you select the DAX function you want to use from this menu, PowerPivot not only inserts the name of the DAX function on the PowerPivot Formula bar (which has the same Cancel, Enter, and Insert Function buttons as the Excel Formula bar), but also displays the complete syntax of the function, showing all the required and optional arguments of that function immediately below the Formula bar.

In addition to using DAX functions in the expressions you create for calculated columns in your Data Model, you can also create simpler expressions using the good old arithmetic operators that you know so well from your Excel formulas (+ for addition, – for subtraction, * for multiplication, / for division, and so on).

To create a calculated column for your Data Model, PowerPivot must be in Data View. (If you’re in Diagram View, you can switch back by clicking the Data View command button on the PowerPivot window’s Home tab or by clicking the Grid button in the lower right corner of the PowerPivot window.) When PowerPivot for Excel is in Data View, you can create a new calculated field by following these steps:

Click the tab of the data table in the PowerPivot window to which you want to add the calculated column.
Click the Add button on the Design tab of the PowerPivot Ribbon.

PowerPivot adds a new column at the end of the current data table with the generic field name, Add Column.
Type = (equal sign) to begin building your DAX expression.

PowerPivot activates its Formula bar where it inserts the equal to sign.
Build your DAX expression on the PowerPivot Formula bar more or less as you build an Excel formula in a cell of one of its worksheets.

To use a DAX function in the expression, click the Insert Function button on the PowerPivot Formula bar and select the function to use in the Insert Function dialog box (which is very similar to the standard Excel Insert Function dialog box except that it contains only DAX functions).

To define an arithmetic or text calculation between columns in the current data table, you select the columns to use by clicking them in the data table interspersed with the appropriate operator.

To select a field to use in a calculation or as an argument in a DAX function, click its field name at the top of its column to add it to the expression on the PowerPivot Formula bar. Note that PowerPivot automatically encloses all field names used in DAX expressions in a pair of square brackets as in
```
=[UnitPrice]*[Quantity]
```
where you’re building an expression in an extended price calculated column that multiplies the values in the UnitPrice field by those in the Quantity field of the active data table.
Click the Enter button on the PowerPivot Formula bar to complete the expression and have it calculated.

As soon as you click the Enter button, PowerPivot performs the calculations specified by the expression you just created, returning the results to the new column. (This may take several moments depending upon the number of records in the data table.)

As soon as PowerPivot completes the calculations, the results appear in the cells of the Add Column field. You can then rename the column by double-clicking its Add Column generic name, typing in the new field name, and pressing Enter.

After creating a calculated column to your data table, you can view its DAX expression simply by clicking its field name at the top of its column in the PowerPivot Data View. If you ever need to edit its expression, you can do so simply by clicking the field name to select the entire column and then click the insertion point in the DAX expression displayed on the PowerPivot Formula bar.

If you no longer need the calculated column in the pivot table for its Data Model, you can remove it by right-clicking the column and then selecting Delete Columns on its shortcut menu. If you simply want to hide the column from the Data View, you select the Hide from Client Tools item on this shortcut menu.

Keep in mind that DAX expressions using arithmetic and logical operators follow the same order of operator precedence as in regular Excel formulas. If you ever need to alter this natural order, you must use nested parentheses in the DAX expression to alter the order as you do in Excel formulas. Just be careful when adding these parentheses this that you don’t disturb any of the square brackets that always enclose the name of any data table field referred to in the DAX expression.