Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,810 --> 00:00:06,180
Now when it comes to filters there's one important difference between power be-I and power pivot for
2
00:00:06,180 --> 00:00:13,510
Excel and that's that power be I actually enables you to create two way or bi directional filters.
3
00:00:13,590 --> 00:00:19,230
So let's take a look at that familiar three table model just like we've seen before with one exception
4
00:00:19,680 --> 00:00:26,460
that relationship between territory look up and sales data now contains a bi directional filter.
5
00:00:26,460 --> 00:00:33,120
So by updating the filter direction in that relationship between sales and territory from single to
6
00:00:33,120 --> 00:00:38,940
both We've essentially allowed filter context to flow in either direction.
7
00:00:38,940 --> 00:00:44,940
What that means is that filter context could flow from the territory look up down to the sales data
8
00:00:45,210 --> 00:00:52,200
just like normal but it could also flow from the sales data up to that territory look up and then from
9
00:00:52,200 --> 00:00:54,510
there down to returns.
10
00:00:54,510 --> 00:01:01,500
So what we'll see now is that we can control and filter returns data using filter context from within
11
00:01:01,500 --> 00:01:07,530
the sales data table which is something that we cannot do using the one directional filters in the last
12
00:01:07,530 --> 00:01:08,410
lecture.
13
00:01:08,430 --> 00:01:10,340
So let me show you what that looks like.
14
00:01:10,350 --> 00:01:10,930
Here we go.
15
00:01:10,980 --> 00:01:12,470
Same three table model.
16
00:01:12,600 --> 00:01:17,920
The only difference is that one bidirectional filter between territory and sales.
17
00:01:18,360 --> 00:01:24,480
And because we've been able to cross filtering between those two tables we can now see correct values
18
00:01:24,750 --> 00:01:28,890
using the territory key fields from either one of those tables.
19
00:01:28,890 --> 00:01:35,640
So again just like before using the territory key from the Look-Up will yield proper accurate values
20
00:01:35,640 --> 00:01:38,370
for both order quantity and returns quantity.
21
00:01:38,490 --> 00:01:45,120
Since it's upstream and connected what's new now is that pulling in the Territory key from the sales
22
00:01:45,120 --> 00:01:52,440
data will not only return proper order quantities as you'd expect but that filter context is now able
23
00:01:52,440 --> 00:01:58,860
to travel up to the Territory Look-Up which then filters territory based on which ones generated sales
24
00:01:59,400 --> 00:02:05,190
and then that filter context in turn passes down to the returns data table which filters accordingly
25
00:02:05,190 --> 00:02:06,050
as well.
26
00:02:06,450 --> 00:02:12,480
So we've essentially created a way to transmit filter contacts from within the sales data table all
27
00:02:12,480 --> 00:02:15,970
the way to the returns data through the territory Look-Up.
28
00:02:16,020 --> 00:02:22,800
Now as you'd expect if we pull in territory key from returns same story as before that filter context
29
00:02:22,830 --> 00:02:29,580
is trapped in the returns data table because we've got that one way relationship between territory and
30
00:02:29,580 --> 00:02:30,870
returns.
31
00:02:30,870 --> 00:02:36,570
So let's keep this idea going a little bit and consider a similar model with one difference which is
32
00:02:36,720 --> 00:02:42,900
bidirectional across philtre now exists on the relationship between territory and returns instead of
33
00:02:42,900 --> 00:02:44,090
sales.
34
00:02:44,190 --> 00:02:50,910
What we see this time is that as always the territory Look-Up version of territory key returns the proper
35
00:02:50,910 --> 00:02:58,410
values the territory key pulled from the sales data table returns incorrect return quantities because
36
00:02:58,410 --> 00:03:02,370
now that filter context is trapped within the sales data table.
37
00:03:02,790 --> 00:03:08,710
But here's where it gets interesting when we pull in the Territory key from the returns data table.
38
00:03:08,850 --> 00:03:14,950
The returns are accurate which we know be the case but check out territory key and order quantity.
39
00:03:15,030 --> 00:03:22,080
At first glance things look good but remember that territory is 2 and 3 don't even exist in the returns
40
00:03:22,080 --> 00:03:23,130
table.
41
00:03:23,190 --> 00:03:30,270
So since no information about territory 2 or 3 ever gets passed up to that territory Look-Up then they'll
42
00:03:30,270 --> 00:03:34,760
subsequently get filtered out of the sales data table as well.
43
00:03:34,890 --> 00:03:41,520
So the filter context that we created by using this territory key field from the returns table is properly
44
00:03:41,520 --> 00:03:45,400
flowing up to the Territory look up and then down to the sales table.
45
00:03:45,450 --> 00:03:51,630
But in doing so it's yielding misleading data and this is dangerous because this type of issue can be
46
00:03:51,630 --> 00:03:56,410
very very difficult to detect even if you know your data model inside out.
47
00:03:56,640 --> 00:04:03,120
So had we not gone through this exercise we may never have realized that territory to actually drove
48
00:04:03,360 --> 00:04:09,450
an order quantity of 40 and territory three drove an order quantity of 30 but because of the way our
49
00:04:09,450 --> 00:04:14,780
filter context flows those values get completely stripped out of this view.
50
00:04:15,180 --> 00:04:20,640
So that brings me to my next point which is a word of warning about two way filters.
51
00:04:20,640 --> 00:04:26,160
The fact is you've got to use two way filters carefully and only when necessary.
52
00:04:26,160 --> 00:04:31,260
Now there are cases where two filters are helpful or sometimes even necessary.
53
00:04:31,350 --> 00:04:36,120
Like for instance if you want to intentionally filter or look up based on a data table kind of like
54
00:04:36,120 --> 00:04:37,160
we just showed.
55
00:04:37,350 --> 00:04:43,770
But the thing is two way filters really aren't recommended for models with multiple data tables exactly
56
00:04:43,770 --> 00:04:45,850
like the one that we're working with.
57
00:04:45,850 --> 00:04:52,020
And in fact if you try to use multiple two way filters in a more complex model like this you run the
58
00:04:52,020 --> 00:04:57,960
risk of creating something called an ambiguous relationship because you're essentially introducing multiple
59
00:04:57,960 --> 00:05:00,070
filter paths between tables.
60
00:05:00,240 --> 00:05:02,450
Could potentially contradict each other.
61
00:05:02,850 --> 00:05:08,250
So consider this data model shown here on the left got the same three tables that we've been looking
62
00:05:08,250 --> 00:05:12,030
at plus now a product lookup table as well.
63
00:05:12,030 --> 00:05:18,000
And you'll notice that there are two bi directional filters here from territory look up down to sales
64
00:05:18,120 --> 00:05:19,830
and down to returns.
65
00:05:19,840 --> 00:05:26,130
What's important to call out here is that only one of the relationships from the product lookup table
66
00:05:26,400 --> 00:05:33,150
to the data tables can be active at one time if you tried to activate both you'd see an error message
67
00:05:33,150 --> 00:05:34,420
like this.
68
00:05:34,440 --> 00:05:40,610
It basically says you can't create a direct active relationship here because it introduces ambiguity.
69
00:05:40,650 --> 00:05:45,780
So you've got to either deactivate or delete a relationship or change one of your bidirectional filters
70
00:05:46,130 --> 00:05:47,130
to one way.
71
00:05:47,290 --> 00:05:52,010
So very very confusing and not really intuitive at first glance.
72
00:05:52,230 --> 00:05:57,750
So I'm going to take a stab at explaining this to you in plain English the best way that I know how
73
00:05:58,080 --> 00:06:02,910
but this is a little bit nuanced so it might be one of those cases where you're going to need to step
74
00:06:02,910 --> 00:06:08,610
back pause the video maybe re watch it a few times before it starts to really click.
75
00:06:08,610 --> 00:06:10,840
So here's the deal.
76
00:06:11,280 --> 00:06:16,710
Let's imagine that we want to filter our data for a specific product and to start all we care about
77
00:06:16,710 --> 00:06:22,560
is the relationship between product lookup and returns which is currently active in the model that we're
78
00:06:22,560 --> 00:06:23,500
seeing here.
79
00:06:23,820 --> 00:06:30,780
So for the sake of example we want to filter down our data to just the product named racing helmet.
80
00:06:31,010 --> 00:06:31,690
OK.
81
00:06:31,950 --> 00:06:39,090
So that filter context product name equals racing helmet passes down flows downstream right to the returns
82
00:06:39,090 --> 00:06:39,790
table.
83
00:06:39,900 --> 00:06:47,160
And as a result that returns data table filters down to only rows where that racing helmet was returned.
84
00:06:47,160 --> 00:06:54,870
Now if that helmet was only returned in territories 7 and 8 because territory keys 7 and 8 were the
85
00:06:54,870 --> 00:06:58,500
only ones remaining in that filtered down returns table.
86
00:06:58,500 --> 00:07:04,980
That's the context that gets passed up to the Territory look up through that bidirectional filter and
87
00:07:05,000 --> 00:07:11,220
tells that territory lookup table hey there are only two territories that exist in the universe 7 and
88
00:07:11,230 --> 00:07:12,170
8.
89
00:07:12,240 --> 00:07:17,910
Now shifting gears to the relationship between the product Look-Up and the sales table let's go through
90
00:07:17,910 --> 00:07:19,720
that similar thought process here.
91
00:07:19,850 --> 00:07:27,450
I imagine we could also activate that relationship that way we can say our product name is racing helmet
92
00:07:28,170 --> 00:07:34,020
lets allow that filter context to flow down to the sales data table just like it flowed down to return's
93
00:07:34,560 --> 00:07:34,830
set.
94
00:07:34,830 --> 00:07:41,820
Now this time the sales data table is going to collapse and filter down to only rows showing records
95
00:07:41,820 --> 00:07:44,460
where that racing helmet was sold.
96
00:07:44,460 --> 00:07:53,460
And if that racing helmet was sold in stores 7 and 8 as well as 1 2 and 3 then that filter context also
97
00:07:53,460 --> 00:07:59,380
would pass up to the Territory look up through that bidirectional filter and tell a territory look up
98
00:07:59,870 --> 00:08:03,540
a there are five territories that exist in the universe.
99
00:08:03,930 --> 00:08:06,470
1 2 3 7 and 8.
100
00:08:06,780 --> 00:08:14,520
So what we've done in this hypothetical situation is past two conflicting sets of filter context to
101
00:08:14,520 --> 00:08:21,000
that same territory lookup table the returns data table is telling the territory Look-Up that the only
102
00:08:21,000 --> 00:08:23,070
territories that exists are 7 and 8.
103
00:08:23,310 --> 00:08:29,940
And the sales data table is passing along different information and saying that territory is 1 2 3 7
104
00:08:29,940 --> 00:08:33,250
and 8 are the 5 territories that exist.
105
00:08:33,300 --> 00:08:39,720
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
106
00:08:39,720 --> 00:08:45,570
power be-I and confusing that territory lookup table in a way that prevents our model from producing
107
00:08:45,570 --> 00:08:48,050
or yielding meaningful values.
108
00:08:48,330 --> 00:08:55,620
So as a result power be-I forces you to keep one of those relationships inactive so that you can't possibly
109
00:08:55,620 --> 00:08:59,820
pass conflicting filter contexts to the same table.
110
00:08:59,850 --> 00:09:05,160
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
111
00:09:05,550 --> 00:09:07,250
in our particular model.
112
00:09:07,500 --> 00:09:13,380
And as a protip rule of thumb I'd recommend that you try to design your models with one way filters
113
00:09:13,680 --> 00:09:19,920
and one to many carnality especially as you're first learning unless more complex relationships are
114
00:09:19,920 --> 00:09:20,760
necessary.
115
00:09:21,530 --> 00:09:26,230
So that let's take a quick peek at what this actually looks like in the power be-I environment.
116
00:09:27,270 --> 00:09:27,500
All right.
117
00:09:27,510 --> 00:09:32,880
So in the adventure we're report I'm going to start in the report view first and as a reminder I've
118
00:09:32,880 --> 00:09:34,210
got my matrix here.
119
00:09:34,350 --> 00:09:39,390
And one thing that you can do is actually hover over the field that we've pulled into a row list to
120
00:09:39,390 --> 00:09:43,460
confirm exactly which version of territory key we're looking at.
121
00:09:43,620 --> 00:09:47,430
In this case we can see that it's coming from the A.W. returns table.
122
00:09:47,430 --> 00:09:52,890
So remember that we've got the key from our returns table and we're getting in correct order quantities
123
00:09:52,890 --> 00:10:00,110
here where we jump to our relationships view and we find that relationship from returns to territory
124
00:10:00,120 --> 00:10:08,140
is we can double click to add it drill into this cross filter direction change it to both and press
125
00:10:08,170 --> 00:10:09,330
OK.
126
00:10:09,490 --> 00:10:13,050
See that little arrow switch from one direction to both.
127
00:10:13,300 --> 00:10:16,070
Now and we return to the report.
128
00:10:16,120 --> 00:10:17,920
This is exactly what we had talked about.
129
00:10:17,920 --> 00:10:24,840
So now order quantities look to be correct and to the untrained eye the initial reaction is to flash
130
00:10:24,840 --> 00:10:27,620
a thumbs up and say OK we're good to go.
131
00:10:27,780 --> 00:10:32,240
You know we've got order quantity these broken down territories we've got returns by territories.
132
00:10:32,440 --> 00:10:36,630
But again remember the problem here is that we are missing data.
133
00:10:36,660 --> 00:10:43,060
There are actual valid order quantity values for territories 2 and 3 that aren't showing up because
134
00:10:43,060 --> 00:10:51,890
by using territory key from the returns table we filtered them out from step 1 so let's go back to relationships.
135
00:10:52,550 --> 00:10:56,980
And then double click that same relationship change it back to a single person.
136
00:10:56,980 --> 00:10:57,440
OK.
137
00:10:58,970 --> 00:11:02,930
And I'm going to find the relationship from sales to territories.
138
00:11:03,020 --> 00:11:12,590
It is going to make this one bi directional press OK head back to report.
139
00:11:13,120 --> 00:11:19,360
Pull out the territory key from returns and put in the Territory key from sales.
140
00:11:19,660 --> 00:11:25,780
And here's what we see in this case we see the correct values from both but only because each of the
141
00:11:25,780 --> 00:11:30,970
10 territories registered a sale or showed up in the sales data table.
142
00:11:30,970 --> 00:11:32,780
So important nuance there.
143
00:11:33,100 --> 00:11:39,880
These numbers could very well be incorrect as well had there been one of the 10 territories that didn't
144
00:11:39,880 --> 00:11:41,200
show up in the sales data.
145
00:11:41,210 --> 00:11:46,830
We'd have the same issue that we just had with territory 2 and 3 in the return table.
146
00:11:46,900 --> 00:11:53,770
So last little demo here Bacary turns if I go back to the relationship between territories and the returns
147
00:11:53,770 --> 00:12:00,970
table double click watch what happens when I try to click both here I get this error that says you're
148
00:12:00,970 --> 00:12:03,280
trying to create an ambiguous relationship.
149
00:12:03,340 --> 00:12:09,310
You can only have one filtering path between tables in a data model so you can't even press ok have
150
00:12:09,310 --> 00:12:15,490
to deactivate one of those existing relationships or unless at least one of those filters was changed
151
00:12:15,490 --> 00:12:17,680
back to single direction.
152
00:12:17,690 --> 00:12:23,630
So for the sake of our model I only want one way filters because I don't want to introduce the potential
153
00:12:23,630 --> 00:12:29,210
headaches that we just talked about when we go back to my sales territory territories relationship change
154
00:12:29,210 --> 00:12:30,830
that back to single as well.
155
00:12:32,440 --> 00:12:33,520
And we go.
156
00:12:33,550 --> 00:12:34,970
And now just for good measure.
157
00:12:34,990 --> 00:12:42,700
Let's go back to report pull that territory key from sales out and let's grab the proper one from our
158
00:12:42,700 --> 00:12:45,510
lookup table and drop it in rows.
159
00:12:45,820 --> 00:12:49,230
So I know that was probably a little bit confusing at first.
160
00:12:49,240 --> 00:12:53,240
Again I'd recommend re watching this a few times until it starts to make more sense.
161
00:12:53,260 --> 00:12:59,890
But the one big takeaway from this entire conversation is that for our purposes we will not be using
162
00:12:59,890 --> 00:13:05,860
two filters and therefore we will never pull in foreign key fields from our data tables.
163
00:13:05,920 --> 00:13:07,640
So it's going to keep things simple.
164
00:13:07,660 --> 00:13:10,560
It's going to keep things accurate from here on out.
165
00:13:10,630 --> 00:13:15,640
Every time we filter our data we're going to do so using fields from our lookup tables.
18401
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.