YT YouTube2Text

YouTube2Text → Transcripts → Learn 80% of DAX in an Hour (with FREE sample file)

Learn 80% of DAX in an Hour (with FREE sample file) — Transcript

by Chandoo · 10,663 words · 1,470 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00one of the most frustrating things when
0:02it comes to learning powerb is
0:04understanding and effectively using the
0:06Dax
0:08language this is a problem that I had
0:10when I started learning powerbi many
0:12years ago so in this video let me
0:16distill the 80% of the Practical Dax
0:19when it comes to data analysis and
0:21business intelligence reporting work
0:24these are all the concepts that we are
0:26going to cover in this video and by the
0:28end of this video you will be able to
0:32understand what Dax is how to use it to
0:35problem solve given a business
0:37requirement towards the end of this
0:40video I'm also going to talk about a
0:43proprietary almost trademarked ACM Buu
0:47Aku approach of using Dax more on that
0:52later let's go here is my powerbi
0:54workbook I have provided a copy of this
0:57file as well as the blank data set if
0:59you want to build the whole data model
1:01yourself I explained how this data model
1:04is constructed in the previous video
1:07essentially this is a data model for a
1:09madeup chocolate company called awesome
1:12chocolates and here we have got five
1:14tables let me show you the semantic or
1:16the data model
1:18quickly so here is the data model that
1:21we are using we have got chocolate
1:22shipments in the fact table at the
1:24center of this model and we have got
1:27four dimensions neatly laid out in the
1:31star schema pattern here normally for
1:34the Str sake of laying things out you
1:36may want to set them up like this kind
1:38of a octopus or a star schema setup but
1:42more from a maintenance perspective it
1:44might be just a good idea to keep all
1:47the dimension tables on one side of the
1:49screen like up
1:51top and move the fact tables to the
1:54bottom this way if you have if your
1:56model ever gets too big you will always
1:58know what is a d Di menion and what is a
2:00fact dimensions are always up
2:03top and fact is at the bottom and from
2:06the dimension the filters move to fact
2:09that's why the arrows always Point
2:11towards the fact so you can see that if
2:13you were to filter for example by a
2:15specific
2:16salesperson their details are shown here
2:19in the shipments table more on this
2:22later but for now this is how our model
2:25looks like again a quick reminder a copy
2:28of this file is a aailable in the video
2:30description the blank file download it
2:33and follow along with me as you practice
2:36taxs as this video can get pretty
2:38intense I highly recommend setting some
2:40time aside to go through this whole
2:42thing in one setting for best
2:44results now let's go and add a new page
2:47this page is something that we built in
2:49the previous exercise where I explained
2:51how we have constructed that particular
2:53data
2:54model so here we're going to start our
2:57concepts with essential data
3:03Dax what is Dax Dax is the language that
3:07is used to calculate things in powerbi
3:10or more importantly in power pivot Dax
3:14stands for data analysis Expressions you
3:17might think why doesn't it call why
3:20don't they call it D Dae because data
3:23analysis Expressions but I don't know
3:26they went and called it
3:28Dax just as the
3:30acronym is confusing some of the Dax
3:32logic and measures and the way we work
3:34with it is also a little bit confusing
3:36especially when you start learning it so
3:39that's why it is important to get the
3:40fundamentals right and that's my purpose
3:42of making this
3:44video so we have got all of these tables
3:47here and in this previous page here we
3:50were able to see for example how much is
3:53the total amount by a product to do this
3:56what we did is we put product onto this
3:59axis of the column chart and amount into
4:02this area or vertical axis or Y axis of
4:05the thing as amount is a numeric column
4:08if I look at my shipments table I have
4:10seen that amount is a number column the
4:13moment I put that into the y axis here
4:17powerbi automatically did a sum of
4:19amount that means it is summing it up we
4:22could for example click on this here and
4:24use this summarization option there to
4:28change it from sum to minimum maximum
4:31count or average or
4:33median this does offer you a quick and
4:37easy way to customize some of the
4:39calculations but the problem with this
4:41is this is quite limited and you can't
4:43really do much when you actually want to
4:46analyze the data and produce some sort
4:48of a business intelligence or deep
4:50insights into your data and that is
4:53where Dax offers a structured framework
4:56to talk to your data ask business
4:59questions and get the results so it
5:01gives you a language and a framework to
5:03do all of this we are going to do that
5:05from now on so the calculations that are
5:08produced by Dax they're usually called
5:10as measures and we are going to write
5:14our first measure and eventually
5:15everything starts to click in so our
5:17first measure is to pretty much
5:19replicate what we are seeing here we
5:21want to understand what is the total
5:23amount some amount and then be able to
5:25see it at a product level so I'm going
5:28to go to a new page doesn't matter
5:30whether you do it in that page or here
5:32but uh we going to keep it clean by
5:34building it in this page to create a new
5:36measure you can go and click on many
5:38places in the screen it is kind of right
5:40in your face uh you can right click on
5:42the shipments table you will find new
5:44measure option here as this is quite an
5:47important feature powerbi also puts this
5:49button right there on the screen in the
5:52home ribbon and there is a whole
5:54modeling ribbon available where you will
5:57have replica of that button plus many
5:59other activities that we can do with Dax
6:02so it is always there and if that is not
6:05enough they have now added a Dax query
6:08view into powerbi this is I think uh
6:11still a preview feature I'm not really
6:12sure but it is also there through which
6:15you can kind of systematically code more
6:18Dax queries and other things we will go
6:20there uh towards the end of this video
6:23but for now I'm just going to this is my
6:25favorite way of doing I'll right click
6:26and then use the new measure
6:29and it will add a formula bar up top
6:32here this is where you usually write the
6:34measures and uh when you are done you
6:36click on this tick mark to commit that
6:39measure you can also press enter and
6:41that gets activated as the measure
6:43whenever you are typing the measure you
6:45will also see a special ribbon called
6:47measure tools get activated this is only
6:49available to you when you are either
6:51writing or editing a measure so you
6:53can't you won't see it when you're
6:54outside and through this you will be
6:56able to adjust some of the settings for
6:59that me
7:00you can also go to the model view and do
7:02that customization there uh we will go
7:05there in a minute but for now our first
7:07goal is to get the total amount so a
7:11measure will have two parts name of the
7:13measure equal to sign and then the
7:15business rule or the logic for creating
7:17that measure let me just expand the text
7:20area here to expand you can hold on the
7:22control and use the scroll wheel on your
7:24mouse to kind of make it bigger or
7:26smaller so here the measure name name
7:29would be total amount and equal to and
7:34we just want to look at the shipments
7:38table this amount column here and then
7:42add it up so the way this works is you
7:44just write Su of shipments table you can
7:50type everything you can also use this
7:51Auto suggest to pick what you want so
7:53for example I'm using my arrow keys here
7:56to pick the amount and once I select I
7:57can hit the Tab Key to fill that for me
8:01so sum of shipments amount is my total
8:03amount and we will get the total value
8:05once it is done you can either hit enter
8:07or you can click on this commit button I
8:10like to just hit enter because usually
8:12at this point I'm on my keyboard it'll
8:14be done once a measure is
8:16added it will go and sit in the table on
8:22which you right clicked and created the
8:23measure so in this case shipments is the
8:25table on which I right clicked so my
8:27total amount is also attached to the
8:30thing there measures will have this
8:32little special symbol next to it it's
8:34the calculator symbol because they're
8:37doing
8:38calculation a measure will not only have
8:41if you select this measure here you can
8:42see that a measure has a couple of
8:44things a measure has a name the
8:47definition of the measure the business
8:49rule or the logic here the rule or the
8:51logic is go to the shipments table take
8:53the amount column and sum it up that's
8:56the logic that we are writing apart from
8:58these two things a measure can also have
9:01display behaviors so here you have got a
9:03whole formatting tab that is currently
9:06set to Auto so if you don't do anything
9:08it'll be automatically formatted based
9:10on what powerbi thinks this value is but
9:12you can set it because many times when
9:14we create this measure on the screen I
9:16may want to see it in a certain way so
9:18for example as this is an amount I'm
9:20going to go into my format area here
9:22from General I'm going to switch to
9:25currency and I'm going to say I don't
9:27want any decimal points so I'll say zero
9:30decimal points there so now that the
9:32measure is created I can click on the
9:34white space to go back to my canvas and
9:36I can see this measure normally when I'm
9:39learning or explaining Dax to others I
9:41like to use either a table or Matrix
9:44visual because these are just numbers
9:46and I can see the numbers I can kind of
9:48connect the dots better so we going to
9:50use a table Visual and here in the
9:54shipments table I have got total amount
9:56first I want to see how much is that
9:57amount per product so I can go to the
9:59product table bring
10:02product and then bring the
10:04amount so here you will see how that
10:07amount is for each product it
10:09automatically calculates what that
10:11amount is and at a total level it also
10:13tells you what that amount is so this is
10:16how a measure works if you see these are
10:19the numbers coming through my total
10:21amount column if I were to now take just
10:23the amount column and put it into the
10:26table it will also do a sum of am amount
10:29autoc calculation this is how
10:32our column chart created by the way and
10:36you'll see that the numbers match
10:39$380
10:40$380,500 and it's the same value here we
10:44don't have the decimal points here we
10:46have decimal points but exactly same and
10:48at a grand total level also you can see
10:50everything adds up nicely so both of
10:53them the total amount and the sum of
10:55amount which is autoc calculated by
10:56powerbi are doing the same thing
11:00and this is one of the things that kind
11:01of frustrates the new Learners or people
11:04who haven't used powerbi much but come
11:08from let's say some other tools and then
11:10start using it and saw some demos they
11:13might be thinking okay this looks like
11:16it's giving me the same answer with a
11:18lot of work why do I even need to learn
11:20Dax when I could get the answers already
11:23and that is a fair point the problem is
11:26like I said earlier this thing the sum
11:29of amount which is autoc
11:31calculated is a restrictive option it
11:34doesn't give you many choices you can
11:36either sum average count Etc
11:40whereas our total amount column the one
11:44that we explicitly calculated gives us
11:47so much more freedom to begin with at a
11:50very simple level I can write the words
11:52that I want to call it for example I can
11:54say total amount instead of sum of
11:56amount total amount makes so much more
11:58business sense than and uh the kind of
12:00AI sounding sum of amount word on top I
12:04can assign some formatting to it so the
12:06moment I put it in my report it
12:08automatically formats but these are kind
12:10of like very trivial benefits the real
12:12benefit and the real power of Dax lies
12:15in the fact that it lets you build your
12:18own calculations based on your business
12:21rules and policies so from here on out
12:24we are going to explore the powerful
12:26side of the Dax and get more interesting
12:29and Innovative with our learning into
12:32Dax so I want to conclude this segment
12:35with one extra note which is the names
12:38that we use especially once you start to
12:40learn and get technical with this is
12:43these are called explicit
12:45measures and these are called implicit
12:49measures what it means is in in this
12:52case here we are just dragging the
12:54amount and dropping and powerbi
12:56automatically implicitly creates that
12:58calculation for us whereas here we are
13:02explicitly calculating things so
13:04normally once you start your journey
13:06into powerbi and you learn the basics
13:08and you move on to the Dax stages you
13:10will pretty much use only explicit
13:12measures so here on not make a promise
13:14to me pause the video and say it out
13:17aloud I will not make any more implicit
13:21measures no matter how convenient they
13:23are I will not make them so say the
13:25Pledge I will not make any more implicit
13:27measures and it sounds corny but this is
13:30an important thing to keep in mind if
13:32you want to grow your powerb skills and
13:35improve your Dax
13:40understanding all right so now that we
13:43have the total amount and we see that in
13:44the table here one other thing that kind
13:47of tips people off is okay I see this in
13:51the
13:52table doesn't mean this also exists in
13:55the
13:56data and when you go to the data View
13:59go to the data and select the shipments
14:01table you don't see any columns here
14:05there is no total amount column added
14:08whereas if you see here in the fields
14:10list you'll see all these fields and
14:13then you'll see the total amount as
14:14another button here where is this one
14:17way to think about this is if your
14:20shipment table is this hand this first
14:22store hand and total amount is this
14:25extra calculation that we built it
14:28doesn't belong in the hand but it acts
14:30on the hand so it can kind of take the
14:33hand and twist it and turn it to tell
14:36you what the value is for each product
14:38or at a grand total level but this hand
14:41is not part of that hand it's just that
14:43they are laid out for the sake of
14:45Simplicity on the screen in the same
14:47table but the column or so the measure
14:50total amount never really belongs in the
14:52table it is just something that acts on
14:55top of the table so this is something
14:57that you want to put put in your mind
14:59find and refer to that analogy every
15:01time you see a measure a measure just
15:04acts on the table it's not part of the
15:09table just as we could do total amount
15:12you could also do averages counts and
15:14other things to demonstrate those I'm
15:16going to take out the sum of the amount
15:18the implicit measure and just use the
15:20total amount now we can again write
15:23click here and write a new
15:25measure and this measure would be for
15:27example number of shipments each row in
15:31the shipment table is one shipment so I
15:33just want to count how many shipments
15:34are there in total so we can call this
15:37measure as shipment count and here the
15:40function that we are using is Count
15:42row count row accepts a table name so
15:45count rows of
15:47shipments and again that will give you
15:49shipment count while we are in the
15:51measure tools I can apply a thousands
15:53formatting for that and then I can add
15:57that to my table to see how many
15:59shipments we are doing by individual
16:00products so we have done a total of
16:037,95 shipments and this is how it looks
16:06at a product level as we are writing
16:09these measures you might see oh these
16:11kind of look like how I would write my
16:14Excel functions in Excel we have got
16:16some function in Excel we have got
16:18countif function they follow the similar
16:20syntactical pattern you have got a
16:21function name Open Bracket and either
16:24columns or
16:25values so another big mistake and this
16:28is a mistake that I have
16:31made in early stages of my learning of
16:34powerbi and power pivot is we think oh
16:38this looks just like Excel so it is
16:40Excel then what happens is in our mind
16:43every time we want to write a measure or
16:47build some logic we're trying to use our
16:50Excel mind to solve the problem and
16:52that's not going to work in powerbi
16:54World think of this like this the alphab
16:58bet that is used by Dax and Excel
17:02functions is same they follow the same
17:04syntactical pattern which is you have
17:06got a function Open Bracket parameters
17:10close bracket kind of a notation so in
17:13Excel we have got some function in
17:16powerbi we have got some function they
17:18look same because they share that kind
17:20of a syntactical pattern the grammar of
17:23the language and the alphabet of the
17:25language but they are two different
17:27things one way of thinking about this is
17:30imagine you know English very well now
17:34you take a plane and you go to
17:36France you'll see all the road signs all
17:40the building signs all the messages and
17:42metros and everything in pretty much
17:45English alphabet I mean French has some
17:47extra letters but the alphabet largely
17:49looks like
17:50English so you might think to your mind
17:53that oh this looks like English let me
17:55try and read it like English and
17:57understand like English
17:59you it wouldn't make any sense you
18:01wouldn't be able to even order a cup of
18:03coffee if you use your English mind this
18:06is because they share that kind of the
18:08alphabet but they're two different
18:13things p p great okay
18:19faster so it's the same way with the Dax
18:22and Exel functions they kind of look
18:24same simply because it is a convenient
18:26choice for the developers to make when
18:28are designing this
18:30language but they're two different
18:32things so from here on out discard all
18:35your Excel knowledge when you are
18:37looking at Dax don't try to think back
18:40and try to connect the dots with Excel
18:42instead approach this as a fresh new
18:44language that way you'll be able to
18:46learn better and you don't have to carry
18:48this extra baggage with you everywhere
18:51hope that helps now let's go and look at
18:54shipment count closely it has a special
18:57function called count row you can see
18:59that here this countr function doesn't
19:01even exist in Excel Excel doesn't have
19:03such a function but powerbi does so Dax
19:05language has hundreds of functions some
19:09of them like sum and count and average
19:11share the same name as Excel functions
19:14but they're just because that's an
19:16obvious thing to do but Dax offers
19:18different set of functions and the
19:19behavior is almost always
19:24different when you write a function
19:26whether it is total amount or count rows
19:29here you just specify what you want you
19:33don't go into all the specifics you only
19:35specify the business rule or the
19:37behavior for example shipment count is
19:39how many shipments are how many rows are
19:41there in the shipment table that is the
19:43business
19:44rule when you apply the measure into a
19:47specific visual here I have got a table
19:50visual with product name in each row the
19:54shipment count will be calculated for
19:57that product automatically
19:59using a concept called evaluation
20:02context this is where whenever you have
20:05a measure when you create you just
20:07create the definition of it so whether
20:09it is shipment count or total amount we
20:11just specify the Bare Bones naked
20:14version of that definition and we don't
20:16go into any specifics but when the
20:19measure is laid out on the screen
20:22depending on what the purpose of that
20:24visual is what is there on that Visual
20:27and what else is there on the scre
20:28screen power bi well technically power
20:31pivot automatically calculates the value
20:34using that evaluation context so for
20:37example here the evaluation context for
20:39that number 86 is product column of
20:43product table is 50% Dark Bites so
20:46behind scene what happens is if you go
20:48to the date model this is where the
20:50model is really helpful if you look at
20:52it the shipments
20:55table has the shipments count so here is
20:57my measure
20:59but this count is defined as number of
21:01rows in the shipments table but at the
21:04time of calculating that particular
21:06number on the screen we have already
21:09looked at a specific product so the
21:11product has been selected here 50% Dark
21:14Bites and once that product is
21:17selected that means the product table is
21:19filtered down to just one row it doesn't
21:22really have all the rows it is only
21:23looking at that product and look at this
21:25line here this line says if the product
21:28table is
21:29filtered the filter should go and apply
21:32on the shipments table as well so that's
21:34what the direction refers so once this
21:36is going here here shipment table get
21:39also filtered down just to product that
21:42first product's number of rows and at
21:44that point it just counts how many
21:46values are there how many rows are there
21:48and then comes back as shipment count
21:51this is why in the definition of the
21:53measure we don't really think about how
21:55to do this for a product specific value
21:58even though the business requirement
22:00might say I want to see how many
22:01shipments we are doing by product you
22:03don't really have to worry about the
22:05product thing here you just write count
22:07shipments there alone once that is there
22:10I can use it in the context of this
22:12visual to see how many shipments are
22:14there for that product I can move this
22:17and in this space here for example I can
22:20put a column chart and in this column
22:23chart I can put our geography on x-axis
22:27and add shipment count on y axis and
22:29then now I'm seeing how many shipments
22:31we're doing at a country level here the
22:34evaluation context is count is UK so
22:38automatically this Geo column is set to
22:41UK and locations table from six rows it
22:44shrinks down to just one row and then
22:47the filter will go into shipment table
22:49because there is a model connection
22:51there shipments table will also shrink
22:53down to just the UK's corresponding
22:55shipments and then the count shipment
22:58count will execute just for the row in
23:00that shipment table at that point in
23:02time every little thing that you do on
23:05the screen will impact this so for
23:07example see what happens the moment I
23:09click on
23:10UK you'll see that here this count is no
23:14longer the earlier number it is now down
23:16to 15 this is because the evaluation
23:19context has changed earlier we are just
23:22looking at 50% AR bites but now we are
23:24interested in what is the value of UK
23:28for 50% AR bites in terms of shipment
23:30count so the evaluation context for this
23:33number is now it has to filter the
23:36product it has to also filter the UK if
23:39there is any other filter so for example
23:41if I have got a slicer on my team
23:44members names or if I've got a filter on
23:46my dates or
23:48anything all of that those will get to
23:51decide what the evaluation context is
23:54and this is why when you look at any
23:55number on the screen it is important to
23:58understand and what is going on on the
24:00screen to interpret and understand that
24:03number so that what is going on on the
24:06screen is pretty much referred to as
24:08evaluation
24:09context all right so we have got total
24:12amount shipment count and I can add
24:15other things as well if you right click
24:17and say new measure you will be able to
24:20for example do something like total
24:23boxes and this is nothing but some of
24:25the boxes column in the shipment tables
24:29some shipments
24:30boxes and we can put this into thousands
24:34with zero decimals and I can add this
24:38and I can see 3.78 for
24:44million so the next concept that we're
24:47going to explore is while you could do
24:49sums counts averages and other things
24:51I'm not going to go into individual
24:53little things like how to build an
24:54average measure or how to do minimum or
24:56how to do maximum because those are are
24:58fairly obvious so I'm not bothering with
25:00that but the next concept that is kind
25:03of important to understand and explore
25:05the Dax betteries for the sake of that
25:08I'm just going to delete this visual uh
25:10we have more screen space for this
25:13is combining or reusing measures so
25:17right now think of these measures as a
25:20little assets that you're
25:24building so we've got three assets we
25:27have got uh shipment count we have got
25:29total amount and we have got total
25:32boxes on a standalone basis they just
25:34tell you what is happening for them but
25:37now think of actual business requirement
25:40where I want to know which products have
25:42more boxes per shipments that means the
25:45shipments might be fewer but we'll
25:47actually send more boxes of chocolates
25:49in those shipments so I want to analyze
25:52that this measure we can think of it as
25:55boxes per shipment and the logic for
25:58this is we take this number and we
26:01divide it with that number that's pretty
26:04much it so how do we develop this this
26:07is where the ReUse concept comes in
26:09because we have already built these
26:10three assets total amount shipment count
26:12and total
26:13boxes I can create a new measure right
26:16click new measure and then I can call
26:18this as boxes per
26:23shipment and here we already have both
26:26of them so I can say total boxes so you
26:28open the square brackets and then you
26:29say total boxes divide
26:32sign with shipment
26:36count and you can click okay to add that
26:40so now that measure is added I can
26:42select the visual and I can put that on
26:44there and I can see boxes for shipment
26:47how we are doing so in terms of for
26:50example our data here this is how it
26:52looks if I'm seeing just the total
26:53amount I'm going to sort this in
26:55ascending order you'll see that 50% Dark
26:58Bites is our lowest selling product
27:00380,000
27:02but if I'm looking at boxes per
27:05shipment you'll see that EES is actually
27:08our lowest product because we only ship
27:10152 boxes of EES per shipment whereas
27:1450% Dark Bites is further down it is in
27:16the fourth place with
27:18295 so this kind of a composite
27:20calculation exposes interesting and
27:23useful information about your data and
27:26we can build all of these is by simply
27:29reusing the measures that are earlier
27:31defined you don't have to write the
27:32whole thing again if you look at this we
27:35are just saying get that number get this
27:37number divide one with another so we
27:39don't have to write the whole sum and
27:41count logic again we just point to those
27:44things and boxes per shipment is a
27:47generic measure so I can use it in the
27:49context of product or I can add a page
27:53and in this page if I I may want to
27:55explore what is happening at our person
27:58level so I can go to people table put
28:00sales person and then see what is the
28:03boxes per shipment at a person level and
28:07then I can even Analyze This to see uh
28:10who is doing more boxes per shipment so
28:12for example rodie wone juu they're all
28:15doing 500 plus boxes per shipment
28:17whereas further down here we have got
28:19meline van and gigy doing around 400 per
28:23shipment again this sort of an
28:25interesting Insight is easily achievable
28:28simply by combining those two
28:34measures you might be having one nagging
28:37question at this point which is if you
28:39look at the way we defined boxes per
28:41shipment we are saying total boxes
28:43divided by total shipment count now
28:46let's take a look at another measure
28:48like total boxes in case of this we are
28:51using shipments boxes if you carefully
28:54observe this we're saying table name
28:58column name this is the notation that we
29:00are following to refer to a specific
29:02item how come we are not following that
29:06convention when we are doing
29:09this this is because if you remember my
29:11earlier example where I said a measure
29:14doesn't really belong in the table it is
29:17just laid out in the table for the sake
29:19of Simplicity that comes back to us now
29:23the measure total boxes or shipment
29:26count is technically not in any table it
29:29is just laid out here for the sake of
29:31visual uh Simplicity and finding it
29:34there that it is in that table but it
29:36doesn't really matter which table it is
29:38it is always going to come up with the
29:40same value so a measure technically
29:43doesn't belong to a table it belongs to
29:45the entire data or semantic model so
29:48measure is part of the semantic model
29:50and when you refer to the measure you
29:52don't have to have the table name you
29:54can put the table name it won't bother
29:56about it but you you don't have to do it
30:00and the second idea here is as a best
30:02practice you don't want to put table
30:05name ever in front of the measure just
30:08always refer to measures by themselves
30:10this way if I'm looking at some complex
30:13piece of Dax code and it refers some
30:15measures and some table columns imagine
30:18these are simple on line ones but pretty
30:20soon you will write like 20 line Dax
30:23measures and there could be multiple
30:25things multiple table columns being used
30:27as well as measures when you are looking
30:29at that jumble anytime you see a format
30:32like this table column you know that oh
30:35this is a table column and anytime you
30:38see just square brackets without the
30:41table name you automatically know that
30:43oh that is just a measure so this is
30:46actually a best practice that will help
30:48you later when you are developing more
30:50complicated things so for that reason we
30:53don't have to use it going back to this
30:55table here let's use the reusability
30:57concept ccept once more this time to
30:59Define amount per shipment so we doing
31:021881 shipments $713,000 of business here
31:06$713,000 what would be the amount per
31:09shipment again we can create a new
31:13measure and this is called amount per
31:17shipment and instead of using the Divide
31:20sign directly powerbi also offers a safe
31:24divide function called divide what this
31:26does is it will try to divide but should
31:29there ever be a divide by zero scenario
31:31because you have filtered down or
31:33narrowed down the datas to a level where
31:35there is not many options it will give
31:37due by zero error if you just do a hot
31:40divide so this basically does a safe
31:42division you just specify numerator and
31:45denominator and it will do the div
31:47division for you so numerator here is my
31:49total amount and denominator is shipment
31:54count and you can also pass an alternate
31:57result normally I don't do it if you
31:59don't say anything it'll just blank out
32:01on the screen so here I'll hit enter and
32:05we can add that to our table and again I
32:09can see what is the amount per shipment
32:11we are doing again you can apply measure
32:13tools formatting to it for example I can
32:15say it should be in currency with the
32:18zero decimal points and I'll see that
32:21and I can apply sort orders and all of
32:23that needless to say once you have these
32:26kind of composite measures that reuse
32:28the concepts from earlier you can just
32:31keep these two alone and take out all of
32:34them they'll still work we have actually
32:36seen it here I'm directly seeing boxes
32:39per shipment per person without seeing
32:41the individual bits and you can use the
32:44rest of the other good things here as
32:46well so for example this is my data now
32:49if I have got a bar chart here with my
32:52geographical breakdown of
32:55shipments so I'm looking at this and I
32:57may want to understand what is the
32:59amount per shipment in UK looking like
33:01if I click on this automatically all of
33:03this will update and I'll see what is
33:05the product with more amount per
33:07shipment in UK so it is organic choco
33:09syrup but if I go to Canada it's $8,000
33:13for Alman chako because again we have
33:16implemented the sort order as soon as I
33:18click on Canada the numbers change and
33:20the sort order kicks in again so this
33:22opens doors for some really interesting
33:25and Powerful analysis of your data
33:27simply because we have created three
33:30base measures these three are our base
33:33measures and two composite measures one
33:36is doing this divided by that and
33:38another is doing this divided by that
33:40again if you look at this data for
33:42example you might be exatic thinking oh
33:44$8,000 per shipment we should ship more
33:47of this Alman choco to Canada and then
33:49you come to the actual shipment count
33:51there was only one shipment so it's
33:53basically an outlier rather than a trend
33:56and more interesting patterns are
33:57somewhere down here where we are doing
33:5940 or 30 shipments and these numbers are
34:02a bit more reliable and at this point
34:04you can actually see the power of power
34:06pivot already Dax already it lets you
34:08build these kind of things which are not
34:11possible with the implicit measures if
34:12I'm just adding sums and counts and
34:14averages I would never be able to go in
34:17this
34:19direction the next concept that we are
34:22going to explore is probably the most
34:25game-changing and most useful most
34:28applicable Concept in all of Dax and it
34:30is the ability to change the filtering
34:34or the evaluation context I'm going to
34:37add a new page for this and let's
34:39explore a typical business problem let's
34:42say I'm looking at a table here and I
34:46want to see what is happening at our
34:48product and then how much is the total
34:52amount so two simple things and one of
34:55our employees bar fon I'm going to just
34:58bring them up here if I go into people
35:02table you'll see that
35:04Baron one of our employees and their ID
35:07is
35:08sp01 I responsible for Baron he's one of
35:11my sales members so as I'm looking at
35:14this I know that 50% AR bites total
35:17amount is 380,000 I would want to know
35:20what is the amount that bar fonny is
35:23bringing I like to call this as bar
35:25amount how do I go about this because if
35:29I'm looking at this and if I'm
35:31interested in bar Fon amount I could for
35:33example theoretically add a slicer and
35:36then put my
35:38salesperson into this slicer and then I
35:41can click on bar Fon as soon as I click
35:44I'll see 50% Dark Bites for bar bar Fon
35:47is 46,000 but I also lose the context of
35:50what was the original amount in order to
35:53go there I'll have to unclick and then
35:55see original amount is 380,000 so this
35:57is like a flicking a switch I can either
35:59have it on or off so this sort of a
36:01thing is not what I want instead what I
36:04want is I would want to keep these
36:06values as they are but add another
36:08column here and call it as bar amount
36:11and see what that number is for each of
36:13the products and maybe add it as a
36:17percentage so what was the bars amount
36:19as a percentage and then do some
36:22exploratory analysis on it I I want to
36:24understand uh which products heavy r on
36:28bar maybe he's planning on going on a
36:303-month trick and we want to know what
36:32impact this would have on our sales so
36:34how do we go about this this is where
36:38powerbi power pivot introduces a really
36:41powerful and extremely versatile
36:43function called
36:45calculate what it does is it can take
36:48control of the situations that are
36:50happening on the screen and it can kind
36:52of override them it's very tricky to
36:55explain because it is kind of like a
36:57Swiss army knife of the functions it can
36:59do a lot of things so there is no one
37:02easy way of explaining it other than
37:04showing it to you so let me show that
37:06we're going to write a new measure and
37:09call this as bar amount and the purpose
37:13of this is calculate the total amount
37:15just for bar F so here this is how the
37:18Syntax for this is we say calculate and
37:21you write an expression here this
37:23expression is usually an existing
37:25measure in the model but you could also
37:27type the whole thing here so we'll say
37:29total amount and then we specify the
37:32filter criteria that you want to apply
37:34so we want to calculate total amount as
37:37if we are looking at bar foring so this
37:39is where we'll say people salesperson is
37:42equal to and then within double codes
37:45bar F GN my f close bracket and commit
37:51this let's apply currency formatting
37:53with zero decimals and let's see the
37:56puppy so here is my bar amount you'll
37:59see that I have 380,000 I have 46,000 as
38:02well both of them visible to me all the
38:04time so I can see both numbers and I
38:07could make an informed decision so 44
38:09million 3.6 million is brought in by bar
38:12Fon while looking at this just pause
38:15here and think what would happen if you
38:17click on the slicer and then select bar
38:20foron all right let me show you so if I
38:22click on Baron now you'll see that both
38:25of these match because this column here
38:28pays attention to the slicer and then it
38:31says oh you want total amount just for
38:33Baron I'm going to show that the bar
38:36amount is kind of self-explanatory it
38:37will be same as what this is now imagine
38:40what would happen if I click on someone
38:43else for example if I go to chess bonell
38:46so if I click on chess can try and pause
38:48here and think what would
38:51happen if you click on chess you'll see
38:54that the total amount column here
38:57reflect CS the values for chess bonnel
38:59so all of these are for this little dude
39:01here what about these numbers they are
39:05stuck with bar foring this is what
39:07calculate does it overwrites what is
39:10happening on the screen so the screen is
39:12saying show me chess bonel and the First
39:15Column respects that because that
39:17measure doesn't have calculate on it it
39:19simply just does the calculation for
39:21whatever is on the screen but the second
39:23measure this bar amount we're going to
39:26change color of this for that this bar
39:29amount is a special one it is using the
39:31calculate so it is saying calculate the
39:34value as if I'm looking at bar for so
39:37even though the screen is saying get me
39:39chest bonel this calculat comes in and
39:42it's like a boxer it punches out chess
39:44bonnel knocks him out and then goes to
39:47Baron to get the value of that little
39:50guy and then print that there so that's
39:53really what a calculate does it
39:55overwrites it takes control of the
39:57evaluation context so that you can
39:59calculate things in a new light you can
40:02calculate anything it doesn't have to be
40:04total amount it could be boxes per
40:06shipment it could be amount per shipment
40:08or it could be something else that you
40:09have come up with whatever it is
40:11calculate can do that for you that is
40:14why I said it is kind of like a very
40:15versatile and Swiss Army kind of knife
40:18kind of function it can do a lot of
40:20things so it's trickier to explain and
40:24it does look kind of very naive and
40:26simple but once you understand the power
40:28of it you can start to see ooh I could
40:31do this I could do that I could build
40:33these kind of calculations and that's
40:35opens many many doors for you so like I
40:37said I can do total amount bar amount
40:40and then I could also calculate bar
40:44amount as a percentage so for example
40:46here I can say bar amount
40:49PCT is equal
40:51to divide bar amount with total amount
40:58and apply a percentage formatting with
41:01one
41:03decimal and then I can put that there I
41:05can see what is the percentage of bar
41:07foron for each product and then I can
41:09kind of sort this to see for example
41:12which products have a heavy Reliance on
41:15bar for example these three products
41:17have almost four products here all have
41:20about 10% of Reliance on bar F so if he
41:23goes on that 3month long track these
41:26products are going to take a big hit uh
41:28whereas further down here um not so much
41:31Reliance still pretty high but not a
41:33very high proportion and needless to say
41:37you can just have this column you don't
41:38even have to have any of these columns
41:40and the numbers will still calculate so
41:42for example if I just take out those
41:45guys you'll see this will come up here
41:48the only caveat here is if you're seeing
41:51this and if you now have this slicer for
41:53whatever reason and if you select for
41:55example someone like gigy bowling
41:57you'll see the percentages are
42:00calculated as of bar against gig's
42:03values so now you're doing a comparison
42:06between this and that so normally here
42:08that's not the intention but we had to
42:10put the slicer there so I could
42:12demonstrate how calculate can punch out
42:15one person and move the context to bar
42:18forny we could add more than one
42:20condition so here if we look at bar
42:22amount we just looking at bar foring if
42:25you look at our uh product I'm going to
42:27go into the table view here and quickly
42:29switch to products you'll see that we
42:32have different products but they are
42:35categorized into a few categories we
42:38have essentially three categories we
42:39have got bars we have got bytes and we
42:42have got other category so I want to
42:45know what is the amount that bar Fon
42:48brings in just from the bars category I
42:52call this as bar bar amount let's go and
42:56build that to build this again we make a
42:58new
42:59measure we'll just call this as bar bar
43:03amount here the criteria is twofold or
43:06the filtering needs to be twofold the
43:08first filter needs to be on bar Fon the
43:10second filter needs to be on bar
43:13scategory so again we can say calculate
43:16Open Bracket total amount people
43:19salesperson is equal to bar on the next
43:22one you just comma and then write the
43:24next one product product is bars
43:28so we close the bracket we can as you
43:30start writing longer Dax Expressions you
43:33might realize that writing everything in
43:35one line is a bit of pain you can also
43:37go place your cursor anywhere and then
43:39press Alt Enter to get into the new line
43:42and then you can press tab to neatly
43:44indent that so you'll see people doing
43:47this sort of a thing they start writing
43:49these kind of multiple line code so now
43:53calculate function broken down into
43:55three lines it's essentially the same
43:56thing but you can see what is the thing
43:58that it is calculating what the first
44:01criteria is and what the second criteria
44:03is you can pretty much put any number of
44:05criteria I don't want to call them as
44:07criteria they're actually filters so the
44:09first filter is people salesperson
44:11should be bar fony second thing is
44:13products product should be bars and the
44:16intention here is we want to calculate
44:17what is the amount for bar fonny in the
44:21bars
44:22category and I'm going to commit this
44:26and uh we'll add this as a currency
44:28formatting with the zero
44:29decimals let's just see that on the
44:32screen unfortunately we are not getting
44:34the result what do you think
44:37happened the problem was we are using
44:39the product column instead of category
44:41column so it should be category and the
44:45moment you fix it you'll get the numbers
44:49here you can see that this is how that
44:51looks bar bar amount you might again say
44:54oh what happened why is he not doing
44:56these products
44:57these products are not bars category
44:59they're actually byes category so
45:01obviously a bite category wouldn't have
45:03bar amount because they're mutually
45:06exclusive and that's why that's not
45:08working but once this is there in this
45:11context here when I'm looking at this
45:14probably my criteria wouldn't be product
45:16I'm not really looking at a product
45:18perspective I might want to look at this
45:19information from a geographical
45:21perspective so I'm going to duplicate
45:23this page because I want to keep this
45:24for your reference and here I'll just
45:28change the product to
45:30geography and then I can see in each
45:33geography what is happening for bar so
45:37I'll rearrange these things I'm going to
45:38move them like
45:41that so Canada total amount 2.6 million
45:45250 1,000 comes from bar which is
45:4899.5% and 141,000 comes from bars bar
45:54sale so that's bar bar amount
45:57this much you can kind of add more
46:00filters into it for example what was the
46:03amount for bar bar in the month of March
46:07and you can see that as well another
46:09thing that you may want to do with
46:10calculate especially when you're
46:12building filters like this is let's say
46:14you're looking at bar amount this is
46:16fine but if I want to do a special
46:18analysis where I want to look at four
46:21people Baron B muet I'm just going to
46:25highlight them here you can see bar Aron
46:28bever I want to look at Donnie I also
46:30want to look at Hussein these four
46:32people I want to know what is
46:36happening then you could build a complex
46:39calculate function here with all the
46:41four people or you could also use a
46:44simpler operator so I'm going to show
46:46you how these work so we are going to
46:48call this as total amount my team V1
46:52we're going to do two versions of this
46:54measure so V1 for the first version and
46:56the first one is calculate total
47:00amount and then in the next line I'm
47:03going to say sales
47:05person is bar Fon now we not just
47:11stopping at bar Fon we would like to do
47:13this for all the four and then together
47:15calculate what the total amount is so
47:17this is where the r condition comes in
47:19because we are trying to do the check on
47:21the same person again so here we use two
47:24pipe symbols to indicate our criteria
47:27and then put the rest of them so the
47:30second one is bever Mett third one is
47:32chess bonell and the fourth one is
47:33Hussein AAR so this two pipe symbols
47:36indicate R criteria and when you apply
47:40this it'll create a measure that looks
47:42at any of these four people let's add
47:45this to our table here so 44 million
47:49total sales my team brings in $8.9
47:53million so this is version one of this
47:55we're going to do two versions I'm going
47:57to take out this slicer so we have more
47:58space to play with these values here the
48:01second version is when you have multiple
48:03people it's a bit of pain to write these
48:06each thing once and then put the pipe
48:09symbol so we could also use in Clause
48:12just like how you can use in clause in
48:14SQL to do these kind of things so we
48:17will copy
48:19this and make a new measure paste it
48:22there change it to
48:24V2 and equal to
48:27instead of equal to we'll say in and
48:30then open curly brackets and comma
48:33separate all the
48:37four so in is basically just like SQL in
48:41you specify all the four values in the
48:43curly brackets and you put them in the
48:46double codes if they're text otherwise
48:48if they're numbers you just type them
48:50out whenu select uh and complete that
48:53it'll go and you can kind of uh see the
48:57values again the values do match its
48:59same numbers it's just one of them uses
49:02in clause which is a bit more convenient
49:04and another one uses this kind of a long
49:08or operators one after another so that
49:11is another way of using calculate
49:13especially if you have got a filter
49:16where you want to look at multiple
49:18people in one
49:21go another crucial aspect of Dax and
49:24pretty much any kind of coding or or
49:27logic Building Systems is conditional
49:29logic to demonstrate that let's say you
49:32have got a simple table like this where
49:34you're looking at salesperson and what
49:37is the amount they are bringing in this
49:39is fine but let's say you are have an
49:42upcoming budget meeting or you are doing
49:44a performance review and you want to
49:46know which salese have met their targets
49:49there is a target of $2 million per
49:51salesperson in our organization so I
49:54want to know who has met the target and
49:57who hasn't met the targets so in this
50:00case we want to do a conditional logic
50:02where I want to take this number compare
50:04that with 2 million and then print an
50:07outcome here that could be like yes you
50:09have met the targets so this is where
50:11the conditional logic functions come in
50:13very handy once you understand them and
50:15once you get the basics of how to build
50:17the base measures and how to use
50:19calculate that alone will take you
50:22really far in terms of analyzing data
50:24and producing actual meaningful out
50:27outcomes from your data so here to do
50:29that I will add a measure we could kind
50:32of hardcode everything but I thought
50:34this would be a better approach so the
50:35first measure that I'm creating is
50:37called sales sales Target and this is
50:40simply hardcoded to 2 million what this
50:43does is it gives you a flexible way to
50:45adjust the number if your business
50:47requirements change later if you put the
50:502 million directly into the conditional
50:52formulas then changing it becomes a pain
50:54so we have got a sales Target which is a
50:56measure that that just always comes up 2
50:58million and I can see that in the table
51:00as well if I put that for everybody it's
51:022 million including at a grand total
51:04level now the next measure that we want
51:06to do is Target comparison V1 we are
51:11going to do four versions or three
51:13versions of this measure so we'll do
51:14this with V1 first and here we can use
51:17the IF function if and if you have used
51:20if in Excel python or Tableau or any
51:23other systems it's exactly same logic if
51:26if and you build a condition logical
51:28test The Logical test here is I want to
51:30see if your total amount is more than
51:32the target so total
51:35amount greater than sales Target if so I
51:40want to say yes else I want to say no so
51:44if you look at the nature of this
51:45measure it is printing the word yes or
51:47no depending on how that comparison is
51:49and once I add that I can just print
51:53that here you can see I'm doing yes no
51:55comparison a lot of people have yes but
51:58we also have some people not meeting the
52:01targets and they're all having no
52:02because their values are under 2 million
52:05so this is one way of
52:09doing here a key thing that you want to
52:12remember is let me just flash this again
52:14we using the IF function to compare two
52:17things so the comparison between two
52:19things need to be just two individual
52:22values they can be numbers dates text
52:24values it doesn't really matter but they
52:26have to to be two individual values more
52:29technical way of referring this is they
52:31have to be scalar values a scalar is
52:33nothing but just a single value so it
52:35has to be a single value and usually in
52:39business situations like this they will
52:40be measures what happens is if you do
52:44this comparison wrong or if you compare
52:46with a table column one column versus
52:48another then you will get into some
52:49trouble so for example um not that you
52:53will write like this but it is likely
52:55that uh in you might actually end up
52:57doing this sort of a mistake in early
52:59stages so we'll do this target
53:04comparison V V2 and here if and instead
53:10of picking a scalar value I'm going to
53:12pick a table column so if I'm going to
53:14say shipments table in fact it won't
53:16even let me do it uh in newer versions
53:18of powerbi but um you could kind of
53:22write this one way or another if you're
53:25using power pivot in Xcel or an older
53:27version of powerbi so if I'm saying
53:30sales shipments table amount column now
53:33you can see that the auto suggest is not
53:35even giving me that option it is saying
53:36you have to pick a measure here it's
53:38only going to work with scalar values
53:40but somehow I brute force my way into
53:43this and I'm saying if shipment amount
53:45column uh is greater than sales
53:51Target already red lines are coming in
53:53here indicating we have a trouble but
53:55let's push ahead with this I'm going to
53:57say yes
54:00no close bracket hit enter it's just not
54:04going to work you can see the red line
54:05is already there and the dreaded error
54:08comes in
54:10here it will give you this sort of an
54:12error a single value for the column
54:14amount in the table shipments cannot be
54:16determined this can happen when a
54:18measure formula refers to a column that
54:20contains many values without specifying
54:22an aggregation such as minimum maximum
54:24Etc so it wants a scalar value an
54:27aggregation basically you can't do one
54:30column versus another column kind of a
54:32comparison with this function there are
54:34other functions where you could do this
54:36but if function switch function which is
54:38another way of doing these kind of
54:40logical tests they are equipped to do
54:43one-on-one comparisons pretty much every
54:46time a measure whether it is any formula
54:49or any other kind of measure that you're
54:51building a simple let P test for that
54:54measure has to be it has to come up with
54:58a single value right it cannot return a
55:01bunch of values it has to be a single
55:03value so the measure is usually an
55:05aggregation process it just takes all
55:07the values and sums it up or it Compares
55:10One value with another and comes up with
55:11the third value whatever may be that
55:13process uh it has to always come up with
55:16single value and if it doesn't work then
55:18internally this sort of a mistake might
55:19have
55:21happened anyhow this doesn't work I'll
55:23leave that there in the model just in
55:25case you want to refer to to that and we
55:27will do another
55:30measure just as you can print yes no you
55:33can also come up with some interesting
55:36or innovative ways of doing this so for
55:38example our Target comparison I can get
55:42this spelling right that'll be good okay
55:45Target comparison version three and this
55:47time we going to say if total amount is
55:50greater than sales Target if so instead
55:54of yes no you may want to print a thumbs
55:57up or thumbs down kind of a symbol and
56:00for this you can use Emoji this is one
56:02of my favorite early tricks and dags
56:05that I like to teach people and it kind
56:07of Lights them up so hopefully it helps
56:09you as well so open double codes and
56:11then press windows and Dot key together
56:14this opens up the Emoji keypad on your
56:16computer again the keystroke is Windows
56:18and the period or dot key together from
56:22here you can pick any Emoji so I'm going
56:24to go in and uh find the thumbs up emoji
56:28I think it's somewhere here yeah here so
56:32if they have met the target then we give
56:34them a thumbs up that reminds me if
56:36you're enjoying this video maybe you
56:37also want to give thumbs up
56:40and else thumbs
56:42down we can add that and in the table I
56:45can add it and you can see that as a
56:49indicator as well so again this is a
56:52very simple cool way of looking at the
56:54targets and then seeing it obviously
56:56viously in a business reporting you may
56:58want to be a little bit more gentle with
57:00these emojis you don't want to have
57:01laughing out loud kind of an emoji there
57:04because probably it doesn't make sense
57:06but again it all depends on what the
57:08context is and who is seeing the reports
57:10so that is how you can do the if if kind
57:12of a comparison if only lets you do
57:15oneon-one comparisons but if you have
57:17got multiple comparisons to do like you
57:19don't have a single sales Target you
57:22have a sliding scale of sales Target so
57:25you have a target of two million 1.5
57:27million 1 million and then you want to
57:29see where people have met their target
57:31some people have more than 2 million
57:33some people have more than 1.5 some have
57:35more than 1 million so I want to give
57:37them different symbols or different
57:39messages in that case you can use the
57:44switch function what it does let you is
57:47it will let you kind of do multiple
57:50comparisons using a kind of a ladder
57:52structure you can also use the IF
57:55function and kind of Nest one IF
57:57function in another just like how you
57:59could have done this in Excel or other
58:01Solutions even VBA or coding systems
58:04just put one if inside another and that
58:06also lets you build that I'm leaving
58:11this as a homework assignment for you
58:13print different symbols or different
58:15messages and people are more than 2
58:17million 1.5 1
58:21million so now that you have understood
58:24the 80% of the important vital and
58:27essential Dax Concepts let me conclude
58:30by giving you five tips for writing
58:33better Dax I call this as ACM Buu or
58:37ammo it's a lousy acronym but a great
58:39technique let's take a look at
58:42this a stands for acquire business
58:45knowledge you can't write good DXs if
58:48you don't understand the underlying
58:50business and what your users need so any
58:54good kind of data analysis business
58:56intelligence project must begin by
58:59acquiring proper knowledge about the
59:01underlying data sets what your users
59:04needs are how they plan to use the
59:07reports how often data is updated and
59:09all of that so spend quite a bit of time
59:13doing this phase correctly if you get it
59:15wrong everything else is just going to
59:17be useless so understand what your users
59:20need understand what your data is able
59:22to provide and then build from there C
59:26stands for cleaning the data if you
59:28don't have clean data you cannot solve
59:31the problem easily using Dax so Dax
59:35should not be the fix to your lousy data
59:37problems so spend quite a bit of time
59:40cleaning the data making sure that it is
59:42in the right shape size and quality
59:46before you start thinking about modeling
59:48and analyzing the
59:50data M start for model for the needs you
59:54might have the same data but depending
59:57on user a needs you may have to create
1:00:00one kind of model and user B's needs you
1:00:03may have to create a different model so
1:00:05keep that in mind your data can always
1:00:08be there but your model should reflect
1:00:10the underlying business needs of what
1:00:13your audience wants and this is where
1:00:15the thing is in Step by-step progression
1:00:17you have to have good knowledge of what
1:00:19your users need and then clean the data
1:00:22accordingly and model it accordingly you
1:00:25can't have in many proper bi situations
1:00:28one fixed solution for everything I mean
1:00:31the data sets and majority of the
1:00:32concepts can be same but some of the
1:00:34modeling needs to change depending on
1:00:37what is happening for your users how
1:00:39they expect to see the
1:00:42results and b stands for blocks not
1:00:45Black Box what I mean by this is think
1:00:48of your Dax as individual blocks that
1:00:52you can stack one on top of another to
1:00:54build a massive Cathedral you don't want
1:00:56to write a 300 line Dax piece just to
1:00:59solve one problem instead construct it
1:01:02in a more logical manner just like how
1:01:04we have done earlier we have got these
1:01:07individual things but we used smaller
1:01:09chunks to individually build out the
1:01:11logic and then combine them to come up
1:01:13with amount per shipment or boxes per
1:01:16shipment so think of it like that don't
1:01:19write complex logic in one go instead
1:01:21break it down to smaller segments and
1:01:23build it in a way so yeah I call this as
1:01:26blocks not
1:01:28blackbox and U stands for using
1:01:31variables and using Dax query view when
1:01:34you got stuck we haven't covered either
1:01:36of these Concepts in this video as these
1:01:38are slightly more advanced but once you
1:01:40start building the knowledge you will be
1:01:42able to come to a place where you'll
1:01:44find that if you write your Dax in such
1:01:47a way that you're using variables and if
1:01:49you are getting stuck using that Dax
1:01:52query view can help you a lot so those
1:01:55are my five practical tips I call them
1:01:58as Abu acquire business knowledge clean
1:02:01data model for the needs build blocks
1:02:05instead of black box and using variables
1:02:08and Dax query View and other features to
1:02:11understand and explore your data better
1:02:14when you get stuck all the best in your
1:02:16Dax Journey I'll catch you in another
1:02:19video bye

About this transcript

This page contains the full transcript of Learn 80% of DAX in an Hour (with FREE sample file) by Chandoo, generated from the public captions YouTube serves with the video. The transcript has 10,663 words across 1,470 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.