All language subtitles for 3. Intro to DAX Calculated Columns

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic Download
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranî)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu
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.