1
00:00:00,300 --> 00:00:06,060
We've officially reached measures now the really deep part of power, pivot and power by that will astound,

2
00:00:06,060 --> 00:00:09,930
amaze, confound and positively anger you on occasion.

3
00:00:10,500 --> 00:00:11,130
But why?

4
00:00:11,610 --> 00:00:14,160
So far, our measures have been incredibly simple.

5
00:00:14,520 --> 00:00:19,430
The formula we've used is simply equal to some of the field and it just seems to work.

6
00:00:20,490 --> 00:00:21,980
But think about that for a moment.

7
00:00:22,500 --> 00:00:24,720
We have a bunch of conditions in our pivot table.

8
00:00:24,930 --> 00:00:28,940
How can our power pivot do these sums if I haven't given it conditions?

9
00:00:29,580 --> 00:00:34,440
What if I want more complicated conditions such as a cumulative running total of our profit?

10
00:00:34,830 --> 00:00:38,520
Ultimately, all of this is going to boil down to context.

11
00:00:38,940 --> 00:00:44,400
Every tax formula calculates based upon the data context in which it is encapsulated.

12
00:00:45,150 --> 00:00:48,450
This pivot table starts out with thirty six context sets.

13
00:00:49,080 --> 00:00:52,100
Once we expand it like this, it's up to over 90.

14
00:00:52,770 --> 00:00:56,370
I keep saying this word context, but what what is it I'm talking about?

15
00:00:57,000 --> 00:01:02,760
Well, the context refers to the data universe as it relates to a particular value cell in our pivot

16
00:01:02,760 --> 00:01:06,390
table or in power by a particular point in a chart.

17
00:01:07,050 --> 00:01:12,180
That means that if we look at this particular cell, the context is all of the data that's available

18
00:01:12,360 --> 00:01:18,090
that has a relationship to Kroger in the stores table and relationship to the quarter three thirty one

19
00:01:18,090 --> 00:01:20,370
twenty nineteen in our calendar table.

20
00:01:22,520 --> 00:01:28,030
So think about it is starting with all of the data and then it gets filtered down by relationships,

21
00:01:28,580 --> 00:01:31,580
note that any unrelated tables would not be filtered.

22
00:01:31,700 --> 00:01:32,830
This can come in handy.

23
00:01:33,560 --> 00:01:38,990
There are ways to modify context within a DACS formula to create alternative context implications for

24
00:01:38,990 --> 00:01:40,090
a given calculation.

25
00:01:40,730 --> 00:01:45,050
But before we figure out how to do that, let's identify an example where we might need it.

26
00:01:46,970 --> 00:01:52,100
If we think about how our relationship definitions work, they limit tables to those entries where the

27
00:01:52,100 --> 00:01:53,540
target values match up.

28
00:01:54,020 --> 00:01:59,060
In this case, we want our measure to capture all of the values that are less than are equal to a given

29
00:01:59,060 --> 00:02:00,680
date, not just equal to it.

30
00:02:01,550 --> 00:02:06,620
Since our relationships aren't up to the task, we need to create a more complicated measure that takes

31
00:02:06,620 --> 00:02:10,640
into account some of our relationships, but not all of our relationships.

32
00:02:10,880 --> 00:02:13,910
And then we need to define a customer relationship on top of it.

33
00:02:14,900 --> 00:02:18,950
This sounds pretty difficult, but if you think about how we might do this with a service function,

34
00:02:19,250 --> 00:02:22,220
it's only a mile change with power pivot.

35
00:02:22,410 --> 00:02:27,740
It's a little bit more complex in that there are two steps to it and unfiltered step and then a filter

36
00:02:27,740 --> 00:02:30,020
step which will be tied to our measure.

37
00:02:31,430 --> 00:02:37,160
All of this is going to boil down to understanding and managing the measures context, so let's really

38
00:02:37,160 --> 00:02:38,840
dive into what context is.

39
00:02:39,080 --> 00:02:40,040
Context isn't.

40
00:02:41,000 --> 00:02:48,350
The hardest part of context is that it's very contextual, obviously the context for a measure is computation

41
00:02:48,590 --> 00:02:51,900
will be based upon where the calculation occurs within an output.

42
00:02:52,610 --> 00:02:55,820
So let's look at our pivot tables for a few examples.

43
00:02:56,850 --> 00:03:02,160
Each of the cells in our pivot table can be imagined to have a full series of tables behind them, each

44
00:03:02,160 --> 00:03:04,290
with a different context related to them.

45
00:03:04,830 --> 00:03:09,990
The cell where three thirty one twenty, nineteen and Kröger intersect has access to all of these table

46
00:03:09,990 --> 00:03:14,280
data sets, all of them filtered to three thirty one twenty, nineteen and Kröger.

47
00:03:15,380 --> 00:03:21,140
Moving to Tom Thumb and we would have Tom Thumb is the filter, so when we request the sum of the purchase

48
00:03:21,140 --> 00:03:27,710
orders or the sales field within the context of that cell, it's calculating the entire sum from that

49
00:03:27,710 --> 00:03:28,160
table.

50
00:03:28,570 --> 00:03:34,100
There's no if computation involved at this stage anymore, which seems odd since we think about it as

51
00:03:34,100 --> 00:03:39,140
being a sum if computation some if the is Kroger and the quarters.

52
00:03:39,140 --> 00:03:41,150
Three thirty one twenty nineteen.

53
00:03:42,830 --> 00:03:46,020
So in this calculation occurs, it's simple, it's fast, it's efficient.

54
00:03:46,370 --> 00:03:51,810
So this raises a question, are there ways to manipulate the context within the computation directly?

55
00:03:52,790 --> 00:03:59,310
Yes, we can modify our measures so that we can manipulate the context in quite a few different ways.

56
00:03:59,570 --> 00:04:02,780
So let's discuss some of the general concepts and functions involved.

57
00:04:03,450 --> 00:04:08,060
The most important function to know is the calculate function, which is where we'll start from.

58
00:04:10,360 --> 00:04:16,300
The calculator function takes some type of computational function or command, such as are some function,

59
00:04:16,480 --> 00:04:21,430
or it can take another measure and it applies one or more filter modifications to it.

60
00:04:22,150 --> 00:04:26,770
In this case, I have simply written a filter that filters down to the store in the sales table equals

61
00:04:26,770 --> 00:04:27,460
the store value.

62
00:04:27,880 --> 00:04:32,320
Obviously this will calculate the exact same results as before, which I can see when I add it to the

63
00:04:32,320 --> 00:04:32,970
pivot table.

64
00:04:33,820 --> 00:04:36,360
But what if we wanted to create a weekend sales total?

65
00:04:36,820 --> 00:04:41,370
Obviously we could add the weekend identifier to our table and modify the context that way.

66
00:04:42,160 --> 00:04:47,440
But in this case, I'm going to go into the measure and modify it so that it has a filter specifically

67
00:04:47,440 --> 00:04:48,310
to weekend.

68
00:04:49,450 --> 00:04:53,610
This filter function adds filters, but we can remove them as well.

69
00:04:54,740 --> 00:04:59,900
There are quite a few different filter removal commands, many of them start with all I'm going to use

70
00:04:59,900 --> 00:05:01,670
the general all function here.

71
00:05:01,940 --> 00:05:07,220
When you use the URL function with a specified field or table, it will remove any filters that are

72
00:05:07,220 --> 00:05:09,610
applied specifically to that table.

73
00:05:10,100 --> 00:05:15,710
That's very odd language to say filters applied to that specific table.

74
00:05:16,400 --> 00:05:19,700
Well, this is where our dimensional tables become very important.

75
00:05:20,240 --> 00:05:24,350
Envision our little relationship here between calendar stores and sales.

76
00:05:24,950 --> 00:05:29,560
When we have store selected as Kroger and our quarter selected is three thirty one twenty nineteen.

77
00:05:30,080 --> 00:05:32,690
We're not directly filtering our sales table.

78
00:05:32,990 --> 00:05:36,980
We're applying our filters to the stores table into our calendar table.

79
00:05:37,250 --> 00:05:42,980
And then we're using these filtered tables and the relationships between these tables with the sales

80
00:05:42,980 --> 00:05:45,020
table in order to get the proper data.

81
00:05:46,620 --> 00:05:53,070
So let's say I wanted to remove my date filter limitation and get just the total for stores across all

82
00:05:53,070 --> 00:05:53,500
dates.

83
00:05:54,180 --> 00:06:00,210
So in other words, I want to ignore the date filter while using the calculate function and the sum

84
00:06:00,210 --> 00:06:00,870
of sales.

85
00:06:01,140 --> 00:06:04,780
I can add a filter that says use all from the calendar table.

86
00:06:05,580 --> 00:06:11,880
Now, with this measure, the context of the cells within the pivot table will always filter only by

87
00:06:11,880 --> 00:06:12,380
store.

88
00:06:13,260 --> 00:06:18,060
I can remove quarter from the pivot table and we can confirm the totals real quick.

89
00:06:18,720 --> 00:06:24,360
As you can see, this all dates version calculates the total ignoring the date field just as we've liked.

90
00:06:25,110 --> 00:06:29,370
Pretty nifty, but what if we wanted to refill targets for a cumulative approach?

91
00:06:30,870 --> 00:06:33,900
This is where we'll really take advantage of some context.

92
00:06:33,900 --> 00:06:34,440
Magic.

93
00:06:35,310 --> 00:06:40,430
If we go look at this next measure, we're going to calculate our sum and apply a compound filter.

94
00:06:40,980 --> 00:06:46,090
The first step in the filter says to give us all of our calendar table, stripping away the calendar

95
00:06:46,090 --> 00:06:46,800
restrictions.

96
00:06:47,070 --> 00:06:52,770
And then it adds a new filter to our calendar table that says to pull the calendar date when it is less

97
00:06:52,770 --> 00:06:54,870
than the maximum calendar date.

98
00:06:55,530 --> 00:06:58,580
That what that there's no way that doesn't make sense.

99
00:06:58,620 --> 00:07:00,750
That's just going to pull all of the calendar dates.

100
00:07:00,780 --> 00:07:01,230
Right.

101
00:07:02,470 --> 00:07:04,360
Let's go at it to the table and see.

102
00:07:06,000 --> 00:07:09,780
When we add it, it looks like it's a running cumulative total, but why?

103
00:07:10,670 --> 00:07:16,430
Context, as always, if we look at our measure and we dig into this filter function a bit.

104
00:07:17,860 --> 00:07:25,270
We could figure this out, what would we get if we calculated our Max calendar date while in this three

105
00:07:25,270 --> 00:07:29,320
thirty one twenty 19 cell independent of all other factors?

106
00:07:30,550 --> 00:07:36,310
Well, because we have relationships, the largest state within this context would be three thirty one

107
00:07:36,310 --> 00:07:37,280
twenty nineteen.

108
00:07:37,750 --> 00:07:42,100
That is the max calendar date isn't the maximum of the table in total.

109
00:07:42,760 --> 00:07:44,950
It's the maximum of the calendar date.

110
00:07:45,130 --> 00:07:51,760
With respect to the context, outside of the calculated function, if we moved out to sell our new max

111
00:07:51,760 --> 00:07:53,620
date would be six thirty, twenty, nineteen.

112
00:07:53,890 --> 00:07:59,860
Since we're now in a different context, giving us a new cumulative total, we'll dig into this in a

113
00:07:59,860 --> 00:08:01,720
little bit more detail as we continue.

114
00:08:02,200 --> 00:08:07,690
But with that little bit of context magic, we've created a new, more flexible measure that accumulates

115
00:08:07,690 --> 00:08:08,790
our sales together.

116
00:08:09,580 --> 00:08:14,560
Join me in our next lesson as we start changing our measures together and we'll continue discussing

117
00:08:14,650 --> 00:08:18,160
and expanding on the beauty of tax context.
