1
00:00:01,200 --> 00:00:07,050
As I discussed in the last lesson, calculated columns will extend tables by adding new column values

2
00:00:07,050 --> 00:00:08,040
for consideration.

3
00:00:08,520 --> 00:00:13,650
There are many times and many places where you're likely to want a new column or two that expands on

4
00:00:13,650 --> 00:00:15,900
the day to present within your existing tables.

5
00:00:16,770 --> 00:00:21,840
Sometimes this might be a combination of fields to construct a new calculated numeric value.

6
00:00:21,840 --> 00:00:27,750
Other times, and most commonly in my cases, this will be to use logic to extend dimensional tables.

7
00:00:28,020 --> 00:00:32,410
Let's start with an example from our grocery store data within our dates table.

8
00:00:32,430 --> 00:00:36,600
There's one particular aggregation date that is missing that I use pretty extensively.

9
00:00:36,930 --> 00:00:39,270
The quarter end date in Excel.

10
00:00:39,270 --> 00:00:44,580
You typically create this calculation using MAAD and the end of month function to calculate the quarter

11
00:00:44,910 --> 00:00:45,830
in power pivot.

12
00:00:45,840 --> 00:00:48,960
I can click on the ADD column and began adding my logic.

13
00:00:49,230 --> 00:00:54,240
That's happens to have a friendly end of quarter function that I can use, which then references my

14
00:00:54,240 --> 00:00:54,890
date field.

15
00:00:55,320 --> 00:00:58,680
Notice that as I type I get lots of and teletext help.

16
00:00:59,070 --> 00:01:04,120
The DAX Formula Bar will help you write so much of your logic this way between the extensive tool tips,

17
00:01:04,470 --> 00:01:08,160
access to the functions and the quick completion with the tab button.

18
00:01:08,520 --> 00:01:13,770
Once I've written the formula very much like you would in Excel, I can press, enter and have a brand

19
00:01:13,770 --> 00:01:15,210
new informational column.

20
00:01:15,390 --> 00:01:17,640
I recommend renaming columns like this.

21
00:01:17,670 --> 00:01:23,070
I call this one quarter and I'll even change its format type to show just the date and move it further

22
00:01:23,070 --> 00:01:23,610
to the left.

23
00:01:23,610 --> 00:01:28,140
And my table by dimensional table will now have a quarterly filter that I can apply.

24
00:01:28,410 --> 00:01:34,020
Calculated columns can also reference other calculated columns just in case you need multi-step formulas

25
00:01:34,200 --> 00:01:37,220
or want to be able to see each step in the process.

26
00:01:37,950 --> 00:01:43,680
In this case, suppose I'm doing some time series analysis by quarter and want the quarter number associated

27
00:01:43,680 --> 00:01:44,460
with each date.

28
00:01:45,000 --> 00:01:49,800
I can add a new column and this time I'll use the month function against my quarter field and divide

29
00:01:49,800 --> 00:01:51,690
it by three, all with functions.

30
00:01:52,080 --> 00:01:58,440
Now I've done this purposefully to show that the concept is almost identical to excels functions with

31
00:01:58,440 --> 00:02:04,740
different inputs and different options that has many, many functions, and when combined with the concept

32
00:02:04,740 --> 00:02:07,650
of measures, many different and unique ways to apply them.

33
00:02:08,280 --> 00:02:12,810
Now that we've built some basic calculations, let's take a short step back and look at the syntax.

34
00:02:13,860 --> 00:02:18,130
Like in Excel, every formula starts with an equal sign from there.

35
00:02:18,180 --> 00:02:20,680
The formula simply needs to generate a result.

36
00:02:20,970 --> 00:02:23,730
So let's start with a formula that says equals one.

37
00:02:24,840 --> 00:02:29,880
Pressing enter the new column is simply one, it's not useful, but we can extend this by making it

38
00:02:29,880 --> 00:02:31,150
refer to other columns.

39
00:02:31,950 --> 00:02:36,690
The first thing to note is that you cannot refer to a specific individual cells within the table.

40
00:02:37,080 --> 00:02:41,460
There's no concept of a one, B five or any other single cell reference.

41
00:02:41,880 --> 00:02:47,450
When making references, you have to make reference either to a specific field or to a specific table.

42
00:02:47,970 --> 00:02:50,550
In this case, let's add one to the day of the week.

43
00:02:50,550 --> 00:02:54,750
No, I can type the plus sign and then type day.

44
00:02:55,590 --> 00:02:59,130
When I do this, the intel, the text will give me a bunch of different options.

45
00:02:59,430 --> 00:03:03,720
I can scroll down by tapping the keyboard, select the day of the week.

46
00:03:03,720 --> 00:03:05,040
No, without the table name.

47
00:03:05,040 --> 00:03:10,650
To keep my formula short and concise, press the tab key on my keyboard to fill out the rest and then

48
00:03:10,650 --> 00:03:11,310
hit enter.

49
00:03:12,390 --> 00:03:17,250
All of the basic arithmetic commands are supported this way, the minus sign will work for subtraction,

50
00:03:17,610 --> 00:03:21,570
the asterisk for multiplication, and then the slash for division.

51
00:03:22,080 --> 00:03:27,960
When working with references within the current table and with non aggregating functions, the calculated

52
00:03:27,960 --> 00:03:33,420
column will pull only the values from the current row of the table if you try to reference columns from

53
00:03:33,420 --> 00:03:34,150
another table.

54
00:03:34,410 --> 00:03:41,190
So let's try and pull the purchase orders while the formula barwell only pull through and teletext those

55
00:03:41,190 --> 00:03:42,390
fields that it sees.

56
00:03:42,480 --> 00:03:48,210
So it only recognizes the entire purchase orders table and then it suggests some implicit measures that

57
00:03:48,210 --> 00:03:49,290
you might want to use.

58
00:03:49,920 --> 00:03:55,590
Note here that there are no active relationships seen when computing a calculated column.

59
00:03:55,800 --> 00:03:58,920
The formula will see the entirety of the data universe.

60
00:03:59,640 --> 00:04:03,510
All of the results will be calculated this way and it will ignore relationships.

61
00:04:04,080 --> 00:04:07,920
As an example, let's use the sum function and calculate two different columns.

62
00:04:08,520 --> 00:04:10,830
The first one I'll sum is the purchase orders.

63
00:04:11,310 --> 00:04:15,890
Notably, we get the total purchase orders from the entire purchase orders table.

64
00:04:16,650 --> 00:04:21,810
If I do this with the quarter number, I get the same type of result because we're using an aggregation

65
00:04:21,810 --> 00:04:26,550
function and it pulls the entire field from the table as opposed to the current row.

66
00:04:27,150 --> 00:04:31,950
Any standard aggregation calculation in a calculated column will function this way.

67
00:04:32,760 --> 00:04:38,670
To repeat that, calculated columns will ignore relationships by default, but you can force the use

68
00:04:38,670 --> 00:04:42,620
of relationships with more complex formulas such as this one right here.

69
00:04:43,200 --> 00:04:49,170
Don't worry about this mishmash of calculate and some functions, yet we'll cover it in significant

70
00:04:49,170 --> 00:04:50,070
detail later.

71
00:04:50,520 --> 00:04:55,680
As you can see, calculated columns can be fairly simple or they can be very complex.

72
00:04:56,230 --> 00:05:01,290
Let's clean this table up a little bit by getting rid of these useless, calculated columns and then

73
00:05:01,290 --> 00:05:06,880
I'll hop out the pivot table and we can look at the quarter value that we added as a new filter.

74
00:05:07,590 --> 00:05:12,000
Now, of course, because this isn't part of the date hierarchy, I have to go into the other fields.

75
00:05:12,300 --> 00:05:18,990
But I've just used a calculated column to extend the usability of the pivot table as I see fit in the

76
00:05:18,990 --> 00:05:24,240
next lesson, I'll introduce you to some basic conditional functions to use and then we'll move on to

77
00:05:24,240 --> 00:05:26,720
measures to really start diving into DACS.