Background

This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1.  What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.

Figure 1: Top 10 Users by Events using an Excel  Named Set

While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter.  Example reports with  this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.

“Filtering” by the Named Set (Static)

As noted in the figure above and the previous blog, with your named set you have a list of the Top 10 Users by Events.  Now you want to filter your data by those ten users.  A quick way to do this is to simply copy/paste the top ten values from the PivotTable you created to a new PowerPivot Table.  To do this, copy the entire table and then open the PowerPivot window.  Because there is data in a tabular format within the clipboard, the Paste from Clipboard panel will be active and you will be able to click the To New Table button

Figure 2: Copy and Paste the PivotTable data into a new PowerPivot table

Once you’ve clicked on the To New Table button, you will be then provided the Paste Preview dialog (right side of Figure 3) so you can create a new table within the PowerPivot Client window.

Figure 3: The original PivotTable of Top 10 Users (left) and creating a new PowerPivot table (right)

Don’t forget to create your relationship between the UserIDs within the pasted table.  In this example, this is the Row Labels column within your “Top 10 All Users by Events” table which contains the top 10 UserIDs and the UserID column within your fact table.

Figure 4: Creating the relationship between the fact table and the related lookup table.

Note: You probably want to actually rename the “Row Labels” column to UserID so its more easy to understand but I kept the “Row Labels” column here for the purpose of this example.

 

Now that you’ve created the relationship, let’s go back and create the report of interest – in this case this is a department / database report as noted in Figure 5.

Figure 5: Department by Database Report

So how do I filter this by my Top 10 users Named Set?  Drag the Row Labels column Top 10 All Users by Events to the Report Filter pane of the PowerPivot Field List.  Open up the filter and note the last checkbox is not checked which represents the users that are not in the top 10 list.

Figure 6: Top 10 Users Named Set within the PivotTable Filter

Once these steps are done, you will notice the same Department / Database report has been updated for the top 10 users of Washington state.

Figure 7: Department / Database Report filtered by Top 10 Users (of Washington State users)

Discussion

So now you have the ability to filter by your Top 10 users for your reports.   This will get you a long way but every time you have a new Top 10 set of users, you’ll need to update the original table and/or create a new table and perform more relationships to do this.

In the next blog posting Create a #PowerPivot report filtered by the Top X Users by <Logic> (Part 3 of 3), I’ll go over how to setup the Top 10 named set so it is dynamic in nature so that way you can change the top 10 criteria and with a few button clicks automatically update the report based on that criteria as well.

Enjoy!