If you’re using Power Pivot, you’re probably dealing with table relationships and applying filters to refine results. This blog post explores filter direction in Power Pivot and how it enables filtering across tables.
Let’s look at the relationship between filter direction and cardinality using the people and orders tables in the Superstore dataset:
Head to Diagram View in this workbook and you’ll see that people and orders are related by the region field. If you look carefully at the line establishing this relationship, you’ll see a small arrow pointing from people to orders:
This arrow displays how filters on one table impact the other. Filters on the left table will affect the right table, but not the other way around. Let’s see it in action.
Filtering orders with people
First, let’s confirm what happens when a field from the people table is used to filter orders. I will add a PivotTable based on the Data Model into the workbook, placing region from people in the Filters area and Sum of sales from orders in Values:
As you’d likely expect, placing a filter on region on East, for example, will return only the sum of sales that are found in the East region:
The fancy way to explain what’s going on here is that the filter gets propogated from people to orders. But isn’t this always what happens when you create a filter? What’s the alternative?
Filtering people with orders
To answer this question, let’s create another PivotTable with region from orders in the Filters area and person from people in the Rows:
Go ahead and try filtering the data so you only get records from the East region. When you do, no records are dropped.
Take a look at the people table and it’s pretty clear that the only person associated with this region is Chuck Magee:
What gives? Why isn’t orders able to filter people? It comes back to filter direction — things just don’t flow this way in the data model!
How was this relationship determined?
Power Pivot’s filter direction is based on relationship cardinality. One-to-many relationships, such as between people and orders, have a direction from the “one” side to the “many” side. That means people can filter orders, but not the other way around.
What’s the reasoning here? It’s more efficient to start from the primary (“one”) table and filter down to the related (“many”) table because the primary table contains unique records. In our case, region and person are only listed once in people, but many times in orders, so it makes more sense to have people filter orders than vice versa. This approach reduces the amount of data that needs to be processed and improves performance.
How can you change filter direction?
You cannot change the filter direction in Power Pivot directly. It is pre-determined by the cardinality of the relationship between the tables. If, for some reason you need to change the filter direction indirectly, check out the CROSSFILTER() function in DAX.
The rhythm guitar of Power Pivot
Jazz guitarist Irving Ashby once said: “Rhythm guitar is like the vanilla in a cake. You can’t taste it, but you know when its been left out.” Filter direction functions similarly in your Data Model. Most of the time, it works just as you would think — so smoothly you forget it’s even there. But when something’s wrong, things get confusing, fast. A proper understanding of filter direction can significantly impact the accuracy and performance of your data analysis.
Has filter direction ever gone awry in your work with Power Pivot? What questions do you have about it? Let me know in the comments.
Leave a Reply