Excel: Adding Calculations In the Power Pivot Grid

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

DAX shares 81 functions with Excel, so if you are proficient with Excel functions, you should have little problem working in DAX. There are two functions in DAX that differ from the equivalent function in Excel.

The first blank column in the PowerPivot window is called Add Column. Click in any cell in that column. Type an equals sign and enter your formula. For example, type =YEAR( and then, using the mouse, click on the Year field. Type a closing parentheses and press Enter. Your formula will populate all the way down the grid.

Right-click on the heading, choose Rename, and type a meaningful field name such as Year.

Seems easy, right? The function is the same as in Excel. Now - there are 81 functions you can use here, and 79 of them are identical to Excel. One that is different: The TEXT function in Excel is FORMAT in DAX. So, in the image below, the month name uses FORMAT.

  1. DAX uses FORMAT instead of TEXT

    It turns out that FORMAT offers choices for Quarter and Week, thus is more robust than TEXT.

    Gotcha: each column can have only one formula. Every row in the column has to have an identical formula. You cannot refer to cells in other rows. Therefore, the concept of cell addresses like A2 is not relevant in PowerPivot.

    As you are building your formula, you can click a field with the mouse to refer to that field. You cannot use the arrow keys to select a field.

For more resources for Microsoft Excel