In this article, we'll look at the basics of creating calculation formulas for both calculated columns and measures in Power Pivot. If you are new to DAX, be sure to check out QuickStart: Learn DAX Basics in 30 Minutes.
Formula Basics
Power Pivot provides Data Analysis Expressions (DAX) for creating custom calculations in Power Pivot tables and in Excel PivotTables. DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.
Here are some basic formulas that could be used in a calculated column:
| Formula | Description |
| Inserts today's date in every row of the column. |
| Inserts the value 3 in every row of the column. |
| Adds the values in the same row of [Column1] and [Column2] and puts the results in the same row of the calculated column. |
You can create Power Pivot formulas for calculated columns much as you create formulas in Microsoft Excel.
Use the following steps when you create a formula:
-
Each formula must begin with an equal sign.
-
You can either type or select a function name, or type an expression.
-
Begin to type the first few letters of the function or name you want, and AutoComplete displays a list of available functions, tables, and columns. Press TAB to add an item from the AutoComplete list to the formula.
-
Click the Fx button to display a list of available functions. To select a function from the dropdown list, use the arrow keys to highlight the item, and then click Ok to add the function to the formula.
-
Supply the arguments to the function by selecting them from a dropdown list of possible tables and columns, or by typing in values or another function.
-
Check for syntax errors: ensure that all parentheses are closed, and that columns, tables and values are referenced correctly.
-
Press ENTER to accept the formula.
Note: In a calculated column, as soon as you accept the formula, the column is populated with values. In a measure, pressing ENTER saves the measure definition.
Create a Simple Formula
| To create a calculated column with a simple formula
Values are then populated in the new calculated column for all rows. |
Tips for Using AutoComplete
-
You can use Formula AutoComplete in the middle of an existing formula with nested functions. The text immediately before the insertion point is used to display values in the drop-down list, and all of the text after the insertion point remains unchanged.
-
Power Pivot does not add the closing parenthesis of functions or automatically match parentheses. You must make sure that each function is syntactically correct or you cannot save or use the formula. Power Pivot does highlight parentheses, which makes it easier to check if they are properly closed.
Working with Tables and Columns
Power Pivot tables look similar to Excel tables, but are different in the way they work with data and with formulas:
-
Formulas in Power Pivot work only with tables and columns, not with individual cells, range references, or arrays.
-
Formulas can use relationships to get values from related tables. The values that are retrieved are always related to the current row value.
-
You cannot paste Power Pivot formulas into an Excel worksheet and vice versa.
-
You cannot have irregular or "ragged" data, like you do in an Excel worksheet. Each row in a table must contain the same number of columns. However, you can have empty values in some columns. Excel data tables and Power Pivot data tables are not interchangeable, but you can link to Excel tables from Power Pivot and paste Excel data into Power Pivot. For more information, see Add worksheet data to a Data Model using a linked table and Copy and paste rows into a Data Model in Power Pivot.
Referring to Tables and Columns in Formulas and Expressions
You can refer to any table and column by using its name. For example, the following formula illustrates how to refer to columns from two tables by using the fully qualified name:
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
When a formula is evaluated, Power Pivot first checks for general syntax, and then checks the names of columns and tables that you provide against possible columns and tables in the current context. If the name is ambiguous or if the column or table cannot be found, you will get an error on your formula (an #ERROR string instead of a data value in cells where the error occurs). For more information about naming requirements for tables, columns, and other objects, see "Naming Requirements in DAX Syntax Specification for Power Pivot.
Note: Context is an important feature of Power Pivot data models that lets you create dynamic formulas. Context is determined by the tables in the data model, the relationships between the tables, and any filters that have been applied. For more information, see Context in DAX Formulas.
Table Relationships
Tables can be related to other tables. By creating relationships, you gain the ability to look up data in another table and use related values to perform complex calculations. For example, you can use a calculated column to look up all the shipping records related to the current reseller, and then sum the shipping costs for each. The effect is like a parameterized query: you can calculate a different sum for each row in the current table.
Many DAX functions require that a relationship exist between the tables, or among multiple tables, in order to locate the columns that you have referenced and return results that make sense. Other functions will attempt to identify the relationship; however, for best results you should always create a relationship where possible.
When you work with PivotTables, it is especially important that you connect all the tables that are used in the PivotTable so that the summary data can be calculated correctly. For more information, see Work with Relationships in PivotTables.
Troubleshooting Errors in Formulas
If you get an error when you are defining a calculated column, the formula might contain either a syntactic error or semantic error.
Syntactic errors are the easiest to resolve. They typically involve a missing parenthesis or comma. For help with the syntax of individual functions, see DAX Function Reference.
The other type of error occurs when the syntax is correct, but the value or the column referenced does not make sense in the context of the formula. Such semantic errors might be caused by any of the following problems:
-
The formula refers to a non-existing column, table, or function.
-
The formula appears to be correct, but when the Power Pivot fetches the data it finds a type mismatch, and raises an error.
-
The formula passes an incorrect number or type of parameters to a function.
-
The formula refers to a different column that has an error, and therefore its values are invalid.
-
The formula refers to a column that has not been processed. This can happen if you changed the workbook to manual mode, made changes, and then never refreshed the data or updated the calculations.
In the first four cases, DAX flags the entire column that contains the invalid formula. In the last case, DAX grays out the column to indicate that the column is in an unprocessed state.
