A tutorial on using pivot tables in Excel based on SSAS tabular models
Part eight of an eight-part series of blogs

Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.

  1. Excel pivot tables in Analysis Services (Tabular Model)
  2. Setting up pivot tables in Excel
  3. Refreshing a pivot table
  4. Formatting numbers in a pivot table
  5. Formatting a pivot table and choosing display options
  6. Drill-down and Quick Explore
  7. Slicers
  8. Things you can't do in tabular model pivot tables (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 24 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Things you can't do in tabular model pivot tables

If you're an experienced Excel pivot table user, there are a couple of surprises awaiting you.  One is that you can't create calculated fields in pivot tables based on tabular models:

No calculated fields

Presumably you can't create calculated fields here because the whole point of SSAS tabular is to create measures using DAX instead.

 

Another thing that you can't do is to create ad hoc groups:

Grouping not available

If you want to group fields together like this ... you can't! The solution would be to create an aggregator calculated column in your model (more on these later in this tutorial).

 

And on that slightly downbeat note, we've reached the end of this part of the Wise Owl SSAS tabular tutorial! 

This blog has 0 threads Add post