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.

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.