Microsoft Office Tutorials and References
In Depth Information
The first option is the easier one. To add a column to the SalesOrderHeader table, you need
to provide a name and an expression for it so that PowerPivot knows how to select that
column and how to compute its values. To add a new column, you need to select the Add
Column button on the Design tab of the PowerPivot ribbon, as you can see in Figure 2-12.
This operation moves the cursor to the end of the current table and places the cursor inside
the formula editor.
FIguRE 2-12 The Add button creates a new calculated column.
You can now write the formula for the new column in the formula bar of PowerPivot. The
formula bar looks very similar to the formula bar of Excel. Nevertheless, formulas for PowerPivot
are very different from formulas in Excel. PowerPivot does not use the Excel formula language.
Instead, it uses a new language called DAX, which we introduce in Chapter 3, “Introduction to
DAX.” But for this simple example, we can ignore the complexities of DAX and enter a simple
formula, which is understandable by itself (moreover, it looks similar to Excel) and is shown in
FIguRE 2-13 The formula for the new column, shown in the formula bar.
This code uses the DAX function IF, which looks and works like the IF function in Excel. If the
value of the first parameter evaluates to True, it returns its second parameter; otherwise,
it returns the third one. In other words, if the OnlineOrderFlag is true, the formula returns
ONLINE ORDER; otherwise, its value is INTERNAL ORDER.
The newly added column has been named CalculatedColumn1 by PowerPivot, which is not
really user friendly. To rename the column, it is enough to select the column, right-click the
column name, and choose Rename from the menu, as you can see in Figure 2-14. You can
choose, for example, to name it OrderType.