Original subtitles

Now when it comes to filters there's one important difference between power be-I and power pivot for

Excel and that's that power be I actually enables you to create two way or bi directional filters.

So let's take a look at that familiar three table model just like we've seen before with one exception

that relationship between territory look up and sales data now contains a bi directional filter.

So by updating the filter direction in that relationship between sales and territory from single to

both We've essentially allowed filter context to flow in either direction.

What that means is that filter context could flow from the territory look up down to the sales data

just like normal but it could also flow from the sales data up to that territory look up and then from

there down to returns.

So what we'll see now is that we can control and filter returns data using filter context from within

the sales data table which is something that we cannot do using the one directional filters in the last

lecture.

So let me show you what that looks like.

Here we go.

Same three table model.

The only difference is that one bidirectional filter between territory and sales.

And because we've been able to cross filtering between those two tables we can now see correct values

using the territory key fields from either one of those tables.

So again just like before using the territory key from the Look-Up will yield proper accurate values

for both order quantity and returns quantity.

Since it's upstream and connected what's new now is that pulling in the Territory key from the sales

data will not only return proper order quantities as you'd expect but that filter context is now able

to travel up to the Territory Look-Up which then filters territory based on which ones generated sales

and then that filter context in turn passes down to the returns data table which filters accordingly

as well.

So we've essentially created a way to transmit filter contacts from within the sales data table all

the way to the returns data through the territory Look-Up.

Now as you'd expect if we pull in territory key from returns same story as before that filter context

is trapped in the returns data table because we've got that one way relationship between territory and

returns.

So let's keep this idea going a little bit and consider a similar model with one difference which is

bidirectional across philtre now exists on the relationship between territory and returns instead of

sales.

What we see this time is that as always the territory Look-Up version of territory key returns the proper

values the territory key pulled from the sales data table returns incorrect return quantities because

now that filter context is trapped within the sales data table.

But here's where it gets interesting when we pull in the Territory key from the returns data table.

The returns are accurate which we know be the case but check out territory key and order quantity.

At first glance things look good but remember that territory is 2 and 3 don't even exist in the returns

table.

So since no information about territory 2 or 3 ever gets passed up to that territory Look-Up then they'll

subsequently get filtered out of the sales data table as well.

So the filter context that we created by using this territory key field from the returns table is properly

flowing up to the Territory look up and then down to the sales table.

But in doing so it's yielding misleading data and this is dangerous because this type of issue can be

very very difficult to detect even if you know your data model inside out.

So had we not gone through this exercise we may never have realized that territory to actually drove

an order quantity of 40 and territory three drove an order quantity of 30 but because of the way our

filter context flows those values get completely stripped out of this view.

So that brings me to my next point which is a word of warning about two way filters.

The fact is you've got to use two way filters carefully and only when necessary.

Now there are cases where two filters are helpful or sometimes even necessary.

Like for instance if you want to intentionally filter or look up based on a data table kind of like

we just showed.

But the thing is two way filters really aren't recommended for models with multiple data tables exactly

like the one that we're working with.

And in fact if you try to use multiple two way filters in a more complex model like this you run the

risk of creating something called an ambiguous relationship because you're essentially introducing multiple

filter paths between tables.

Could potentially contradict each other.

So consider this data model shown here on the left got the same three tables that we've been looking

at plus now a product lookup table as well.

And you'll notice that there are two bi directional filters here from territory look up down to sales

and down to returns.

What's important to call out here is that only one of the relationships from the product lookup table

to the data tables can be active at one time if you tried to activate both you'd see an error message

like this.

It basically says you can't create a direct active relationship here because it introduces ambiguity.

So you've got to either deactivate or delete a relationship or change one of your bidirectional filters

to one way.

So very very confusing and not really intuitive at first glance.

So I'm going to take a stab at explaining this to you in plain English the best way that I know how

but this is a little bit nuanced so it might be one of those cases where you're going to need to step

back pause the video maybe re watch it a few times before it starts to really click.

So here's the deal.

Let's imagine that we want to filter our data for a specific product and to start all we care about

is the relationship between product lookup and returns which is currently active in the model that we're

seeing here.

So for the sake of example we want to filter down our data to just the product named racing helmet.

OK.

So that filter context product name equals racing helmet passes down flows downstream right to the returns

table.

And as a result that returns data table filters down to only rows where that racing helmet was returned.

Now if that helmet was only returned in territories 7 and 8 because territory keys 7 and 8 were the

only ones remaining in that filtered down returns table.

That's the context that gets passed up to the Territory look up through that bidirectional filter and

tells that territory lookup table hey there are only two territories that exist in the universe 7 and

8.

Now shifting gears to the relationship between the product Look-Up and the sales table let's go through

that similar thought process here.

I imagine we could also activate that relationship that way we can say our product name is racing helmet

lets allow that filter context to flow down to the sales data table just like it flowed down to return's

set.

Now this time the sales data table is going to collapse and filter down to only rows showing records

where that racing helmet was sold.

And if that racing helmet was sold in stores 7 and 8 as well as 1 2 and 3 then that filter context also

would pass up to the Territory look up through that bidirectional filter and tell a territory look up

a there are five territories that exist in the universe.

1 2 3 7 and 8.

So what we've done in this hypothetical situation is past two conflicting sets of filter context to

that same territory lookup table the returns data table is telling the territory Look-Up that the only

territories that exists are 7 and 8.

And the sales data table is passing along different information and saying that territory is 1 2 3 7

and 8 are the 5 territories that exist.

So that's what we mean by ambiguity both can't be true at the same time and all we end up doing is confusing

power be-I and confusing that territory lookup table in a way that prevents our model from producing

or yielding meaningful values.

So as a result power be-I forces you to keep one of those relationships inactive so that you can't possibly

pass conflicting filter contexts to the same table.

So at the end of the day that's really just a long winded way of saying we don't want to use two filters

in our particular model.

And as a protip rule of thumb I'd recommend that you try to design your models with one way filters

and one to many carnality especially as you're first learning unless more complex relationships are

necessary.

So that let's take a quick peek at what this actually looks like in the power be-I environment.

All right.

So in the adventure we're report I'm going to start in the report view first and as a reminder I've

got my matrix here.

And one thing that you can do is actually hover over the field that we've pulled into a row list to

confirm exactly which version of territory key we're looking at.

In this case we can see that it's coming from the A.W. returns table.

So remember that we've got the key from our returns table and we're getting in correct order quantities

here where we jump to our relationships view and we find that relationship from returns to territory

is we can double click to add it drill into this cross filter direction change it to both and press

OK.

See that little arrow switch from one direction to both.

Now and we return to the report.

This is exactly what we had talked about.

So now order quantities look to be correct and to the untrained eye the initial reaction is to flash

a thumbs up and say OK we're good to go.

You know we've got order quantity these broken down territories we've got returns by territories.

But again remember the problem here is that we are missing data.

There are actual valid order quantity values for territories 2 and 3 that aren't showing up because

by using territory key from the returns table we filtered them out from step 1 so let's go back to relationships.

And then double click that same relationship change it back to a single person.

OK.

And I'm going to find the relationship from sales to territories.

It is going to make this one bi directional press OK head back to report.

Pull out the territory key from returns and put in the Territory key from sales.

And here's what we see in this case we see the correct values from both but only because each of the

10 territories registered a sale or showed up in the sales data table.

So important nuance there.

These numbers could very well be incorrect as well had there been one of the 10 territories that didn't

show up in the sales data.

We'd have the same issue that we just had with territory 2 and 3 in the return table.

So last little demo here Bacary turns if I go back to the relationship between territories and the returns

table double click watch what happens when I try to click both here I get this error that says you're

trying to create an ambiguous relationship.

You can only have one filtering path between tables in a data model so you can't even press ok have

to deactivate one of those existing relationships or unless at least one of those filters was changed

back to single direction.

So for the sake of our model I only want one way filters because I don't want to introduce the potential

headaches that we just talked about when we go back to my sales territory territories relationship change

that back to single as well.

And we go.

And now just for good measure.

Let's go back to report pull that territory key from sales out and let's grab the proper one from our

lookup table and drop it in rows.

So I know that was probably a little bit confusing at first.

Again I'd recommend re watching this a few times until it starts to make more sense.

But the one big takeaway from this entire conversation is that for our purposes we will not be using

two filters and therefore we will never pull in foreign key fields from our data tables.

So it's going to keep things simple.

It's going to keep things accurate from here on out.

Every time we filter our data we're going to do so using fields from our lookup tables.