Excel PivotTable Group options disabled with Tabular Model

3
votes

I'm having a lot of fun trying to solve this one.

THE PROBLEM: We have two database 'cubes'. One is a multidimensional (old legacy cube) and one is a tabular model (new). We are trying to phase out the old multidimensional cube.

Client is using new tabular cube model with PivotTable. What she is trying to do is drag in COUNTY (text value) into rows, select multiple county values, right click and group counties into custom groups (ie. NORTH and SOUTH). This works with the old multidimensional cube but is grayed out for the new tabular cube. So much for the new hotness.

FAILED SOLUTIONS:

  1. Confirmed problem is present on both Excel 2013 and 2016
  2. Checked for blank fields. There are no blank fields.
  3. Tried with multiple fields and other tabular data cubes with same problem.
  4. Changed formatting of cells to text.
  5. Read every posting I can find on this topic on the internet.

If someone has a solution for this I would be so grateful.

1
excelssaspivot-tabletabularssas-tabular

1 Answers

2
votes

That's correct. That grouping feature uses "session cubes" which aren't supported in Tabular. Session cubes are actually a deprecated feature for Multidimensional and were never supported for Tabular.

One workaround is to create an MDX calculated member following these instructions (see the What Are Calculated Members section) and use the formula:

Aggregate(
 {
  [County Dimension].[County].&[county 5456],
  [County Dimension].[County].&[county 5457]
 }
)