Calculated Columns
Using the data from the BTS site, you can create a calculated column using DAX. To begin, download the data files from the BTS site at the following URL:
Figure 4-2 shows the download page.
From the BTS download site, ensure you choose the Prezipped option, and download the file to your development computer. Uncompressing the file will result in a file in the name form On_Time_On_Time_Performance_YYYY_MM.csv, where YYYY is the year of the data and MM is the month number. Import this file into PowerPivot for Excel, noting the column names are in the first row of the dataset. Figure 4-3 below is an example of the On_Time_Performance_2010_07.csv file.
Figure 4-3. On-time performance file example
Note While not strictly necessary for this example, you can also download additional months of data from the BTS site, concatenating all the files into one using the following command within a DOS Command Window, assuming the current directory context is the folder containing your On Time Performance downloads from BTS:
copy On_Time_On_Time_Performance_2010_*.csv On_Time_Performance_2010.csv
Creating a Calculated Column
To create the calculated column, navigate to the PowerPivot window, and move to the last column of the PowerPivot for Excel data table containing the on-time performance data. Figure 4-4 illustrates the PowerPivot table for this data before adding the new calculated column. The final column of the data table will have the header Add Column as the column name.
Figure 4-4. On-time performance table, before adding the calculated column
Navigate to the first row of this new column, and enter the following:
=MONTH(On_Time_On_Time_Performance_2010[FlightDate])
The preceding formula can be entered in its entirety from the keyboard, but the PowerPivot formula editor can assist in finding the correct object names for function arguments. As illustrated in Figure 4-5, typing MONTH(ON in the formula editor will create a pull-down list of potential PowerPivot table columns, in fully qualified table (column) form, beginning with the letters “ON.”
Figure 4-5. Formula editor object name pull-down
Regardless of the method of DAX formula entry, at the completion of the formula, PowerPivot will populate the entire table with the new calculated data and add a new heading to the column with the default name of CalculatedColumn1. Create a new name for the column by right-clicking the column header and selecting the Rename option from the context menu. Use MonthNumber to have a more meaningful name for the new column.
Of particular importance to note here is that in PowerPivot, DAX formulas operate on entire tables. Even though we are working within Excel, the concept of a cell address does not exist within DAX. This is a vital concept to keep in mind as you design and develop your own PowerPivot solutions leveraging DAX. The data for each calculated column is stored at the point in time data is loaded from the source. Because all of the data has been loaded, our MonthNumber column has been populated for the entire table. Moving the record cursor to the end of the table will reveal a value in the MonthNumber column for the final row. Figure 4-6 below contains an excerpt of the On Time Performance table, including the new calculated column MonthNumber.
Figure 4-6. The On Time Performance table with a new calculated column
Filtering PowerPivot Data
The effect of the new calculated column can be immediately seen by using column filtering. Using a mouse-click to pull down the column menu for the MonthNumber column from within PowerPivot will create a menu similar to the one shown in Figure 4-7.
Figure 4-7. Column Menu
The contents of the menu will vary based on the actual data contained in the PowerPivot table. In the example screenshot, the underlying table contains data for the first seven months of 2010. If you downloaded a single month of on-time performance data, your filter dialog will contain a single row representing the month for which data was available.
Spotting Calculated Columns in the PowerPivot Field List
Calculated columns are available for use anywhere columns from the original source data can be used. The PowerPivot Field List renders the calculated column, just as any other column within the source table grouping area. As depicted in Figure 4-8, the new calculated column appears at the bottom (highlighted) of the fields available from the table from which it is calculated.
Figure 4-8. PowerPivot Field List with the calculated column