What's New in PowerPivot

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

The SQL Server 2012 release of PowerPivot for Excel includes the following new features:

• Diagram View. The Diagram View is available on the Home tab of the PowerPivot window, and it enables you to view tables in a visually organized way and to easily add and change relationships and hierarchies. For more information, see PowerPivot Window: Diagram View.

• Hierarchies. A hierarchy is a list of child nodes that you can create from columns and place into any order you want, making it easier for reporting client users to select and navigate the common paths of data. For more information about hierarchies, see Hierarchies in PowerPivot and Create a Hierarchy in a Table (Tutorial).

• Relationships in Diagram View. In Diagram View, you can easily create relationships between columns in separate tables. The relationships appear visually, which enables you to quickly see how all the tables relate to each other. For more information about Relationships in Diagram View, see Create Relationships Between Tables (Tutorial).

• Multiple Relationships. We added the ability to import multiple relationships. The first relationship is active, and the other relationships are inactive and appear as dotted lines in Diagram View. For more information, see PowerPivot Window: Diagram View and View and Edit Relationships.

• Calculation Area. The Calculation Area enables you to view measures in a grid pattern and to easily create, edit, and manage measures and Key Performance Indicators (KPIs) within the model. For more information, see PowerPivot Window: Calculation Area and Create a Measure and KPI (Tutorial).

• Advanced Tab. The advanced features are now available in a separate tab. The features include the ability to create or edit perspectives, summarize a numeric column by an aggregation function, and set reporting properties for a reporting client tool, such as Power View. For more information, see PowerPivot Window: Advanced Tab.

• Reporting Properties. In the Reporting Properties area of the Advanced tab, you can set the table identifier, group values based on a table identifier, add table details, set the representative column, set an image URL, and set the representative image for reporting client tools, such as Power View. For more information, see PowerPivot Window: Advanced Tab.

• New DAX Function. We added a new function to enrich the relationship feature:

  • USERELATIONSHIP Function. Specifies one or more relationships to be used in a specific calculation. For more information, see USERELATIONSHIP Function (DAX).

• Sort by Other Column. If you do not want to sort your column alphabetically, you can now sort your column by another column. For example, you can sort by a month number column that assigns each month its number in order to sort the column in a natural way.

• Add Values to Rows and Columns. You can now add values to rows and columns.

• Blob Support. You can now import images and blobs. Blob data is now automatically detected and accepted as a binary data type.

• Other Tab Changes. We moved the ribbon features around in the Home and Design tabs slightly, based on customer feedback and usability studies.

  • Home Tab. The Freeze and Column Width buttons were added to the Design tab. The View area was added with the Data View, Diagram View, Show Hidden, and Calculation Area buttons. For more information about the new Home tab, see PowerPivot Window: Home Tab, PowerPivot Window: Diagram View, and PowerPivot Window: Calculation Area.

  • Design Tab. The Hide and Unhide button was removed and became the new Show Hidden button that was added to the Home tab. The Mark as Date Table button was added. When you switch to Advanced Mode, the Advanced area is no longer added to the Design tab (the Advanced tab is added instead). For more information about the new Design tab, see PowerPivot Window: Design Tab.

In addition, the following features were added to previous SQL Server 2012 releases of PowerPivot:

• Perspectives. With the Advanced Mode’s Perspectives feature, you can add, edit, delete, copy, and view perspectives. Perspectives are metadata layers that track different slices or sets of data. Perspectives are typically defined for a particular user group or business scenario and make it easier to navigate large data sets. For more information, see Perspectives in PowerPivot and Perspectives Dialog Box.

• Key Performance Indicators. A KPI is based on a specific measure and is designed to help evaluate the current value and status of a metric. For more information, see Key Performance Indicators (KPIs) in PowerPivot and Key Performance Indicator Dialog Box.

• Date Table Settings. You can mark a table as date table, which will enable you to leverage date filtering in Excel. For more information, see Mark as Date Table Dialog Box.

• Show Details. Right-click a cell in a PivotTable in Excel, and then click Show Details. A new worksheet opens with the underlying data that contribute to the value of the specified cell. For more information, see “Show Details” in Create a PivotTable or PivotChart Report.

• New DAX Functions. We added several new functions to extend the language in areas that were requested the most by our customers:

  • Statistical Functions. For more information, see Statistical Functions (DAX).

    • DISTINCTCOUNT. Return a distinct count of cells (DISTINCTCOUNT()). For more information, see DISTINCTCOUNT Function (DAX).

    • Generate Cross Content. Statistical table functions to generate cross content (CROSSJOIN(), GENERATE(), GENERATEALL()). For more information, see CROSSJOIN Function (DAX), GENERATE Function (DAX), and GENERATEALL Function (DAX).

    • Rank. Rank a set of values (RANK.EQ(), RANKX()). For more information, see RANK.EQ Function (DAX) and RANKX Function (DAX).

    • Standard Deviation. These functions were added to calculate the standard deviation of the entire population or a sample (STDEV.S(), STDEV.P(), STDEVX.S(), STDEVX.P()). For more information, see STDEV.S Function (DAX), STDEV.P Function (DAX), STDEVX.S Function (DAX), and STDEVX.P Function (DAX).

    • Statistical Table Functions. These functions enable you to create aggregated table content (SUMMARIZE(), ROW()) or to temporarily add columns to existing tables (ADDCOLUMNS()), without having to create calculated columns. For more information, see SUMMARIZE Function (DAX), ROW Function (DAX), and ADDCOLUMNS Function (DAX).

    • TOPN. Select the top elements of a set (TOPN()). For more information, see TOPN Function (DAX).

    • Variance. Calculate the variance of the entire population or a sample (VAR.S(), VAR.P(), VARX.S(), VARX.P()). For more information, see VAR.S Function (DAX), VAR.P Function (DAX), VARX.S Function (DAX), and VARX.P Function (DAX).

  • Information Functions. For more information, see Information Functions (DAX).

    • LOOKUPVALUE. Informational search functions to look up a certain value from a table (LOOKUPVALUE()) or to assess whether a value or combination of values in a row exist in a table (CONTAINS()). For more information, see LOOKUPVALUE Function (DAX) and CONTAINS Function (DAX).

    • Parent-Child Relationships. Informational parent-child functions (PATH(), PATHCONTAINS(), PATHITEM(), PATHITEMREVERSE(), PATHLENGTH()). For more information, see PATH Function (DAX), PATHCONTAINS Function (DAX), PATHITEM Function (DAX), PATHITEMREVERSE Function (DAX), and PATHLENGTH Function (DAX).

  • SWITCH. Logical function to provide a multiple-choice selection of an option and action (SWITCH()). For more information, see SWITCH Function (DAX).

  • Filter Functions. For more information, see Filter Functions (DAX).

    • ALLSELECTED. Filter removal from columns and rows to have visual totals (ALLSELECTED()). For more information, see ALLSELECTED Function (DAX).

    • Context-Examination Functions. Filter context-examination functions (ISCROSSFILTERED(), ISFILTERED(), HASONEVALUE(), HASONEFILTER(), FILTERS()). For more information, see ISCROSSFILTERED Function (DAX), ISFILTERED Function (DAX), HASONEVALUE Function (DAX), HASONEFILTER Function (DAX), and FILTERS Function (DAX).

    • HASONEVALUE. Tells you what columns contain only one distinct value (HASONEVALUE()). For more information, see HASONEVALUE Function (DAX).

  • CURRENCY. Currency-casting math function (CURRENCY()). For more information, see CURRENCY Function (DAX).

• Change Data Types. You can change the data type for a calculated column in the same way you can change the data type for all non-calculated columns. For more information, see “Changing the Data Type” in Calculated Columns.

• Number Formats for Measures. You can set the type of number format (such as Currency), enter how many decimal places you want to display, select a symbol you want to display with the numbers, and use a digit-grouping symbol (such as a comma) to separate the thousand place. For more information, see “Formatting Options” in Measure Settings Dialog Box (Custom Aggregation).

• Formatting Persistence. Now when you apply formatting to columns in the modeling environment, the formatting persists as you add fields to value areas of a PivotTable.

• Field List: Descriptions. Add descriptions to tables, measures, and key performance indicators (KPIs). When the user hovers over those tables, measures, and KPIs in the Field List, tooltips appear that feature your descriptions of the context of each field. For more information about the Field List, see PowerPivot Field List.

• Field List: Display Order of Tables and Fields. The Field List is now sorted alphabetically. For more information about the Field List, see PowerPivot Field List.

See Also

Tasks

PowerPivot for Excel Tutorial Introduction

Reference

Excel Window: PowerPivot Tab Reference

Concepts

Introducing PowerPivot for Excel

Other Resources

Welcome to PowerPivot for Excel

PowerPivot Window Reference

PowerPivot User Interface