Filter Context

One of PowerPivot for Excel’s unique behaviors is the use of the filter context to dynamically transform a calculation to take advantage of additional row (or column) labels and slicer values. DAX includes no concept of cell addressing. Instead, relationships between tables and filter context combine to create an environment for formula evaluation. One way to understand filter context is the row and column context created by both data labels in addition to any applicable slicer values. Considering my fanatic obsession with NCAA football, it is my hope the example in this section will both illustrate filter context and be readily applied to composition analysis.

Using the Roster table from the earlier example in this chapter, we can create a detailed composition analysis of the personnel available to each of the teams in Football Bowl Subdivision (FBS). The Roster table includes some interesting attributes to help you understand the relative experience of each team’s players. In the simplest of terms, each player is granted the ability to play four years, roughly aligning with the time period required to complete a four-year college degree. The Roster table includes the institution in which each player is enrolled and the year of their enrollment, expressed as Freshman, Sophomore, Junior, or Senior, as of the generation of the Roster file.

Begin with a PivotTable

To understand the data, start with a PivotTable using Institution as the row label and Year as the column label. Placing PlayerKey in the values area of the PivotTable Field List (from the earlier example in this chapter) will cause a unique behavior by PowerPivot for Excel. Because the PlayerKey is a text column, there is only one operation that can be applied to the data: Count. The other aggregating functions for measures (Min, Max, Average, and Sum) do not apply and will generate an error. This example will actually take advantage of this behavior, as you need to understand the relative composition of the four classes, by institution. Your PivotTable should resemble Figure 4-24.

Figure 4-24 Count by Instition and Class

At this point, we have some data, but there are at least two problems with this table format. First, the order of the column labels does not follow the normal progression from Freshman to Senior; it’s alphabetical. Second, there are additional Year values representing an empty string and NA.

For the first problem, we can craft a DAX expression that will sort our Roster[Year] column labels in a meaningful order. To most efficiently implement the sort, first we will implement a new table and relationship and then a new DAX expression

Since the meaningful sort order of Freshman, Sophomore, Junior, Senior, NA does not exist in the source data, we can implement the sort using a linked table. In a new sheet, enter the data as it appears in Figure 4-25. The first column is the key for a Year value in the Roster table. The second column, SortYear, represents a string the PivotTable (and perhaps a slicer) will use to sort the Year values in a meaningful way.

Figure 4-25. SortYear linked table data

After entering the data, create a Linked Table using the Create Linked Table selection from the PowerPivot Ribbon. If you check the “My table has headers” option, the new table will contain two columns named Year and SortYear. Additionally, as a matter of personal preference, you can rename the new table from the default TableN+1. My solution uses SortYear as the new table name. Create a relationship from the Year column of the Roster table to the Year column of the new SortYear (or whatever name you chose) table.

Now, it is time to sling a little DAX. Since there is now a relationship between the Roster[Year] and SortYear[Year], we can employ the RELATED() DAX function. The RELATED() function puts a column from another table, into the current table, via an existing relationship between the two tables. As there is already a relationship between Roster and SortYear (via the Year column in both tables), this is a potential solution to the SortYear problem. To implement, enter the following in the formula entry area for a new column:

This use of the RELATED() function will add a new column to the Roster table, named predictably enough in the form CalculatedColumnN+1. Rename the column to something more recognizable (maybe SortYear). Return to the Excel window to further edit the PivotTable to enjoy the benefits of your work in DAX.

The PowerPivot Field List should display the “PowerPivot data was modified” warning. You will need to press the Refresh button to actually see the new table and column that have been implemented. Replace the Year column with the new Roster[SortYear] column in the Column Labels area. Your results should look something like Figure 4-26.

Figure 4-26. SortYear in PivotTable

Note that the values of Year in the column labels are now sorted in a meaningful order, based on the prefix assigned to each when the data was entered (01, 02, etc.). Additionally, you may wonder why the Blank Year values would appear when there is no corresponding row in the SortYear table. This is actually an intended consequence to ensure no accidental misrepresentation of the underlying data occurs. To remove the Blank Year column headers, you would have to filter the with a slicer (or filter at the data source description).

To explicitly eliminate the Blank Year column, add a horizontal (or vertical) slicer to the solution. Select the values of SortYear other than the blank values, and the PivotTable removes the Blank column header. However, the absence of the data from the report is self-evident by the presence of the SortYear slicer (and the deselection of the Blank SortYear).

Compute Percent of Whole

To illustrate filter context, add an additional horizontal slicer to the solution representing the Conference column from the Teams table. Additionally, replace Institution with Conference as the Row Label. Your PivotTable should look something like Figure 4-27.

Figure 4-27. PivotTable by Conference

The Filter Context applied to each cell in the PivotTable is comprised of the selections made by the slicers and the Column and Row labels. The slicers, in this case, only determine which values of Conference and SortYear will appear as Row and Column labels respectively. However, as we add more layers to this illustration, you should see Labels (row or column) that are not directly tied to a slicer. The calculation for the number of Freshman (01-FR) players in the ACC is based on filtering the Roster data for Conference and SortYear.

To add another layer to the example, add Institution as a Row Label after Conference. Subtotaling of the conference-level data is achieved by PowerPivot using Filter Context, which, depending on the row label, may be at the conference or team level. While this is valuable data manipulation, the slicing of the table is not unique to PowerPivot for Excel.

To further understand the usefulness of Filter Context, right-click any of the measures in the PivotTable to reveal the context menu. Select “Show Values as” from the context menu, and select “% of Row Total” from the subsequent menu. Because PowerPivot evaluates each cell based on the implied constraints of the slicers and row and column labels, converting the absolute count of players by year into a percentage for each row total is efficient.

Finally, to dress up the visually unappealing PivotTable, select a single conference from the Conference slicer. Highlight the cells for each of the years, for all teams, avoiding the Conference and Grand Total rows and the Grand Total Column. From the Excel 2010 home ribbon, choose the Conditional Formatting drop-down. Choose Color Scales, and then the three-color pattern. Apply the pattern to the table of percentages, and your solution should look similar to Figure 4-28.

Figure 4-28. Percent of whole, with conditional formatting