In our last lesson, we built some simple and not so simple calculated columns, these columns used
formulas that focused on straight manipulation and introduced the basic math operators and syntax for
how to use functions.
In this lesson, I'll guide you through some of the logical functions the tax has to offer.
The first functions we'll get into will be the and switch functions, these functions let you determine
a logical condition to check against and then generate a calculation that is based upon the outcome
of that condition.
If you're familiar with these in Excel, the DAX versions are effectively identical.
We could boil these logical functions down to an if then and else series following these functions.
I'll introduce you to some of the logical functions that are available to you for combining different
boolean.
That is true false operations together, notably the and or and not functions.
For this lesson, I'll be working with our calendar table once again.
So here it is with both our quarter and quarter number of fields that we created before.
If we look at our calendar table, it seems that some key information is missing.
We have lots of details about the day of the week, but the dates haven't been segregated in the weekends
versus weekdays, which seems like it would be a nice filter to have, especially when understanding
revenue trends.
So let's think about what our logic should look like if the day of the week is Saturday or the day of
the week is Sunday, then it's a weekend.
In all other cases, it would be a weekday.
The function seems like it would be a good place to start.
However, since we have two conditions in an earlier statement to test, we're going to start with the
switch function.
The switch function lets us test one expression as many times as we want.
For my expression, I want to check day of the week so it'll be the first input.
The way the rest of the function works is in pairs.
There's a value and then a result the value asks, does the expression that I input it match this result?
If it does, then the result is the output, the last input.
If it's not in a value result, pair format will serve as our alse result.
In this case, we have two weekends and five weekdays, so I can type Saturday come a weekend as my
first value and result pair and then Sunday and weekend as my second pair.
If I wanted to, I could write out all five weekdays and then weekday as five additional keys.
But since all of the other days are weekdays, I can simply put just weekday as the final input sort
of visualize this.
We can take this day of the week value in and then go from top to bottom in this list and find the point
at which it matches.
When it does match, then that's our result.
In essence, it's a series of nested if elser statements.
So how can we do this with the if function now that we've done it with switch?
The aFunction has a fixed format.
It takes a logical expression, it has a result output if it's true and then a result output when it's
false.
There's no one combo pairs here, which makes it simpler but less flexible than the switch until you
start adding additional logical functions.
So let's start with a simple one output case for Saturdays.
So the function works slightly different than the switch function as it needs to evaluate an expression.
So I'm going to type day of week equals and Saturday.
Now, when this is true, we'll get the first output.
And so I'm going to type weekend after the comma when it's false, which is after the next comma.
I'll just say that it's weekday for now and we'll come back to that momentarily.
This gets us halfway there, if we compare it to our switch calculation, we can see that sun shows
up as a weekday, which is clearly wrong.
Now, there are multiple ways to handle this, the first approach would be to basically recreate the
switch approach by nesting RF functions.
So what do I mean by nesting?
And if function, I mean that we can use and if function inside another if function.
So inside our false condition.
Instead of turning weekday, I'm going to make this an if statement.
Within this statement, I can use the exact same logic as I did for Saturday, except I'm going to use
it for Sunday.
Nested shifts are extremely common.
However, they're generally bad practice unless they're absolutely necessary due to how convoluted and
messy they can become.
This leads us to the system, functions to our conditional functions, the logical functions and and
or these functions can condense multiple operations together into a single result in different ways.
The end function will take all of the logical results fed to it, check all of them.
And if every single one of them is true, then the end function will return.
A true think about it as sentence's is Jack Iterator and is Jack working on Saturday?
And is Nasdaq open on Saturday the only case in which the end function will return?
True is when every single statement here is true.
In any other case, the statement will be considered false.
Your function is similar.
If we replace those ends with AWS, we have a different type of logical result.
If any individual phrase within the statement is true, then the whole statement is considered true.
Now, if you're a logic student, you may question, is this an inclusive or an exclusive or.
This is an inclusive or which means that if all of the conditions are true, the statement will return
true as well.
So it's equivalent to the and in that case, with these functions, we can re-evaluate our nested if
function using the statement, if our day of the week is Saturday or the day of the week is Sunday,
then it's the weekend, otherwise it's a weekday.
I can add a new column for this.
If or now our or statement simply works off of the list of logical functions so I can write day of week
equals Saturday, comma, day of week equals Sunday.
Then weekend commo weekday.
So this gives us three different ways of calculating whether it's the weekend now, which is the best.
Really, whichever approach you prefer and find the easiest, right?
I personally prefer the function with your statement, since it reads most closely to how I think about
it.
But the switch function can make a lot of sense in this case, too.
This leaves two functions that I've talked about the end function and the not function to use the end
function.
Let's create a new need.
We need to know when there's a December weekend.
This column will need to indicate yes, four weekends in December or know otherwise.
If I phrase this as a sentence, I can say it as the month is December and the weekend says weekend.
I can use an if statement, and since I'm using a logical and I'll open my end function here, I'll
start with weekend F equals weekend comma month equals December.
I can close this function and add a comma then.
Yes.
And a comma and then no.
Entering this initially, I have no weekends in December and January, which makes sense, but once
I filter to December, I can see that my logic works the weekends in December, a yes value.
Finally, let's look at the not function, the not function will reverse whatever the inputted boolean
result is.
So if I say not one equal to one, since we know that one does equal one and that is true, not that
result gets us a false answer.
This can be useful in conjunction with or and and functions to create specific combinations of results
that might be difficult to identify through traditional comparisons.
As a quick example, I can use the not function in an IV like this, if not, we can them equals we
can, then yes, otherwise no.
Basically, what we're doing is saying if it's not the weekend, then give me a yes.
So yes value.
It's a weekday entering this.
We can see that we get yes.
For weekdays and no.
Four weekends.
Finally and highly useful, these boolean functions can be nested together in any number of unique and
functional ways.
Consider our multistep process for identifying the summer weekends.
We used one of our calculations as an intermediary step.
Suppose I wanted to get that calculation from the get go.
I could write a single formula to do that by nesting and in your logic.
So if the month of December and the day is Saturday or the day is Sunday, then it is a December weekend,
you'll have to figure out how you want a group, these types of logical statements when you're working
with them.
But I can group these using parentheses here and here to understand how I want the logic to work.
This is an and statement within your statement embedded in it, which can be written just like this.
Index, you should primarily use conditional formulas for your calculated columns as you want your measures
to use the context for their computations to maximize their functionality, try creating a few if columns
on your own, such as a column indicating whether each month is part of the spring, summer, winter
or fall seasons.
In our next lesson, we'll finally get to measures and you'll get to see what DAX is all about.