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.