PowerPivot Slicers
Slicers are a new addition to Excel 2010. They allow filtering in pivot tables, but with a lot of added advantages compared to traditional filters. First, they look a lot better – they are much more inviting! Second, when multiple filters are selected, you can actually SEE the selected options as opposed to the [multiple values] that you get with traditional filters. And last, when using multiple slicers against a pivot table, they can actually autoselect the members with available data based on the selections on the other slicers (in effect, generating a cascading filter)
It is possible to add a slicer to an Excel worksheet without any pivot table at all. However, in order to benefit from the cascading mechanism, we must tie it to Pivot table (in that way, members of the slicer will know if they have or not numeric data available for them).
Load the PowerPivot window with the prepopulated Contoso BI data, and select PivotTable under the PowerPivot home tab. When asked where it should be placed, select “new worksheet”.
Add the RegionCountryName to the rows field, the Sales Amount to the Values field, and the Calendar year and ContinentName as vertical slicers.
The reason we add the rows and values, even though all we want is the slicers, is to generate the autoexists functionality in the slicers as described above (they will act as cascading filters). In the picture below, you can see this feature in action – members without any data are colored in grey, indicating they have no measure values associated with them.
If we move the pivot table to another page (PivotTable Tools tab->Move PivotTable), the slicers will stay on the current spreadsheet. In that way, we can use them when constructing our cube formula based report while still having the PowerPivot field list available to us in case we want to review available dimension/measure attributes or create new DAX calculations.
There are 7 cube functions in Excel. They can be used to simulate MDX query functionality, from set creation to retrieval of measure values based on filter expressions. In our current report, we will use 3 of them -
CUBESET: used to define a set of members. You can leverage this function to create tuples that span multiple dimensions as well; its usage is not restricted to members of the same dimension as in regular named sets.
CUBERANKEDMEMBER: used to access each of the members of a CUBESET.
CUBEVALUE: used to retrieve measure values in the context of specific slicers (expressions).
If we wanted to know - for example - the total sales for Greece in our sample Contoso BI data, we could use the following formula:
=CUBEVALUE("PowerPivot Data","[DimGeography].[RegionCountryName].[All].[Greece]","[Measures].[Sales]")
The formula queries Vertipaq and retrieves the measure from the equivalent MDX expression of
Aggregate([DimGeography].[RegionCountryName].[All].[Greece], [Measures].[Sales])
Adding Slicers to the Mix
In order to tie our current slicers with the cube functions, we can leverage the fact that Excel makes available a name range as a pointer to the slicer member set.
Query against Vertipaq
When building cube formulas against the Excel vertipaq database, we leverage the automatically generated “PowerPivot Data” connection. There is intellisense in the formulas, which helps guide us see the options we have available.
Note how Vertipaq generated a dimension for each of the queries loaded in-memory. There is also an automatically generated ‘Measures’ dimension.
This dimension holds a not very intuitive ‘count’ aggregate for each of the queries in the vertipaq data store as well. This was done in order to force Analysis Service to create a measure group placeholder, in which to build on when creating DAX calculations. In our example using Contoso BI, some DAX calculated members were already created for us, and Vertipaq correctly placed them as Measure members ([Measures].[BaseQty] below)
You can also see and edit these DAX measures in the PowerPivot field list. They have a calculator icon next to them.
In our specific report example, we will be using the [Measures].[Sales] DAX calculation, which is simple a SUM of all SalesAmount values in the FactSales query. This DAX measure will be evaluated in the context of the slicers used as expressions within the cube function. The picture below shows the DAX measure editor for this calculation.
Add both slicers to the formula -
Tying everything up, we end up with the formula below.
=CUBEVALUE("PowerPivot Data","[Measures].[Sales]",Slicer_ContinentName, Slicer_CalendarYear)
This allows us to uses the slicers to dynamically evaluate the [Measures].[Sales] member in the context of the slicers’ selection.
In the next entry, I will continue building the report in the attempt to answer the questions:
which are the top 5 selling countries per continent and year?
Which were the best selling brands for one or more of the countries above?
Which was the top product out of the best selling brand group above, and what were it sales performance for the last year?