1
00:00:00,360 --> 00:00:07,110
In our last lesson, we built some simple and not so simple calculated columns, these columns used

2
00:00:07,110 --> 00:00:13,500
formulas that focused on straight manipulation and introduced the basic math operators and syntax for

3
00:00:13,500 --> 00:00:14,720
how to use functions.

4
00:00:15,090 --> 00:00:19,980
In this lesson, I'll guide you through some of the logical functions the tax has to offer.

5
00:00:20,900 --> 00:00:26,900
The first functions we'll get into will be the and switch functions, these functions let you determine

6
00:00:26,900 --> 00:00:33,410
a logical condition to check against and then generate a calculation that is based upon the outcome

7
00:00:33,410 --> 00:00:34,470
of that condition.

8
00:00:34,910 --> 00:00:39,550
If you're familiar with these in Excel, the DAX versions are effectively identical.

9
00:00:40,220 --> 00:00:47,450
We could boil these logical functions down to an if then and else series following these functions.

10
00:00:47,600 --> 00:00:52,490
I'll introduce you to some of the logical functions that are available to you for combining different

11
00:00:52,490 --> 00:00:53,030
boolean.

12
00:00:53,030 --> 00:00:58,520
That is true false operations together, notably the and or and not functions.

13
00:00:58,970 --> 00:01:02,570
For this lesson, I'll be working with our calendar table once again.

14
00:01:02,780 --> 00:01:07,580
So here it is with both our quarter and quarter number of fields that we created before.

15
00:01:08,450 --> 00:01:12,890
If we look at our calendar table, it seems that some key information is missing.

16
00:01:13,220 --> 00:01:18,140
We have lots of details about the day of the week, but the dates haven't been segregated in the weekends

17
00:01:18,140 --> 00:01:23,600
versus weekdays, which seems like it would be a nice filter to have, especially when understanding

18
00:01:23,600 --> 00:01:24,470
revenue trends.

19
00:01:25,070 --> 00:01:30,380
So let's think about what our logic should look like if the day of the week is Saturday or the day of

20
00:01:30,380 --> 00:01:32,480
the week is Sunday, then it's a weekend.

21
00:01:32,720 --> 00:01:35,030
In all other cases, it would be a weekday.

22
00:01:35,900 --> 00:01:38,590
The function seems like it would be a good place to start.

23
00:01:38,750 --> 00:01:44,120
However, since we have two conditions in an earlier statement to test, we're going to start with the

24
00:01:44,120 --> 00:01:45,020
switch function.

25
00:01:45,500 --> 00:01:49,700
The switch function lets us test one expression as many times as we want.

26
00:01:50,120 --> 00:01:54,290
For my expression, I want to check day of the week so it'll be the first input.

27
00:01:55,280 --> 00:01:57,710
The way the rest of the function works is in pairs.

28
00:01:57,980 --> 00:02:04,850
There's a value and then a result the value asks, does the expression that I input it match this result?

29
00:02:05,330 --> 00:02:08,930
If it does, then the result is the output, the last input.

30
00:02:08,930 --> 00:02:13,190
If it's not in a value result, pair format will serve as our alse result.

31
00:02:14,470 --> 00:02:21,340
In this case, we have two weekends and five weekdays, so I can type Saturday come a weekend as my

32
00:02:21,340 --> 00:02:26,020
first value and result pair and then Sunday and weekend as my second pair.

33
00:02:26,740 --> 00:02:32,440
If I wanted to, I could write out all five weekdays and then weekday as five additional keys.

34
00:02:32,890 --> 00:02:38,830
But since all of the other days are weekdays, I can simply put just weekday as the final input sort

35
00:02:38,830 --> 00:02:40,030
of visualize this.

36
00:02:40,540 --> 00:02:46,720
We can take this day of the week value in and then go from top to bottom in this list and find the point

37
00:02:46,720 --> 00:02:47,740
at which it matches.

38
00:02:48,280 --> 00:02:50,640
When it does match, then that's our result.

39
00:02:51,310 --> 00:02:54,880
In essence, it's a series of nested if elser statements.

40
00:02:55,810 --> 00:02:59,230
So how can we do this with the if function now that we've done it with switch?

41
00:03:00,190 --> 00:03:02,200
The aFunction has a fixed format.

42
00:03:02,380 --> 00:03:08,380
It takes a logical expression, it has a result output if it's true and then a result output when it's

43
00:03:08,380 --> 00:03:08,860
false.

44
00:03:09,250 --> 00:03:14,620
There's no one combo pairs here, which makes it simpler but less flexible than the switch until you

45
00:03:14,620 --> 00:03:16,760
start adding additional logical functions.

46
00:03:17,440 --> 00:03:20,860
So let's start with a simple one output case for Saturdays.

47
00:03:21,250 --> 00:03:26,820
So the function works slightly different than the switch function as it needs to evaluate an expression.

48
00:03:27,310 --> 00:03:31,440
So I'm going to type day of week equals and Saturday.

49
00:03:31,990 --> 00:03:34,420
Now, when this is true, we'll get the first output.

50
00:03:34,630 --> 00:03:40,270
And so I'm going to type weekend after the comma when it's false, which is after the next comma.

51
00:03:40,570 --> 00:03:44,350
I'll just say that it's weekday for now and we'll come back to that momentarily.

52
00:03:45,580 --> 00:03:50,980
This gets us halfway there, if we compare it to our switch calculation, we can see that sun shows

53
00:03:50,980 --> 00:03:52,930
up as a weekday, which is clearly wrong.

54
00:03:53,890 --> 00:03:58,570
Now, there are multiple ways to handle this, the first approach would be to basically recreate the

55
00:03:58,570 --> 00:04:01,060
switch approach by nesting RF functions.

56
00:04:01,960 --> 00:04:03,440
So what do I mean by nesting?

57
00:04:03,440 --> 00:04:08,710
And if function, I mean that we can use and if function inside another if function.

58
00:04:09,190 --> 00:04:10,960
So inside our false condition.

59
00:04:11,230 --> 00:04:15,520
Instead of turning weekday, I'm going to make this an if statement.

60
00:04:16,330 --> 00:04:21,730
Within this statement, I can use the exact same logic as I did for Saturday, except I'm going to use

61
00:04:21,730 --> 00:04:22,480
it for Sunday.

62
00:04:23,170 --> 00:04:25,720
Nested shifts are extremely common.

63
00:04:26,050 --> 00:04:31,630
However, they're generally bad practice unless they're absolutely necessary due to how convoluted and

64
00:04:31,630 --> 00:04:32,890
messy they can become.

65
00:04:33,430 --> 00:04:38,950
This leads us to the system, functions to our conditional functions, the logical functions and and

66
00:04:38,950 --> 00:04:45,490
or these functions can condense multiple operations together into a single result in different ways.

67
00:04:46,000 --> 00:04:51,430
The end function will take all of the logical results fed to it, check all of them.

68
00:04:51,610 --> 00:04:55,630
And if every single one of them is true, then the end function will return.

69
00:04:55,630 --> 00:05:02,530
A true think about it as sentence's is Jack Iterator and is Jack working on Saturday?

70
00:05:02,770 --> 00:05:09,970
And is Nasdaq open on Saturday the only case in which the end function will return?

71
00:05:09,970 --> 00:05:13,000
True is when every single statement here is true.

72
00:05:13,990 --> 00:05:17,350
In any other case, the statement will be considered false.

73
00:05:18,410 --> 00:05:19,810
Your function is similar.

74
00:05:20,170 --> 00:05:25,270
If we replace those ends with AWS, we have a different type of logical result.

75
00:05:25,690 --> 00:05:31,420
If any individual phrase within the statement is true, then the whole statement is considered true.

76
00:05:33,210 --> 00:05:38,130
Now, if you're a logic student, you may question, is this an inclusive or an exclusive or.

77
00:05:39,130 --> 00:05:44,800
This is an inclusive or which means that if all of the conditions are true, the statement will return

78
00:05:44,800 --> 00:05:45,690
true as well.

79
00:05:46,000 --> 00:05:51,990
So it's equivalent to the and in that case, with these functions, we can re-evaluate our nested if

80
00:05:52,000 --> 00:05:58,180
function using the statement, if our day of the week is Saturday or the day of the week is Sunday,

81
00:05:58,420 --> 00:06:01,350
then it's the weekend, otherwise it's a weekday.

82
00:06:02,020 --> 00:06:03,760
I can add a new column for this.

83
00:06:04,760 --> 00:06:13,340
If or now our or statement simply works off of the list of logical functions so I can write day of week

84
00:06:13,340 --> 00:06:16,850
equals Saturday, comma, day of week equals Sunday.

85
00:06:18,270 --> 00:06:21,090
Then weekend commo weekday.

86
00:06:26,680 --> 00:06:32,740
So this gives us three different ways of calculating whether it's the weekend now, which is the best.

87
00:06:33,860 --> 00:06:37,550
Really, whichever approach you prefer and find the easiest, right?

88
00:06:38,090 --> 00:06:43,610
I personally prefer the function with your statement, since it reads most closely to how I think about

89
00:06:43,610 --> 00:06:43,760
it.

90
00:06:43,920 --> 00:06:46,910
But the switch function can make a lot of sense in this case, too.

91
00:06:47,480 --> 00:06:54,050
This leaves two functions that I've talked about the end function and the not function to use the end

92
00:06:54,050 --> 00:06:54,460
function.

93
00:06:54,470 --> 00:06:55,640
Let's create a new need.

94
00:06:55,940 --> 00:06:58,360
We need to know when there's a December weekend.

95
00:06:59,180 --> 00:07:04,190
This column will need to indicate yes, four weekends in December or know otherwise.

96
00:07:05,180 --> 00:07:11,510
If I phrase this as a sentence, I can say it as the month is December and the weekend says weekend.

97
00:07:12,650 --> 00:07:18,770
I can use an if statement, and since I'm using a logical and I'll open my end function here, I'll

98
00:07:18,770 --> 00:07:24,380
start with weekend F equals weekend comma month equals December.

99
00:07:24,990 --> 00:07:27,560
I can close this function and add a comma then.

100
00:07:27,560 --> 00:07:28,040
Yes.

101
00:07:28,040 --> 00:07:29,300
And a comma and then no.

102
00:07:30,480 --> 00:07:36,090
Entering this initially, I have no weekends in December and January, which makes sense, but once

103
00:07:36,090 --> 00:07:41,730
I filter to December, I can see that my logic works the weekends in December, a yes value.

104
00:07:42,880 --> 00:07:49,030
Finally, let's look at the not function, the not function will reverse whatever the inputted boolean

105
00:07:49,030 --> 00:07:49,750
result is.

106
00:07:50,290 --> 00:07:57,670
So if I say not one equal to one, since we know that one does equal one and that is true, not that

107
00:07:57,670 --> 00:07:59,920
result gets us a false answer.

108
00:08:00,680 --> 00:08:07,210
This can be useful in conjunction with or and and functions to create specific combinations of results

109
00:08:07,360 --> 00:08:11,200
that might be difficult to identify through traditional comparisons.

110
00:08:12,480 --> 00:08:19,830
As a quick example, I can use the not function in an IV like this, if not, we can them equals we

111
00:08:19,830 --> 00:08:21,990
can, then yes, otherwise no.

112
00:08:23,450 --> 00:08:27,420
Basically, what we're doing is saying if it's not the weekend, then give me a yes.

113
00:08:27,710 --> 00:08:28,610
So yes value.

114
00:08:28,640 --> 00:08:31,490
It's a weekday entering this.

115
00:08:31,490 --> 00:08:32,720
We can see that we get yes.

116
00:08:32,720 --> 00:08:34,190
For weekdays and no.

117
00:08:34,190 --> 00:08:35,030
Four weekends.

118
00:08:37,780 --> 00:08:44,170
Finally and highly useful, these boolean functions can be nested together in any number of unique and

119
00:08:44,170 --> 00:08:45,230
functional ways.

120
00:08:46,030 --> 00:08:49,740
Consider our multistep process for identifying the summer weekends.

121
00:08:50,140 --> 00:08:54,070
We used one of our calculations as an intermediary step.

122
00:08:54,850 --> 00:08:57,930
Suppose I wanted to get that calculation from the get go.

123
00:08:58,420 --> 00:09:02,580
I could write a single formula to do that by nesting and in your logic.

124
00:09:03,220 --> 00:09:09,610
So if the month of December and the day is Saturday or the day is Sunday, then it is a December weekend,

125
00:09:10,600 --> 00:09:14,590
you'll have to figure out how you want a group, these types of logical statements when you're working

126
00:09:14,590 --> 00:09:14,970
with them.

127
00:09:15,400 --> 00:09:21,430
But I can group these using parentheses here and here to understand how I want the logic to work.

128
00:09:22,420 --> 00:09:27,850
This is an and statement within your statement embedded in it, which can be written just like this.

129
00:09:30,060 --> 00:09:36,000
Index, you should primarily use conditional formulas for your calculated columns as you want your measures

130
00:09:36,000 --> 00:09:42,300
to use the context for their computations to maximize their functionality, try creating a few if columns

131
00:09:42,300 --> 00:09:47,970
on your own, such as a column indicating whether each month is part of the spring, summer, winter

132
00:09:47,970 --> 00:09:49,260
or fall seasons.

133
00:09:50,450 --> 00:09:55,000
In our next lesson, we'll finally get to measures and you'll get to see what DAX is all about.
