Filters are a very powerful yet simple building block of Prism. Using them will unleash endless possibilities for enhancing your dashboard and getting deeper insights from your data.
This tutorial will introduce you to filters in Prism, and get you started in working them in a few minutes.
Foreword: What are Filters good for?
The dimensions of your data source represent the "flesh" of your data. They give meaning to the numbers and columns you see and analyze.
But sometimes, or maybe even always, we don't really want to see all of a dimension. For example, If I have sales data for a thousand inventory items, putting them all in one huge pivot would not be very helpful.
However, seeing filtered parts of the data would be very useful. For example, seeing my 10 best selling items, all items that were sold this week, all items that were sold internationally, all items that are of type "laptop", etc. To get these parts from the whole dimension is done by using Filters.
Step 1: Creating the new filter
This tutorial uses an Excel file as a data source for the sake of demonstration, but all things demonstrated are similar for all kinds of data sources.
You can download the file yourself and try the demonstrated examples on it before using these techniques on your own data.
Download US Laptop Sales.xls
If you do not know how to connect to a data source, go over this tutorial before proceeding.
Once you have connected to your data source, its dimensions and measures are visible on the data browser.
If you expand any of the dimensions in the tree, you will notice a "Filters" node for each. Expand the "Customer" dimension:
To add a new filter, right click the "Filters" node and select "New...". This will open the "Create new Filter" dialog window:
If you click on "Select filter type", all types of available filters are shown. We will choose the "Top Ranking" filter for this demonstration:
Notice that the dialog window changes according to the filter type you choose. For "Top Ranking" it will look like this:
The "Count" field lets you select the count of your top ranking filter. For example, Top 10, Top 5, Top 3, etc. We will choose Top 3.
Below, under "By", we choose what we want to rank by. For example, "Top 10 by Sales" or "Top 10 by Costs".
Let's choose by "Sum Sales":
Use "Click to define background..." to limit the filter to certain conditions. We will leave this field as it is in this tutorial.
Now let's give a meaningful name for our new Filter in the "Caption" field, and press OK:
The new filter is added under the Filters node:
You can expand the filter itself and see the results:
Step 2: Using the filter on the dashboard
Let's create a new Pivot table by right clicking the canvas and selecting "New"->"Pivot":
If you haven't worked with Pivot tables before, take a look at the Pivot tutorial.
Drag the new filter we've created onto the "Rows" section of the data layout, and drag both Measures "Sum Cost" and "Sum Sales" onto the "Measures" section of the data layout. It should look like this:
Click Update and take a look at the Pivot:
Notice it holds Sales and Cost data only for our top 3 customers by Sales.
Step 3: Using the filter by context
Now let's take the pivot table we just created one step further.
Suppose we don't want to see our top 3 overall customers by Sales, but we want to see the top 3 customers for each weekday.
Who are my top 3 customers on Mondays? Tuesdays? etc.
From the data browser choose "Purchase Date (Weekdays)" and drag it onto "Rows" in the data layout of the Pivot, above the Top 3 filter that we put there before. Not the data layout will look as follows:
Click update and notice the change in the Pivot:
Notice that the Top 3 customers change from day to day, without us having had to change anything about the filter.
The filters you create automatically recognize the context they are used in, and display data accordingly.
In this example, the filter realizes that by being below a weekday dimension in the pivot, it should rerank the top 3 for every weekday.
Conclusion
This tutorial demonstrated how to create and use filters. Although a Top Ranking filter was used here, other types of filters are just as straight-forward.
Further reading:
Filter types - see examples of what each filter type can be used for.
Filter by two measures - Example
Filter by measure and numeric value - Example