As I discussed in the last lesson, calculated columns will extend tables by adding new column values
for consideration.
There are many times and many places where you're likely to want a new column or two that expands on
the day to present within your existing tables.
Sometimes this might be a combination of fields to construct a new calculated numeric value.
Other times, and most commonly in my cases, this will be to use logic to extend dimensional tables.
Let's start with an example from our grocery store data within our dates table.
There's one particular aggregation date that is missing that I use pretty extensively.
The quarter end date in Excel.
You typically create this calculation using MAAD and the end of month function to calculate the quarter
in power pivot.
I can click on the ADD column and began adding my logic.
That's happens to have a friendly end of quarter function that I can use, which then references my
date field.
Notice that as I type I get lots of and teletext help.
The DAX Formula Bar will help you write so much of your logic this way between the extensive tool tips,
access to the functions and the quick completion with the tab button.
Once I've written the formula very much like you would in Excel, I can press, enter and have a brand
new informational column.
I recommend renaming columns like this.
I call this one quarter and I'll even change its format type to show just the date and move it further
to the left.
And my table by dimensional table will now have a quarterly filter that I can apply.
Calculated columns can also reference other calculated columns just in case you need multi-step formulas
or want to be able to see each step in the process.
In this case, suppose I'm doing some time series analysis by quarter and want the quarter number associated
with each date.
I can add a new column and this time I'll use the month function against my quarter field and divide
it by three, all with functions.
Now I've done this purposefully to show that the concept is almost identical to excels functions with
different inputs and different options that has many, many functions, and when combined with the concept
of measures, many different and unique ways to apply them.
Now that we've built some basic calculations, let's take a short step back and look at the syntax.
Like in Excel, every formula starts with an equal sign from there.
The formula simply needs to generate a result.
So let's start with a formula that says equals one.
Pressing enter the new column is simply one, it's not useful, but we can extend this by making it
refer to other columns.
The first thing to note is that you cannot refer to a specific individual cells within the table.
There's no concept of a one, B five or any other single cell reference.
When making references, you have to make reference either to a specific field or to a specific table.
In this case, let's add one to the day of the week.
No, I can type the plus sign and then type day.
When I do this, the intel, the text will give me a bunch of different options.
I can scroll down by tapping the keyboard, select the day of the week.
No, without the table name.
To keep my formula short and concise, press the tab key on my keyboard to fill out the rest and then
hit enter.
All of the basic arithmetic commands are supported this way, the minus sign will work for subtraction,
the asterisk for multiplication, and then the slash for division.
When working with references within the current table and with non aggregating functions, the calculated
column will pull only the values from the current row of the table if you try to reference columns from
another table.
So let's try and pull the purchase orders while the formula barwell only pull through and teletext those
fields that it sees.
So it only recognizes the entire purchase orders table and then it suggests some implicit measures that
you might want to use.
Note here that there are no active relationships seen when computing a calculated column.
The formula will see the entirety of the data universe.
All of the results will be calculated this way and it will ignore relationships.
As an example, let's use the sum function and calculate two different columns.
The first one I'll sum is the purchase orders.
Notably, we get the total purchase orders from the entire purchase orders table.
If I do this with the quarter number, I get the same type of result because we're using an aggregation
function and it pulls the entire field from the table as opposed to the current row.
Any standard aggregation calculation in a calculated column will function this way.
To repeat that, calculated columns will ignore relationships by default, but you can force the use
of relationships with more complex formulas such as this one right here.
Don't worry about this mishmash of calculate and some functions, yet we'll cover it in significant
detail later.
As you can see, calculated columns can be fairly simple or they can be very complex.
Let's clean this table up a little bit by getting rid of these useless, calculated columns and then
I'll hop out the pivot table and we can look at the quarter value that we added as a new filter.
Now, of course, because this isn't part of the date hierarchy, I have to go into the other fields.
But I've just used a calculated column to extend the usability of the pivot table as I see fit in the
next lesson, I'll introduce you to some basic conditional functions to use and then we'll move on to
measures to really start diving into DACS.