All language subtitles for 12. Understanding Filter Flow

All right time to cover one of the most important lectures certainly in the data modeling section but

perhaps in the entire course.

Let's talk about filter flow now to demonstrate that you use a very simple example here you've just

got a three table model two data tables sales and returns both with valid connections or relationships

to a single look at Territory look up.

Now when you look closely at the two relationships you'll note the filter directions which are shown

as arrows point from the one side of the relationship or the lookup side to the many side the data side.

And when you filter any table that filter context is passed along through that relationship line to

all related downstream tables following the direction of the arrow.

And by contrast filters or filter context cannot flow upstream.

In other words it cannot flow against the direction of the arrow.

So you can almost think of these relationships as filter wires because that's all that relationships

do is they pass filter context back and forth between tables.

Quick protip one thing you may have already noticed that I've been doing is that I like to arrange my

lookup tables above my data tables in every data model that I build.

And that just helps serve as a visual reminder that filters flowed down stream from those lookups down

to your data tables.

So let's look at a visual representation of why filter flow is so important.

We're going to look at that same simple three table model and let's say we want to analyze sales and

returns broken down by territory key.

Now the only valid way to do that is to use that territory key field from our lookup table.

And when we grab that field from the Look-Up pull it into our matrix rows we see the proper accurate

values for order quantity and return quantity both broken down by territory.

Now the reason we get the correct answer here is because that filter context that we created by using

the territory key in the lookup table is able to pass or flow downstream to both the sales table and

the returns table.

But keep in mind we also have a territory key in the returns data as well as a territory key in the

sales data.

So technically there are three different ways we could try to replicate this view.

So let's take a look at what happens when we try to do that filtering using territory key from our sales

data table will yield the correct order quantities since those come from the sales data table but incorrect

returns values.

And the reason that's the case is that the filter contacts that we created by using territory key in

the sales data table is stuck in this table.

It cannot flow up there this relationship because it can't flow against the direction of the arrow to

filter either the look up or the returns data.

So as a result the return quantity knows nothing about the filter context that was set from using the

territory key in the sales data table and therefore it just returns the same grand total value over

and over and over again.

Now very similar case if we try to use the territory key from the returns table we see the correct value

for the return quantity now but incorrect values for order quantity because the filter context that

we created by using the territory key and the returns data is stuck in the returns data table.

It can't filter the look up or the sales data table.

So just like our last example order quantity now knows nothing about the filter context passed from

the returns data table and therefore returns the grand total over and over and over again in addition

to that.

You'll notice that this table smaller contains fewer rows and that's because only territories that registered

returns are even going to be visible in this table since the filter's taking place within that returns

data table.

So we don't even see data from territories 2 and 3 even though they registered orders.

So this is a little bit tricky to grasp at first but it's an incredibly important concept that's going

to come up time and time again as we start working with more complicated concepts like daks formulas

and interactions between individuals and the report view.

So to really drive this point home Let's hop back into power be-I and build our own matrix visual to

see this filter flow concept for ourselves.

All right back in my report view my Adventure Works file I've got the same matrix that we just showed

in our last video and to follow the demo that we just talked about and then I select the Matrix and

instead of gender here let's go ahead and find our territories table.

There it is.

And we want the sales territory key and drag that into rows.

So just like we're demonstrating We've got 10 sales territories 1 through 10 and we're seeing proper

order quantities and proper return quantities.

And you can see from that last example that territory is 2 and 3 did not register any returns in our

sample so those cells are blank.

So this is an accurate valid view for orders and returns by sales territory.

But just like we demonstrated if we pull sales territory out of the rows and we drill into the actual

sales data itself and grab the territory key from there when we dropped that went into rows.

Now you'll see that return quantity duplicated every single row but you'll still see all 10 territories

because all 10 territories generated orders.

And I think you see where this is going we can pull territory key out drill into our returns table grab

the returns table version of the territory key and there you see that order quantities are now duplicated

returned quantities are correct.

And those two territories that did not register returns don't even show up here in the Matrix because

they were filtered out from the start.

So hopefully this demo starts to give you a sense of why filter flow is so important to understand making

sure that we have a crystal clear understanding of our models filter flow will be critical for making

sure that the views we're creating the filters we're using and the numbers that we're showing are 100

percent accurate.