Troubleshooting Pivot Table Grouping Errors in Tabular Cubes

Error Messages Encountered

When attempting to group items in PivotTables connected to tabular cubes, users may experience the following:

1. Before Refreshing: If a user selects "Group" from the Excel ribbon or right-clicks to group items, the following error message appears:

   "This Analysis Services feature is not available with database compatibility level 1200 or higher."

   This message indicates that the requested grouping functionality is unavailable in tabular models at this compatibility level.

2. After Refreshing: The "Group" button becomes disabled, preventing users from initiating grouping operations altogether.

Reason for Lack of Support

Tabular data models, which use DAX (Data Analysis Expressions) rather than MDX (Multidimensional Expressions), do not support grouping in PivotTables. Compatibility level 1200 and higher introduced a more modern data modeling architecture but omitted features such as Excel grouping.

This limitation is by design and applies to all tabular models using compatibility level 1200 or higher. For more technical details, refer to Microsoft’s documentation on Tabular Model Programming for Compatibility Level 1200.

Workarounds

Although grouping functionality is unavailable, there are alternative methods to achieve similar results:

1. Use Hierarchy Management in Catalyst:

   • Catalyst provides robust tools to manage hierarchies directly within the platform. To create groups:

     • Navigate to the hierarchy editor in Catalyst.

     • Define groupings by consolidating data points into logical categories.

     • These hierarchies will be reflected in Excel PivotTables, ensuring consistent and pre-aggregated groupings.

2. Avoid Adding Data to the Data Model:

   • As a workaround, you can convert your Catalyst PivotTable cube to a flat file by copying and pasting as values into another sheet or exporting the data directly from Catalyst rather than using a cube PivotTable. Then, create your PivotTable off of this data set.

   • When creating PivotTables, uncheck the "Add this data to the Data Model" option. This allows the use of Excel’s native grouping feature, though it may limit other advanced capabilities like distinct counts.

3. Create Grouping Columns in the Data Model:

   • Open the Power Pivot window and add calculated columns to define groupings. For instance, to group dates by month, use the following DAX formula:

     =FORMAT([Date], "MMMM")

   • These calculated columns can then be used in your PivotTable to simulate grouping.

Next Steps

For users encountering this issue, we recommend:

• Adjusting workflows to manage groupings in Catalyst or through calculated columns, using Smartload cubes, and aligning hierarchies in Catalyst to account for groups you’d like to see.

• Educating team members on the limitations of tabular models and introducing workarounds.

• Monitoring updates from Microsoft for potential future support of grouping in tabular cubes.

Further Reading

1. Microsoft Documentation: Tabular Model Programming for Compatibility Level 1200

2. Microsoft Answers: Unable to Group Fields in Pivot Table That Uses OLAP Cube

3. Contextures Blog: Problem Grouping Pivot Table Items

4. Excel Pivot Table Blog: Cannot Group That Selection Error

5. Stack Overflow: Why Can't I Group Pivot Table Fields Connected to a Tabular Model?

By following these guidelines, you can maintain efficient data analysis workflows despite this Microsoft limitation.