Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,930 --> 00:00:06,720
So I want to start with calculated columns because they're a bit more user friendly a bit more intuitive
2
00:00:07,230 --> 00:00:10,410
especially for people coming from an Excel background.
3
00:00:10,410 --> 00:00:16,860
So simply put calculated columns allow you to add new formula based columns to the tables in your power
4
00:00:16,860 --> 00:00:18,180
be-I file.
5
00:00:18,270 --> 00:00:20,290
Couple of important things to note.
6
00:00:20,310 --> 00:00:28,260
Number one there is no A-one style references you can't point to sell C-16 or sell B5 calculated columns
7
00:00:28,260 --> 00:00:36,540
refer to entire tables or columns next calculated columns actually generate new values for each row
8
00:00:36,540 --> 00:00:43,690
in the table and those values are actually visible within your tables in the data view of power be-I.
9
00:00:43,710 --> 00:00:48,780
And then third calculated columns understand something called row context.
10
00:00:48,780 --> 00:00:54,020
What I mean by that is that they can see the information in each row in which they calculate.
11
00:00:54,300 --> 00:00:59,800
And so they're great for defining things like properties based on information in that row.
12
00:00:59,940 --> 00:01:05,500
But they're generally useless for things like aggregation like some's or accounts or averages.
13
00:01:05,610 --> 00:01:07,500
So important note here.
14
00:01:07,770 --> 00:01:14,550
As a rule of thumb you're going to use calculated columns if you want to stamp static fixed values to
15
00:01:14,610 --> 00:01:20,730
each row and one of your tables or you can also use the tools in the query editor to do the same thing
16
00:01:20,730 --> 00:01:22,350
in many cases.
17
00:01:22,350 --> 00:01:28,320
What you don't want to do is use calculated columns for aggregation formulas or to try to calculate
18
00:01:28,350 --> 00:01:33,340
numerical values to use in the values area of a visualization.
19
00:01:33,420 --> 00:01:35,810
We'll be using measures for that instead.
20
00:01:35,820 --> 00:01:42,030
So protip calculate columns are typically used for filtering data giving you new fields that you can
21
00:01:42,030 --> 00:01:48,180
use to create those filters rather than creating or defining new numerical values.
22
00:01:48,540 --> 00:01:53,290
So let's walk through a couple of quick examples here we've got two different tables.
23
00:01:53,370 --> 00:01:56,640
And in each case we've created a new calculated column.
24
00:01:56,670 --> 00:02:02,010
Now you'll note the DAX formula in the formula bar at the top right above the table preview.
25
00:02:02,010 --> 00:02:07,680
And here in this top example we've created a new calculated column that we've named parent which equals
26
00:02:07,720 --> 00:02:14,920
Yes if the total children field in that table is greater than zero and no otherwise.
27
00:02:14,940 --> 00:02:19,470
And when you read through this formula and the top left you'll notice that it looks very very similar
28
00:02:19,470 --> 00:02:20,520
to Excel.
29
00:02:20,550 --> 00:02:23,600
So that same kind of user friendly syntax.
30
00:02:23,610 --> 00:02:30,090
Now remember since this calculated column understands row context a new value is calculated in each
31
00:02:30,090 --> 00:02:34,110
row based on the associated value in the total children column.
32
00:02:34,350 --> 00:02:40,350
So what we're doing is cycling row by row by row looking at the row context and returning the proper
33
00:02:40,350 --> 00:02:41,800
value based on the formula.
34
00:02:42,000 --> 00:02:48,630
So this is a valid use of calculated columns creates a new row property that we can now use to filter
35
00:02:48,630 --> 00:02:54,750
or segment any related data within the model so we can grab that parent field pull it into a visual
36
00:02:55,050 --> 00:03:01,420
and break down our numerical metrics or values to compare data for parents versus non-parents.
37
00:03:01,560 --> 00:03:07,200
In this second example here we're actually trying to use an aggregation function in this case a simple
38
00:03:07,200 --> 00:03:12,960
sum of the order quantity field to create a new column named total quantity.
39
00:03:12,990 --> 00:03:18,450
But here's the thing since calculated columns don't understand filter context we'll get into that a
40
00:03:18,450 --> 00:03:24,330
bit more the same grand total is returned in every single row of the table.
41
00:03:24,330 --> 00:03:31,110
So it doesn't matter which row you're in whether you're calculating the value in row 1 to 3 or 4 in
42
00:03:31,260 --> 00:03:37,650
every single case the sum of the order quantity field is the same sum the same total.
43
00:03:37,650 --> 00:03:40,890
So this is not a valid use of calculated columns.
44
00:03:40,890 --> 00:03:46,620
We've essentially just taken that grand total and we've stamped it into the table in every single row.
45
00:03:46,830 --> 00:03:53,640
And in doing so we've created this static column of numbers that can't be filtered sliced or subdivided
46
00:03:53,700 --> 00:03:54,460
any way.
47
00:03:54,630 --> 00:03:58,560
So for something like this teaser alert we're going to be using measures.
48
00:03:58,560 --> 00:04:02,630
The other important application of data analysis expressions.
49
00:04:02,910 --> 00:04:07,410
So let's quickly hop into our power file and run through a quick demo for ourselves.
50
00:04:08,270 --> 00:04:13,640
OK so once you've opened up your adventure works report head to your data view and click on the sales
51
00:04:13,640 --> 00:04:19,190
table come in order quantities and if you'll remember that earlier in the course when we cover the query
52
00:04:19,190 --> 00:04:26,540
editor we use that conditional column tool to create a new column called quantity type that was based
53
00:04:26,540 --> 00:04:28,480
on the order quantity here.
54
00:04:28,520 --> 00:04:35,220
So any rows where the order quantity was greater than one quantity type took a value of multiple items.
55
00:04:35,300 --> 00:04:37,820
Otherwise it took a value of single item.
56
00:04:37,820 --> 00:04:42,830
So I'm going to quickly show you how we could have created the exact same calculated column here using
57
00:04:42,830 --> 00:04:46,850
daks as opposed to using the tool within the query editor.
58
00:04:46,850 --> 00:04:52,700
So again one of those common themes that we'll see time and time again multiple approaches to solving
59
00:04:52,700 --> 00:04:55,240
the same problem in power.
60
00:04:55,640 --> 00:05:01,730
So what I can do is right click add a new column and write appear in the formula bars where I'm going
61
00:05:01,730 --> 00:05:02,780
to write some daks.
62
00:05:02,960 --> 00:05:04,670
Don't worry about the syntax quite yet.
63
00:05:04,670 --> 00:05:10,460
We're going to dive into exactly how daks formulas are written and then do a ton of demos with some
64
00:05:10,460 --> 00:05:11,990
of the most common functions.
65
00:05:12,200 --> 00:05:18,680
But for now focus is really just on when and why to use calculated columns instead of measures.
66
00:05:18,980 --> 00:05:28,620
So for the sake of example we can name this quantity type equals and now start typing her function.
67
00:05:28,820 --> 00:05:31,470
And in this case we're using a function.
68
00:05:31,700 --> 00:05:39,590
So if and I'll start typing the name of the column daks uses something called Intellisense just like
69
00:05:39,860 --> 00:05:40,790
Excel formulas.
70
00:05:40,800 --> 00:05:46,300
Auto complete provides a list of my options dynamically as I'm typing this case.
71
00:05:46,340 --> 00:05:48,930
I want A.W. sales order quantity.
72
00:05:48,930 --> 00:05:50,510
Just double click to lock it in.
73
00:05:50,720 --> 00:05:59,960
So if order quantity from my A.W. sales table is greater than one comma to my result if true which is
74
00:06:01,220 --> 00:06:04,720
multiple items in quotes.
75
00:06:04,730 --> 00:06:10,290
Comment again to my value false which is single item.
76
00:06:10,370 --> 00:06:17,000
Close the parenthesis press enter and that will lock in my new quantity type column here.
77
00:06:17,020 --> 00:06:17,540
So again.
78
00:06:17,540 --> 00:06:22,290
Same exact logic that we used when we created this column in the query editor.
79
00:06:22,340 --> 00:06:25,790
Except this time we've done it on our own using daks.
80
00:06:25,790 --> 00:06:32,080
If we scroll down we see or single type items here and then we've got some multiple type item categories
81
00:06:32,410 --> 00:06:36,380
where the order quantity is greater than 1 so seems to check out.
82
00:06:36,730 --> 00:06:39,820
That is a valid use of a calculated column.
83
00:06:39,820 --> 00:06:48,290
Now if we were to add a another calculated column new column and this time name it something like total
84
00:06:48,290 --> 00:06:48,950
quantity
85
00:06:52,000 --> 00:07:01,730
and give it an aggregation function like this some groups of A.W. sales order quantity and enter that
86
00:07:02,600 --> 00:07:09,820
just like we showed before we get that exact same duplicate total stamped on every single row.
87
00:07:09,980 --> 00:07:16,250
Because in this case the road context is meaningless and the sum of that quantity column is always going
88
00:07:16,250 --> 00:07:20,620
to return that same grand total of eighty four thousand one hundred seventy four.
89
00:07:20,900 --> 00:07:22,640
So again let's delete it.
90
00:07:22,640 --> 00:07:26,340
This is not a valid use of calculated columns.
91
00:07:27,460 --> 00:07:32,590
What we want to use instead for something like that is a DACs measure which we're in to talk about now.
10123
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.