- Downloading and Installing PowerPivot
- Getting your Data into PowerPivot
- Linking an Excel Table to PowerPivot
- Importing Data into PowerPivot
- Adding DAX Calculations to the Grid
- Creating Pivot Tables from PowerPivot
- Defining a Relationship Between Worksheets
- Using Automatic Relationship Detection
- Using Explicit Relationships
- Using the RELATED Function in the PowerPivot Grid
- Adding Slicers to Drive All Pivot Elements
- Adding DAX Calculations to the PivotTable
- Entering a Measure
- Using the X Functions
- Using CountRows and Distinct
- PowerPivot Is a Home Run
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Adding DAX Calculations to the Grid
DAX (which stands for Data Analysis eXpressions) is a new function language. Eighty of Excel's 400 functions made it into DAX. Most of Excel's Date and Time, Information, Logical, Math & Trig, Statistical, and Text functions are in PowerPivot. If you have a favorite function in Excel, look in the fx icon in PowerPivot to see whether it is there.
In addition to the 80 Excel functions in DAX, there are 55 new functions in DAX. The new functions include 34 Time Intelligence functions, 14 Filter and Value functions, and 7 new statistical functions.
Out of the 135 DAX functions, some are appropriate for use in the grid. Others are useful only when you are writing DAX Expressions in the final pivot table.
The PowerPivot grid always has an Add Column heading after the last column of data. Click in the first cell of that column. You can either click the FX icon by the formula bar or type an equals sign and use the AutoComplete drop-downs to build your formula. When you need to refer to another column in this worksheet, use the mouse to click on a cell in that column.
When you finish typing the formula and press Enter, PowerPivot will calculate the formula for the entire column. This could take a lot of time because it might be doing the calculation millions of times. When the calculation is finished, it will have the column heading of New Column. Right-click the column and choose Rename Column to enter an appropriate heading for the column.
In the following figure, a formula calculates a Fiscal Year for a company with a fiscal year ending March 31. The DAX formula is =Year(ENDOFYEAR(demo[Date],"3/31")). Note that this formula combines an existing Excel function, Year(), with one of the new Time Intelligence functions, ENDOFYEAR().
){kind=small}
The Fiscal Quarter function uses the formula of ="Q"&IF(format(demo[Date],"Q")="1",4,format(demo[Date],"Q")-1).
Note that it is possible to refer to values on another worksheet in your formula. However, this has to be done after a relationship has been defined. We'll come back to using RELATED later in this article.