Original subtitles

So I want to start with calculated columns because they're a bit more user friendly a bit more intuitive

especially for people coming from an Excel background.

So simply put calculated columns allow you to add new formula based columns to the tables in your power

be-I file.

Couple of important things to note.

Number one there is no A-one style references you can't point to sell C-16 or sell B5 calculated columns

refer to entire tables or columns next calculated columns actually generate new values for each row

in the table and those values are actually visible within your tables in the data view of power be-I.

And then third calculated columns understand something called row context.

What I mean by that is that they can see the information in each row in which they calculate.

And so they're great for defining things like properties based on information in that row.

But they're generally useless for things like aggregation like some's or accounts or averages.

So important note here.

As a rule of thumb you're going to use calculated columns if you want to stamp static fixed values to

each row and one of your tables or you can also use the tools in the query editor to do the same thing

in many cases.

What you don't want to do is use calculated columns for aggregation formulas or to try to calculate

numerical values to use in the values area of a visualization.

We'll be using measures for that instead.

So protip calculate columns are typically used for filtering data giving you new fields that you can

use to create those filters rather than creating or defining new numerical values.

So let's walk through a couple of quick examples here we've got two different tables.

And in each case we've created a new calculated column.

Now you'll note the DAX formula in the formula bar at the top right above the table preview.

And here in this top example we've created a new calculated column that we've named parent which equals

Yes if the total children field in that table is greater than zero and no otherwise.

And when you read through this formula and the top left you'll notice that it looks very very similar

to Excel.

So that same kind of user friendly syntax.

Now remember since this calculated column understands row context a new value is calculated in each

row based on the associated value in the total children column.

So what we're doing is cycling row by row by row looking at the row context and returning the proper

value based on the formula.

So this is a valid use of calculated columns creates a new row property that we can now use to filter

or segment any related data within the model so we can grab that parent field pull it into a visual

and break down our numerical metrics or values to compare data for parents versus non-parents.

In this second example here we're actually trying to use an aggregation function in this case a simple

sum of the order quantity field to create a new column named total quantity.

But here's the thing since calculated columns don't understand filter context we'll get into that a

bit more the same grand total is returned in every single row of the table.

So it doesn't matter which row you're in whether you're calculating the value in row 1 to 3 or 4 in

every single case the sum of the order quantity field is the same sum the same total.

So this is not a valid use of calculated columns.

We've essentially just taken that grand total and we've stamped it into the table in every single row.

And in doing so we've created this static column of numbers that can't be filtered sliced or subdivided

any way.

So for something like this teaser alert we're going to be using measures.

The other important application of data analysis expressions.

So let's quickly hop into our power file and run through a quick demo for ourselves.

OK so once you've opened up your adventure works report head to your data view and click on the sales

table come in order quantities and if you'll remember that earlier in the course when we cover the query

editor we use that conditional column tool to create a new column called quantity type that was based

on the order quantity here.

So any rows where the order quantity was greater than one quantity type took a value of multiple items.

Otherwise it took a value of single item.

So I'm going to quickly show you how we could have created the exact same calculated column here using

daks as opposed to using the tool within the query editor.

So again one of those common themes that we'll see time and time again multiple approaches to solving

the same problem in power.

So what I can do is right click add a new column and write appear in the formula bars where I'm going

to write some daks.

Don't worry about the syntax quite yet.

We're going to dive into exactly how daks formulas are written and then do a ton of demos with some

of the most common functions.

But for now focus is really just on when and why to use calculated columns instead of measures.

So for the sake of example we can name this quantity type equals and now start typing her function.

And in this case we're using a function.

So if and I'll start typing the name of the column daks uses something called Intellisense just like

Excel formulas.

Auto complete provides a list of my options dynamically as I'm typing this case.

I want A.W. sales order quantity.

Just double click to lock it in.

So if order quantity from my A.W. sales table is greater than one comma to my result if true which is

multiple items in quotes.

Comment again to my value false which is single item.

Close the parenthesis press enter and that will lock in my new quantity type column here.

So again.

Same exact logic that we used when we created this column in the query editor.

Except this time we've done it on our own using daks.

If we scroll down we see or single type items here and then we've got some multiple type item categories

where the order quantity is greater than 1 so seems to check out.

That is a valid use of a calculated column.

Now if we were to add a another calculated column new column and this time name it something like total

quantity

and give it an aggregation function like this some groups of A.W. sales order quantity and enter that

just like we showed before we get that exact same duplicate total stamped on every single row.

Because in this case the road context is meaningless and the sum of that quantity column is always going

to return that same grand total of eighty four thousand one hundred seventy four.

So again let's delete it.

This is not a valid use of calculated columns.

What we want to use instead for something like that is a DACs measure which we're in to talk about now.