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

one of the most frustrating things when it comes to learning powerb is understanding and effectively using the Dax language this is a problem that I had when I started learning powerbi many years ago so in this video let me distill the 80% of the Practical Dax when it comes to data analysis and business intelligence reporting work these are all the concepts that we are going to cover in this video and by the end of this video you will be able to understand what Dax is how to use it to problem solve given a business requirement

towards the end of this video I'm also going to talk about a proprietary almost trademarked ACM Buu Aku approach of using Dax more on that later let's go here is my powerbi workbook I have provided a copy of this file as well as the blank data set if you want to build the whole data model yourself I explained how this data model is constructed in the previous video essentially this is a data model for a madeup chocolate company called awesome chocolates and here we have got five tables let me show you the semantic or the

data model quickly so here is the data model that we are using we have got chocolate shipments in the fact table at the center of this model and we have got four dimensions neatly laid out in the star schema pattern here normally for the Str sake of laying things out you may want to set them up like this kind of a octopus or a star schema setup but more from a maintenance perspective it might be just a good idea to keep all the dimension tables on one side of the screen like up top and move

the fact tables to the bottom this way if you have if your model ever gets too big you will always know what is a d Di menion and what is a fact dimensions are always up top and fact is at the bottom and from the dimension the filters move to fact that's why the arrows always Point towards the fact so you can see that if you were to filter for example by a specific salesperson their details are shown here in the shipments table more on this later but for now this is how our model looks

like again a quick reminder a copy of this file is a aailable in the video description the blank file download it and follow along with me as you practice taxs as this video can get pretty intense I highly recommend setting some time aside to go through this whole thing in one setting for best results now let's go and add a new page this page is something that we built in the previous exercise where I explained how we have constructed that particular data model so here we're going to start our concepts with essential data Dax what

is Dax Dax is the language that is used to calculate things in powerbi or more importantly in power pivot Dax stands for data analysis Expressions you might think why doesn't it call why don't they call it D Dae because data analysis Expressions but I don't know they went and called it Dax just as the acronym is confusing some of the Dax logic and measures and the way we work with it is also a little bit confusing especially when you start learning it so that's why it is important to get the fundamentals right and that's my

purpose of making this video so we have got all of these tables here and in this previous page here we were able to see for example how much is the total amount by a product to do this what we did is we put product onto this axis of the column chart and amount into this area or vertical axis or Y axis of the thing as amount is a numeric column if I look at my shipments table I have seen that amount is a number column the moment I put that into the y axis here powerbi

automatically did a sum of amount that means it is summing it up we could for example click on this here and use this summarization option there to change it from sum to minimum maximum count or average or median this does offer you a quick and easy way to customize some of the calculations but the problem with this is this is quite limited and you can't really do much when you actually want to analyze the data and produce some sort of a business intelligence or deep insights into your data and that is where Dax offers a

structured framework to talk to your data ask business questions and get the results so it gives you a language and a framework to do all of this we are going to do that from now on so the calculations that are produced by Dax they're usually called as measures and we are going to write our first measure and eventually everything starts to click in so our first measure is to pretty much replicate what we are seeing here we want to understand what is the total amount some amount and then be able to see it at a

product level so I'm going to go to a new page doesn't matter whether you do it in that page or here but uh we going to keep it clean by building it in this page to create a new measure you can go and click on many places in the screen it is kind of right in your face uh you can right click on the shipments table you will find new measure option here as this is quite an important feature powerbi also puts this button right there on the screen in the home ribbon and there is

a whole modeling ribbon available where you will have replica of that button plus many other activities that we can do with Dax so it is always there and if that is not enough they have now added a Dax query view into powerbi this is I think uh still a preview feature I'm not really sure but it is also there through which you can kind of systematically code more Dax queries and other things we will go there uh towards the end of this video but for now I'm just going to this is my favorite way of

doing I'll right click and then use the new measure and it will add a formula bar up top here this is where you usually write the measures and uh when you are done you click on this tick mark to commit that measure you can also press enter and that gets activated as the measure whenever you are typing the measure you will also see a special ribbon called measure tools get activated this is only available to you when you are either writing or editing a measure so you can't you won't see it when you're outside and

through this you will be able to adjust some of the settings for that me you can also go to the model view and do that customization there uh we will go there in a minute but for now our first goal is to get the total amount so a measure will have two parts name of the measure equal to sign and then the business rule or the logic for creating that measure let me just expand the text area here to expand you can hold on the control and use the scroll wheel on your mouse to kind

of make it bigger or smaller so here the measure name name would be total amount and equal to and we just want to look at the shipments table this amount column here and then add it up so the way this works is you just write Su of shipments table you can type everything you can also use this Auto suggest to pick what you want so for example I'm using my arrow keys here to pick the amount and once I select I can hit the Tab Key to fill that for me so sum of shipments amount

is my total amount and we will get the total value once it is done you can either hit enter or you can click on this commit button I like to just hit enter because usually at this point I'm on my keyboard it'll be done once a measure is added it will go and sit in the table on which you right clicked and created the measure so in this case shipments is the table on which I right clicked so my total amount is also attached to the thing there measures will have this little special symbol next

to it it's the calculator symbol because they're doing calculation a measure will not only have if you select this measure here you can see that a measure has a couple of things a measure has a name the definition of the measure the business rule or the logic here the rule or the logic is go to the shipments table take the amount column and sum it up that's the logic that we are writing apart from these two things a measure can also have display behaviors so here you have got a whole formatting tab that is currently

set to Auto so if you don't do anything it'll be automatically formatted based on what powerbi thinks this value is but you can set it because many times when we create this measure on the screen I may want to see it in a certain way so for example as this is an amount I'm going to go into my format area here from General I'm going to switch to currency and I'm going to say I don't want any decimal points so I'll say zero decimal points there so now that the measure is created I can click

on the white space to go back to my canvas and I can see this measure normally when I'm learning or explaining Dax to others I like to use either a table or Matrix visual because these are just numbers and I can see the numbers I can kind of connect the dots better so we going to use a table Visual and here in the shipments table I have got total amount first I want to see how much is that amount per product so I can go to the product table bring product and then bring the amount

so here you will see how that amount is for each product it automatically calculates what that amount is and at a total level it also tells you what that amount is so this is how a measure works if you see these are the numbers coming through my total amount column if I were to now take just the amount column and put it into the table it will also do a sum of am amount autoc calculation this is how our column chart created by the way and you'll see that the numbers match $380 $380,500 and it's

the same value here we don't have the decimal points here we have decimal points but exactly same and at a grand total level also you can see everything adds up nicely so both of them the total amount and the sum of amount which is autoc calculated by powerbi are doing the same thing and this is one of the things that kind of frustrates the new Learners or people who haven't used powerbi much but come from let's say some other tools and then start using it and saw some demos they might be thinking okay this looks

like it's giving me the same answer with a lot of work why do I even need to learn Dax when I could get the answers already and that is a fair point the problem is like I said earlier this thing the sum of amount which is autoc calculated is a restrictive option it doesn't give you many choices you can either sum average count Etc whereas our total amount column the one that we explicitly calculated gives us so much more freedom to begin with at a very simple level I can write the words that I want

to call it for example I can say total amount instead of sum of amount total amount makes so much more business sense than and uh the kind of AI sounding sum of amount word on top I can assign some formatting to it so the moment I put it in my report it automatically formats but these are kind of like very trivial benefits the real benefit and the real power of Dax lies in the fact that it lets you build your own calculations based on your business rules and policies so from here on out we are

going to explore the powerful side of the Dax and get more interesting and Innovative with our learning into Dax so I want to conclude this segment with one extra note which is the names that we use especially once you start to learn and get technical with this is these are called explicit measures and these are called implicit measures what it means is in in this case here we are just dragging the amount and dropping and powerbi automatically implicitly creates that calculation for us whereas here we are explicitly calculating things so normally once you start your

journey into powerbi and you learn the basics and you move on to the Dax stages you will pretty much use only explicit measures so here on not make a promise to me pause the video and say it out aloud I will not make any more implicit measures no matter how convenient they are I will not make them so say the Pledge I will not make any more implicit measures and it sounds corny but this is an important thing to keep in mind if you want to grow your powerb skills and improve your Dax understanding all

right so now that we have the total amount and we see that in the table here one other thing that kind of tips people off is okay I see this in the table doesn't mean this also exists in the data and when you go to the data View go to the data and select the shipments table you don't see any columns here there is no total amount column added whereas if you see here in the fields list you'll see all these fields and then you'll see the total amount as another button here where is this

one way to think about this is if your shipment table is this hand this first store hand and total amount is this extra calculation that we built it doesn't belong in the hand but it acts on the hand so it can kind of take the hand and twist it and turn it to tell you what the value is for each product or at a grand total level but this hand is not part of that hand it's just that they are laid out for the sake of Simplicity on the screen in the same table but the

column or so the measure total amount never really belongs in the table it is just something that acts on top of the table so this is something that you want to put put in your mind find and refer to that analogy every time you see a measure a measure just acts on the table it's not part of the table just as we could do total amount you could also do averages counts and other things to demonstrate those I'm going to take out the sum of the amount the implicit measure and just use the total amount

now we can again write click here and write a new measure and this measure would be for example number of shipments each row in the shipment table is one shipment so I just want to count how many shipments are there in total so we can call this measure as shipment count and here the function that we are using is Count row count row accepts a table name so count rows of shipments and again that will give you shipment count while we are in the measure tools I can apply a thousands formatting for that and then

I can add that to my table to see how many shipments we are doing by individual products so we have done a total of 7,95 shipments and this is how it looks at a product level as we are writing these measures you might see oh these kind of look like how I would write my Excel functions in Excel we have got some function in Excel we have got countif function they follow the similar syntactical pattern you have got a function name Open Bracket and either columns or values so another big mistake and this is a

mistake that I have made in early stages of my learning of powerbi and power pivot is we think oh this looks just like Excel so it is Excel then what happens is in our mind every time we want to write a measure or build some logic we're trying to use our Excel mind to solve the problem and that's not going to work in powerbi World think of this like this the alphab bet that is used by Dax and Excel functions is same they follow the same syntactical pattern which is you have got a function Open

Bracket parameters close bracket kind of a notation so in Excel we have got some function in powerbi we have got some function they look same because they share that kind of a syntactical pattern the grammar of the language and the alphabet of the language but they are two different things one way of thinking about this is imagine you know English very well now you take a plane and you go to France you'll see all the road signs all the building signs all the messages and metros and everything in pretty much English alphabet I mean French

has some extra letters but the alphabet largely looks like English so you might think to your mind that oh this looks like English let me try and read it like English and understand like English you it wouldn't make any sense you wouldn't be able to even order a cup of coffee if you use your English mind this is because they share that kind of the alphabet but they're two different things p p great okay faster so it's the same way with the Dax and Exel functions they kind of look same simply because it is a

convenient choice for the developers to make when are designing this language but they're two different things so from here on out discard all your Excel knowledge when you are looking at Dax don't try to think back and try to connect the dots with Excel instead approach this as a fresh new language that way you'll be able to learn better and you don't have to carry this extra baggage with you everywhere hope that helps now let's go and look at shipment count closely it has a special function called count row you can see that here this

countr function doesn't even exist in Excel Excel doesn't have such a function but powerbi does so Dax language has hundreds of functions some of them like sum and count and average share the same name as Excel functions but they're just because that's an obvious thing to do but Dax offers different set of functions and the behavior is almost always different when you write a function whether it is total amount or count rows here you just specify what you want you don't go into all the specifics you only specify the business rule or the behavior for

example shipment count is how many shipments are how many rows are there in the shipment table that is the business rule when you apply the measure into a specific visual here I have got a table visual with product name in each row the shipment count will be calculated for that product automatically using a concept called evaluation context this is where whenever you have a measure when you create you just create the definition of it so whether it is shipment count or total amount we just specify the Bare Bones naked version of that definition and we

don't go into any specifics but when the measure is laid out on the screen depending on what the purpose of that visual is what is there on that Visual and what else is there on the scre screen power bi well technically power pivot automatically calculates the value using that evaluation context so for example here the evaluation context for that number 86 is product column of product table is 50% Dark Bites so behind scene what happens is if you go to the date model this is where the model is really helpful if you look at it

the shipments table has the shipments count so here is my measure but this count is defined as number of rows in the shipments table but at the time of calculating that particular number on the screen we have already looked at a specific product so the product has been selected here 50% Dark Bites and once that product is selected that means the product table is filtered down to just one row it doesn't really have all the rows it is only looking at that product and look at this line here this line says if the product table

is filtered the filter should go and apply on the shipments table as well so that's what the direction refers so once this is going here here shipment table get also filtered down just to product that first product's number of rows and at that point it just counts how many values are there how many rows are there and then comes back as shipment count this is why in the definition of the measure we don't really think about how to do this for a product specific value even though the business requirement might say I want to see

how many shipments we are doing by product you don't really have to worry about the product thing here you just write count shipments there alone once that is there I can use it in the context of this visual to see how many shipments are there for that product I can move this and in this space here for example I can put a column chart and in this column chart I can put our geography on x-axis and add shipment count on y axis and then now I'm seeing how many shipments we're doing at a country level

here the evaluation context is count is UK so automatically this Geo column is set to UK and locations table from six rows it shrinks down to just one row and then the filter will go into shipment table because there is a model connection there shipments table will also shrink down to just the UK's corresponding shipments and then the count shipment count will execute just for the row in that shipment table at that point in time every little thing that you do on the screen will impact this so for example see what happens the moment I

click on UK you'll see that here this count is no longer the earlier number it is now down to 15 this is because the evaluation context has changed earlier we are just looking at 50% AR bites but now we are interested in what is the value of UK for 50% AR bites in terms of shipment count so the evaluation context for this number is now it has to filter the product it has to also filter the UK if there is any other filter so for example if I have got a slicer on my team members

names or if I've got a filter on my dates or anything all of that those will get to decide what the evaluation context is and this is why when you look at any number on the screen it is important to understand and what is going on on the screen to interpret and understand that number so that what is going on on the screen is pretty much referred to as evaluation context all right so we have got total amount shipment count and I can add other things as well if you right click and say new measure

you will be able to for example do something like total boxes and this is nothing but some of the boxes column in the shipment tables some shipments boxes and we can put this into thousands with zero decimals and I can add this and I can see 3.78 for million so the next concept that we're going to explore is while you could do sums counts averages and other things I'm not going to go into individual little things like how to build an average measure or how to do minimum or how to do maximum because those are

are fairly obvious so I'm not bothering with that but the next concept that is kind of important to understand and explore the Dax betteries for the sake of that I'm just going to delete this visual uh we have more screen space for this is combining or reusing measures so right now think of these measures as a little assets that you're building so we've got three assets we have got uh shipment count we have got total amount and we have got total boxes on a standalone basis they just tell you what is happening for them but

now think of actual business requirement where I want to know which products have more boxes per shipments that means the shipments might be fewer but we'll actually send more boxes of chocolates in those shipments so I want to analyze that this measure we can think of it as boxes per shipment and the logic for this is we take this number and we divide it with that number that's pretty much it so how do we develop this this is where the ReUse concept comes in because we have already built these three assets total amount shipment count

and total boxes I can create a new measure right click new measure and then I can call this as boxes per shipment and here we already have both of them so I can say total boxes so you open the square brackets and then you say total boxes divide sign with shipment count and you can click okay to add that so now that measure is added I can select the visual and I can put that on there and I can see boxes for shipment how we are doing so in terms of for example our data here

this is how it looks if I'm seeing just the total amount I'm going to sort this in ascending order you'll see that 50% Dark Bites is our lowest selling product 380,000 but if I'm looking at boxes per shipment you'll see that EES is actually our lowest product because we only ship 152 boxes of EES per shipment whereas 50% Dark Bites is further down it is in the fourth place with 295 so this kind of a composite calculation exposes interesting and useful information about your data and we can build all of these is by simply reusing

the measures that are earlier defined you don't have to write the whole thing again if you look at this we are just saying get that number get this number divide one with another so we don't have to write the whole sum and count logic again we just point to those things and boxes per shipment is a generic measure so I can use it in the context of product or I can add a page and in this page if I I may want to explore what is happening at our person level so I can go to

people table put sales person and then see what is the boxes per shipment at a person level and then I can even Analyze This to see uh who is doing more boxes per shipment so for example rodie wone juu they're all doing 500 plus boxes per shipment whereas further down here we have got meline van and gigy doing around 400 per shipment again this sort of an interesting Insight is easily achievable simply by combining those two measures you might be having one nagging question at this point which is if you look at the way we

defined boxes per shipment we are saying total boxes divided by total shipment count now let's take a look at another measure like total boxes in case of this we are using shipments boxes if you carefully observe this we're saying table name column name this is the notation that we are following to refer to a specific item how come we are not following that convention when we are doing this this is because if you remember my earlier example where I said a measure doesn't really belong in the table it is just laid out in the table

for the sake of Simplicity that comes back to us now the measure total boxes or shipment count is technically not in any table it is just laid out here for the sake of visual uh Simplicity and finding it there that it is in that table but it doesn't really matter which table it is it is always going to come up with the same value so a measure technically doesn't belong to a table it belongs to the entire data or semantic model so measure is part of the semantic model and when you refer to the measure

you don't have to have the table name you can put the table name it won't bother about it but you you don't have to do it and the second idea here is as a best practice you don't want to put table name ever in front of the measure just always refer to measures by themselves this way if I'm looking at some complex piece of Dax code and it refers some measures and some table columns imagine these are simple on line ones but pretty soon you will write like 20 line Dax measures and there could be

multiple things multiple table columns being used as well as measures when you are looking at that jumble anytime you see a format like this table column you know that oh this is a table column and anytime you see just square brackets without the table name you automatically know that oh that is just a measure so this is actually a best practice that will help you later when you are developing more complicated things so for that reason we don't have to use it going back to this table here let's use the reusability concept ccept once more

this time to Define amount per shipment so we doing 1881 shipments $713,000 of business here $713,000 what would be the amount per shipment again we can create a new measure and this is called amount per shipment and instead of using the Divide sign directly powerbi also offers a safe divide function called divide what this does is it will try to divide but should there ever be a divide by zero scenario because you have filtered down or narrowed down the datas to a level where there is not many options it will give due by zero error

if you just do a hot divide so this basically does a safe division you just specify numerator and denominator and it will do the div division for you so numerator here is my total amount and denominator is shipment count and you can also pass an alternate result normally I don't do it if you don't say anything it'll just blank out on the screen so here I'll hit enter and we can add that to our table and again I can see what is the amount per shipment we are doing again you can apply measure tools formatting

to it for example I can say it should be in currency with the zero decimal points and I'll see that and I can apply sort orders and all of that needless to say once you have these kind of composite measures that reuse the concepts from earlier you can just keep these two alone and take out all of them they'll still work we have actually seen it here I'm directly seeing boxes per shipment per person without seeing the individual bits and you can use the rest of the other good things here as well so for example

this is my data now if I have got a bar chart here with my geographical breakdown of shipments so I'm looking at this and I may want to understand what is the amount per shipment in UK looking like if I click on this automatically all of this will update and I'll see what is the product with more amount per shipment in UK so it is organic choco syrup but if I go to Canada it's $8,000 for Alman chako because again we have implemented the sort order as soon as I click on Canada the numbers change

and the sort order kicks in again so this opens doors for some really interesting and Powerful analysis of your data simply because we have created three base measures these three are our base measures and two composite measures one is doing this divided by that and another is doing this divided by that again if you look at this data for example you might be exatic thinking oh $8,000 per shipment we should ship more of this Alman choco to Canada and then you come to the actual shipment count there was only one shipment so it's basically an

outlier rather than a trend and more interesting patterns are somewhere down here where we are doing 40 or 30 shipments and these numbers are a bit more reliable and at this point you can actually see the power of power pivot already Dax already it lets you build these kind of things which are not possible with the implicit measures if I'm just adding sums and counts and averages I would never be able to go in this direction the next concept that we are going to explore is probably the most game-changing and most useful most applicable Concept

in all of Dax and it is the ability to change the filtering or the evaluation context I'm going to add a new page for this and let's explore a typical business problem let's say I'm looking at a table here and I want to see what is happening at our product and then how much is the total amount so two simple things and one of our employees bar fon I'm going to just bring them up here if I go into people table you'll see that Baron one of our employees and their ID is sp01 I responsible

for Baron he's one of my sales members so as I'm looking at this I know that 50% AR bites total amount is 380,000 I would want to know what is the amount that bar fonny is bringing I like to call this as bar amount how do I go about this because if I'm looking at this and if I'm interested in bar Fon amount I could for example theoretically add a slicer and then put my salesperson into this slicer and then I can click on bar Fon as soon as I click I'll see 50% Dark Bites

for bar bar Fon is 46,000 but I also lose the context of what was the original amount in order to go there I'll have to unclick and then see original amount is 380,000 so this is like a flicking a switch I can either have it on or off so this sort of a thing is not what I want instead what I want is I would want to keep these values as they are but add another column here and call it as bar amount and see what that number is for each of the products and maybe

add it as a percentage so what was the bars amount as a percentage and then do some exploratory analysis on it I I want to understand uh which products heavy r on bar maybe he's planning on going on a 3-month trick and we want to know what impact this would have on our sales so how do we go about this this is where powerbi power pivot introduces a really powerful and extremely versatile function called calculate what it does is it can take control of the situations that are happening on the screen and it can kind

of override them it's very tricky to explain because it is kind of like a Swiss army knife of the functions it can do a lot of things so there is no one easy way of explaining it other than showing it to you so let me show that we're going to write a new measure and call this as bar amount and the purpose of this is calculate the total amount just for bar F so here this is how the Syntax for this is we say calculate and you write an expression here this expression is usually an

existing measure in the model but you could also type the whole thing here so we'll say total amount and then we specify the filter criteria that you want to apply so we want to calculate total amount as if we are looking at bar foring so this is where we'll say people salesperson is equal to and then within double codes bar F GN my f close bracket and commit this let's apply currency formatting with zero decimals and let's see the puppy so here is my bar amount you'll see that I have 380,000 I have 46,000 as

well both of them visible to me all the time so I can see both numbers and I could make an informed decision so 44 million 3.6 million is brought in by bar Fon while looking at this just pause here and think what would happen if you click on the slicer and then select bar foron all right let me show you so if I click on Baron now you'll see that both of these match because this column here pays attention to the slicer and then it says oh you want total amount just for Baron I'm going

to show that the bar amount is kind of self-explanatory it will be same as what this is now imagine what would happen if I click on someone else for example if I go to chess bonell so if I click on chess can try and pause here and think what would happen if you click on chess you'll see that the total amount column here reflect CS the values for chess bonnel so all of these are for this little dude here what about these numbers they are stuck with bar foring this is what calculate does it overwrites

what is happening on the screen so the screen is saying show me chess bonel and the First Column respects that because that measure doesn't have calculate on it it simply just does the calculation for whatever is on the screen but the second measure this bar amount we're going to change color of this for that this bar amount is a special one it is using the calculate so it is saying calculate the value as if I'm looking at bar for so even though the screen is saying get me chest bonel this calculat comes in and it's

like a boxer it punches out chess bonnel knocks him out and then goes to Baron to get the value of that little guy and then print that there so that's really what a calculate does it overwrites it takes control of the evaluation context so that you can calculate things in a new light you can calculate anything it doesn't have to be total amount it could be boxes per shipment it could be amount per shipment or it could be something else that you have come up with whatever it is calculate can do that for you that

is why I said it is kind of like a very versatile and Swiss Army kind of knife kind of function it can do a lot of things so it's trickier to explain and it does look kind of very naive and simple but once you understand the power of it you can start to see ooh I could do this I could do that I could build these kind of calculations and that's opens many many doors for you so like I said I can do total amount bar amount and then I could also calculate bar amount as

a percentage so for example here I can say bar amount PCT is equal to divide bar amount with total amount and apply a percentage formatting with one decimal and then I can put that there I can see what is the percentage of bar foron for each product and then I can kind of sort this to see for example which products have a heavy Reliance on bar for example these three products have almost four products here all have about 10% of Reliance on bar F so if he goes on that 3month long track these products are

going to take a big hit uh whereas further down here um not so much Reliance still pretty high but not a very high proportion and needless to say you can just have this column you don't even have to have any of these columns and the numbers will still calculate so for example if I just take out those guys you'll see this will come up here the only caveat here is if you're seeing this and if you now have this slicer for whatever reason and if you select for example someone like gigy bowling you'll see the

percentages are calculated as of bar against gig's values so now you're doing a comparison between this and that so normally here that's not the intention but we had to put the slicer there so I could demonstrate how calculate can punch out one person and move the context to bar forny we could add more than one condition so here if we look at bar amount we just looking at bar foring if you look at our uh product I'm going to go into the table view here and quickly switch to products you'll see that we have different

products but they are categorized into a few categories we have essentially three categories we have got bars we have got bytes and we have got other category so I want to know what is the amount that bar Fon brings in just from the bars category I call this as bar bar amount let's go and build that to build this again we make a new measure we'll just call this as bar bar amount here the criteria is twofold or the filtering needs to be twofold the first filter needs to be on bar Fon the second filter

needs to be on bar scategory so again we can say calculate Open Bracket total amount people salesperson is equal to bar on the next one you just comma and then write the next one product product is bars so we close the bracket we can as you start writing longer Dax Expressions you might realize that writing everything in one line is a bit of pain you can also go place your cursor anywhere and then press Alt Enter to get into the new line and then you can press tab to neatly indent that so you'll see people

doing this sort of a thing they start writing these kind of multiple line code so now calculate function broken down into three lines it's essentially the same thing but you can see what is the thing that it is calculating what the first criteria is and what the second criteria is you can pretty much put any number of criteria I don't want to call them as criteria they're actually filters so the first filter is people salesperson should be bar fony second thing is products product should be bars and the intention here is we want to calculate

what is the amount for bar fonny in the bars category and I'm going to commit this and uh we'll add this as a currency formatting with the zero decimals let's just see that on the screen unfortunately we are not getting the result what do you think happened the problem was we are using the product column instead of category column so it should be category and the moment you fix it you'll get the numbers here you can see that this is how that looks bar bar amount you might again say oh what happened why is he

not doing these products these products are not bars category they're actually byes category so obviously a bite category wouldn't have bar amount because they're mutually exclusive and that's why that's not working but once this is there in this context here when I'm looking at this probably my criteria wouldn't be product I'm not really looking at a product perspective I might want to look at this information from a geographical perspective so I'm going to duplicate this page because I want to keep this for your reference and here I'll just change the product to geography and then

I can see in each geography what is happening for bar so I'll rearrange these things I'm going to move them like that so Canada total amount 2.6 million 250 1,000 comes from bar which is 99.5% and 141,000 comes from bars bar sale so that's bar bar amount this much you can kind of add more filters into it for example what was the amount for bar bar in the month of March and you can see that as well another thing that you may want to do with calculate especially when you're building filters like this is let's

say you're looking at bar amount this is fine but if I want to do a special analysis where I want to look at four people Baron B muet I'm just going to highlight them here you can see bar Aron bever I want to look at Donnie I also want to look at Hussein these four people I want to know what is happening then you could build a complex calculate function here with all the four people or you could also use a simpler operator so I'm going to show you how these work so we are going

to call this as total amount my team V1 we're going to do two versions of this measure so V1 for the first version and the first one is calculate total amount and then in the next line I'm going to say sales person is bar Fon now we not just stopping at bar Fon we would like to do this for all the four and then together calculate what the total amount is so this is where the r condition comes in because we are trying to do the check on the same person again so here we use

two pipe symbols to indicate our criteria and then put the rest of them so the second one is bever Mett third one is chess bonell and the fourth one is Hussein AAR so this two pipe symbols indicate R criteria and when you apply this it'll create a measure that looks at any of these four people let's add this to our table here so 44 million total sales my team brings in $8.9 million so this is version one of this we're going to do two versions I'm going to take out this slicer so we have more

space to play with these values here the second version is when you have multiple people it's a bit of pain to write these each thing once and then put the pipe symbol so we could also use in Clause just like how you can use in clause in SQL to do these kind of things so we will copy this and make a new measure paste it there change it to V2 and equal to instead of equal to we'll say in and then open curly brackets and comma separate all the four so in is basically just like

SQL in you specify all the four values in the curly brackets and you put them in the double codes if they're text otherwise if they're numbers you just type them out whenu select uh and complete that it'll go and you can kind of uh see the values again the values do match its same numbers it's just one of them uses in clause which is a bit more convenient and another one uses this kind of a long or operators one after another so that is another way of using calculate especially if you have got a filter

where you want to look at multiple people in one go another crucial aspect of Dax and pretty much any kind of coding or or logic Building Systems is conditional logic to demonstrate that let's say you have got a simple table like this where you're looking at salesperson and what is the amount they are bringing in this is fine but let's say you are have an upcoming budget meeting or you are doing a performance review and you want to know which salese have met their targets there is a target of $2 million per salesperson in our

organization so I want to know who has met the target and who hasn't met the targets so in this case we want to do a conditional logic where I want to take this number compare that with 2 million and then print an outcome here that could be like yes you have met the targets so this is where the conditional logic functions come in very handy once you understand them and once you get the basics of how to build the base measures and how to use calculate that alone will take you really far in terms of

analyzing data and producing actual meaningful out outcomes from your data so here to do that I will add a measure we could kind of hardcode everything but I thought this would be a better approach so the first measure that I'm creating is called sales sales Target and this is simply hardcoded to 2 million what this does is it gives you a flexible way to adjust the number if your business requirements change later if you put the 2 million directly into the conditional formulas then changing it becomes a pain so we have got a sales Target

which is a measure that that just always comes up 2 million and I can see that in the table as well if I put that for everybody it's 2 million including at a grand total level now the next measure that we want to do is Target comparison V1 we are going to do four versions or three versions of this measure so we'll do this with V1 first and here we can use the IF function if and if you have used if in Excel python or Tableau or any other systems it's exactly same logic if if

and you build a condition logical test The Logical test here is I want to see if your total amount is more than the target so total amount greater than sales Target if so I want to say yes else I want to say no so if you look at the nature of this measure it is printing the word yes or no depending on how that comparison is and once I add that I can just print that here you can see I'm doing yes no comparison a lot of people have yes but we also have some people

not meeting the targets and they're all having no because their values are under 2 million so this is one way of doing here a key thing that you want to remember is let me just flash this again we using the IF function to compare two things so the comparison between two things need to be just two individual values they can be numbers dates text values it doesn't really matter but they have to to be two individual values more technical way of referring this is they have to be scalar values a scalar is nothing but just

a single value so it has to be a single value and usually in business situations like this they will be measures what happens is if you do this comparison wrong or if you compare with a table column one column versus another then you will get into some trouble so for example um not that you will write like this but it is likely that uh in you might actually end up doing this sort of a mistake in early stages so we'll do this target comparison V V2 and here if and instead of picking a scalar value

I'm going to pick a table column so if I'm going to say shipments table in fact it won't even let me do it uh in newer versions of powerbi but um you could kind of write this one way or another if you're using power pivot in Xcel or an older version of powerbi so if I'm saying sales shipments table amount column now you can see that the auto suggest is not even giving me that option it is saying you have to pick a measure here it's only going to work with scalar values but somehow I

brute force my way into this and I'm saying if shipment amount column uh is greater than sales Target already red lines are coming in here indicating we have a trouble but let's push ahead with this I'm going to say yes no close bracket hit enter it's just not going to work you can see the red line is already there and the dreaded error comes in here it will give you this sort of an error a single value for the column amount in the table shipments cannot be determined this can happen when a measure formula refers

to a column that contains many values without specifying an aggregation such as minimum maximum Etc so it wants a scalar value an aggregation basically you can't do one column versus another column kind of a comparison with this function there are other functions where you could do this but if function switch function which is another way of doing these kind of logical tests they are equipped to do one-on-one comparisons pretty much every time a measure whether it is any formula or any other kind of measure that you're building a simple let P test for that measure

has to be it has to come up with a single value right it cannot return a bunch of values it has to be a single value so the measure is usually an aggregation process it just takes all the values and sums it up or it Compares One value with another and comes up with the third value whatever may be that process uh it has to always come up with single value and if it doesn't work then internally this sort of a mistake might have happened anyhow this doesn't work I'll leave that there in the model

just in case you want to refer to to that and we will do another measure just as you can print yes no you can also come up with some interesting or innovative ways of doing this so for example our Target comparison I can get this spelling right that'll be good okay Target comparison version three and this time we going to say if total amount is greater than sales Target if so instead of yes no you may want to print a thumbs up or thumbs down kind of a symbol and for this you can use Emoji

this is one of my favorite early tricks and dags that I like to teach people and it kind of Lights them up so hopefully it helps you as well so open double codes and then press windows and Dot key together this opens up the Emoji keypad on your computer again the keystroke is Windows and the period or dot key together from here you can pick any Emoji so I'm going to go in and uh find the thumbs up emoji I think it's somewhere here yeah here so if they have met the target then we give

them a thumbs up that reminds me if you're enjoying this video maybe you also want to give thumbs up and else thumbs down we can add that and in the table I can add it and you can see that as a indicator as well so again this is a very simple cool way of looking at the targets and then seeing it obviously viously in a business reporting you may want to be a little bit more gentle with these emojis you don't want to have laughing out loud kind of an emoji there because probably it doesn't

make sense but again it all depends on what the context is and who is seeing the reports so that is how you can do the if if kind of a comparison if only lets you do oneon-one comparisons but if you have got multiple comparisons to do like you don't have a single sales Target you have a sliding scale of sales Target so you have a target of two million 1.5 million 1 million and then you want to see where people have met their target some people have more than 2 million some people have more than

1.5 some have more than 1 million so I want to give them different symbols or different messages in that case you can use the switch function what it does let you is it will let you kind of do multiple comparisons using a kind of a ladder structure you can also use the IF function and kind of Nest one IF function in another just like how you could have done this in Excel or other Solutions even VBA or coding systems just put one if inside another and that also lets you build that I'm leaving this as

a homework assignment for you print different symbols or different messages and people are more than 2 million 1.5 1 million so now that you have understood the 80% of the important vital and essential Dax Concepts let me conclude by giving you five tips for writing better Dax I call this as ACM Buu or ammo it's a lousy acronym but a great technique let's take a look at this a stands for acquire business knowledge you can't write good DXs if you don't understand the underlying business and what your users need so any good kind of data

analysis business intelligence project must begin by acquiring proper knowledge about the underlying data sets what your users needs are how they plan to use the reports how often data is updated and all of that so spend quite a bit of time doing this phase correctly if you get it wrong everything else is just going to be useless so understand what your users need understand what your data is able to provide and then build from there C stands for cleaning the data if you don't have clean data you cannot solve the problem easily using Dax so

Dax should not be the fix to your lousy data problems so spend quite a bit of time cleaning the data making sure that it is in the right shape size and quality before you start thinking about modeling and analyzing the data M start for model for the needs you might have the same data but depending on user a needs you may have to create one kind of model and user B's needs you may have to create a different model so keep that in mind your data can always be there but your model should reflect the

underlying business needs of what your audience wants and this is where the thing is in Step by-step progression you have to have good knowledge of what your users need and then clean the data accordingly and model it accordingly you can't have in many proper bi situations one fixed solution for everything I mean the data sets and majority of the concepts can be same but some of the modeling needs to change depending on what is happening for your users how they expect to see the results and b stands for blocks not Black Box what I mean

by this is think of your Dax as individual blocks that you can stack one on top of another to build a massive Cathedral you don't want to write a 300 line Dax piece just to solve one problem instead construct it in a more logical manner just like how we have done earlier we have got these individual things but we used smaller chunks to individually build out the logic and then combine them to come up with amount per shipment or boxes per shipment so think of it like that don't write complex logic in one go instead

break it down to smaller segments and build it in a way so yeah I call this as blocks not blackbox and U stands for using variables and using Dax query view when you got stuck we haven't covered either of these Concepts in this video as these are slightly more advanced but once you start building the knowledge you will be able to come to a place where you'll find that if you write your Dax in such a way that you're using variables and if you are getting stuck using that Dax query view can help you a

lot so those are my five practical tips I call them as Abu acquire business knowledge clean data model for the needs build blocks instead of black box and using variables and Dax query View and other features to understand and explore your data better when you get stuck all the best in your Dax Journey I'll catch you in another video bye