Part V: Understanding Power Pivot and Power Query
The bottom line is not all DAX functions can be used with calculated columns. Because a
calculated column evaluates at the row level, only DAX functions that evaluate single data
points can be used in a calculated column.
A good rule of thumb is if the function requires an array or a range of cells as an argument,
then it not viable in a calculated column. Therefore, functions such as SUM, MIN, MAX,
AVERAGE, and COUNT don’t work in calculated columns. Functions that require only single
data point arguments work quite well in calculated columns, such as YEAR, MONTH, MID,
LEFT, RIGHT, IF, and IFERROR.
Building DAX-driven calculated columns
To demonstrate the usefulness of employing a DAX function to enhance calculated col-
umns, let’s return to our walk-through example. Go to the Power Pivot window, and click
the InvoiceHeader tab. If you accidentally closed the Power Pivot window, you can activate
it by clicking the Manage button on the Home tab of the Power Pivot Ribbon.
The InvoiceHeader table, shown in Figure 37.9, contains an InvoiceDate column. Although
this column is valuable in the raw table, the individual dates aren’t convenient when ana-
lyzing the data with a PivotTable. It would be beneficial to have a column for Month and a
column for Year. This way, you could aggregate and analyze your data by month and year.
FIGURE 37.9
DAX functions can help enhance the InvoiceHeader data with Year and Month time dimensions.
For this endeavor, you use the YEAR(), MONTH(), and FORMAT() DAX functions to add
some time dimensions to your data model. Follow these steps:
- In the InvoiceHeader table, click the first blank cell in the empty column
labeled Add Column on the far right.
- In the Formula bar, type =YEAR([InvoiceDate]), and then press Enter.
- Power Pivot will automatically rename the column to Calculated Column 1.
Double-click the column label, and rename the column Year.
- Starting the next column, click the first blank cell in the empty column labeled
Add Column on the far right.
- In the Formula bar, type =MONTH([InvoiceDate]) and then press Enter.
