Learn Power Pivot & DAX for Power BI with 20 Beginner to PRO Examples (Sample file included)

if power bi is a car then power pivot is the engine that drives that car so in this video let's understand how to use power pivot and how to create measures using the dax language i'm going to demonstrate the dax language using 20 different examples as this is a fairly detailed lesson i have provided a sample power bi workbook for you so grab the file and join me on this journey let's go for the drive [Music] this is the example file that we will use to understand powerpivot and dax as i mentioned earlier if we think of power bi as a fancy car then power pivot is the engine that does all the calculation work so it is the processing mechanism behind all your data to facilitate our discussion i have already loaded some sample data in this file and i'm just going to open the model view so we can quickly review this data so this is for a made up company called awesome chocolates and we sell chocolates so this chocolate sales data is recorded in the sales table and which people are selling the chocolate that is captured in the people table which locations are getting the chocolates that is captured in the location table and what chocolate we're selling is in the product table as i talk a lot about this particular company awesome chocolates i am not explaining in a great detail what is in each of these tables if you are completely new to power bi i highly recommend that you pause the video here and learn the power bi basics from another video that i have on the channel and then come back to this so that it all makes sense so we have got our sales table and all of these other tables and we can use the dax language to ask questions about our data and get answers from our pivot let's start the questions by asking a very very simple question which is what is the total money that we have generated if you go to the sales table you can see that we have got a amount column so essentially we just want to add up this amount column to see what the total amount would be here i can create a measure on any table by right clicking on the table and then selecting the new measure option alternatively from home ribbon also you can click on the new measure button so let's click on the new measure and this measure we will name this as total amount and then equal to so this is how you create the measure you type the name for the measure the name can be anything and then after equal to you then write the dax formula because we want to add up the total amount column in the sales table we can use the formula sum and open bracket specify the column name so when you give the column name you need to specify it with table name column name so we'll say sales table amount column you don't have to type the whole thing as you type one or two letters you will see the auto suggest come up and then you just pick the whole thing and close the bracket hit enter and now you have a measure called total amount attached to the table sales table here one way to think about the measure c is imagine your data is this fixed then the measure is something that sits on top of it so my sales table is this fist and the total amount is something that acts on top of my data that's why we can't find the total amount as a column in the table but it is something that is just sitting on top and then it kind of acts on the data of the sales table to see the value for total amount you could then kind of put it in a table or a graph or a card so if i put a simple card and put the total amount there i'll see the total amount as 46 million because it's kind of adding up everything but if i put this in a table so let's just use tables especially when you are learning dax using tables is a great way to understand dax because then you can kind of see the numbers and everything clearly with the graph you are always seeing the values in the graphical representation and sometimes that is hard to understand so we will see the total amount by individual locations so i'm going to put 0 on the values and add total amount so that we can see how that 46 million total is adding up to individual geographies [Music] when you create the measure you only specify the logic you don't have to tell power bi how to calculate the amount for individual geographies you just say i want the sales table amount column added up so this is called as the calculation logic or the definition of the measure and the definition of measure needs to be kind of bare minimum or naked but when you place it on the screen at that point during the runtime power bi automatically figures out how to get that value as per what's happening on the screen so for example here to show these values internally what power pivot will do is it will go to the geo locations and then it'll get each geography we have six geographies so it will list one per item so australia canada india like that and then because the geography table or the location table is linked to my sales table it follows the relationship here so right now let's just say we are calculating the value for australia so it goes to here picks australia follows the trial here because the filter direction says it needs to go from here to there that arrow it's gonna take the overall sales table and then shrink it down to just australia rows and at that point the total amount logic says that it needs to add up the amount column so it's just going to sum up the amount column only for australia rows so that's how the calculation is done in two steps so one way to think about power pivot engine is it does really two things it filters the data and then it calculates the values so everything that you do on the screen basically goes through these two steps what is it that the power pivot engine is filtering and what is it that we are calculating the calculation process is defined by your tax measure so your total amount is the sum of the sales amount column that is defined by the calculator or the calculation measure the filter will tell power pivot what aspect of the data to look at when calculating the value so first power pivot will filter the data and then it will calculate the value [Music] [Applause] [Music] when you create the measure you just write the logic but when you show it on the screen sometimes you may want to format it in a friendly way you can also specify the formatting for a measure so just select the measure and from the measure tools ribbon this kind of appears every time you pick a measure so if i pick a column i don't see measures i see column tools but if i pick the measure i see measure tools from here using the format area i can tell that the formatting needs to be in dollars with zero decimals and now i'll have a nice little 7.895 million formatting here so that's my total amount let's add one more measure to add up how many boxes we are doing so right click new measure total boxes and this is again a sum of the boxes column you don't have to type the table name column name you can directly start typing boxes and then it kind of shows you all the bevo values in the data model and i can pick sales boxes close bracket and we now have total boxes as well let's add this to the table so we can see total amount seven million five hundred and twenty two thousand boxes i'm just gonna apply comma formatting on the total boxes so that we can see that so this is how total amount and total boxes are calculated let's say i want to know how many shipments are happening [Music] one way to think about shipments is in the sales table each row is one shipment so we can kind of count the number of rows and that will give us the number of shipments so we can again right click on the sales table new measure and this is my shipment count is equal to and we can use the count rows function to do this count rows and then any table you specify so we'll just say sales table it'll tell you how many rows are there in the sales table so we can then add the sales table shipment count here so that we can see that at the overall level we have done 9400 shipments and that is how they are broken down by each geography again you can apply the formatting to see the values presented nicely on the screen each of these measures when you create you only write the bare minimum definition as i mentioned earlier but when they get calculated power pivot automatically figures out how to calculate based on what is happening on the screen so this kind of what is happening on the screen is loosely referred to as evaluation context that means when the measure is defined we write the logic but when it is calculated depending on what is going on on the screen what field support what filters are put everything changes so for example this is my table that shows to me everything that is happening let's just say if i put a filter or a slicer on my screen and on this slicer i'm gonna put my product category we have three different categories of products bars bytes and other and let's just pick bars automatically all these values will change to tell me how many bars we have what is the total amount how many boxes of bars we are shipping and how many shipments of bars we have done so this kind of filtering process is the one that happens first and then the calculation happens next that is what i mean when i said we power pivot to first filters the data and then calculates the totals so here is our first examples of the measures we got three measures total abandoned total boxes and shipment count now let's take it up to the next level hey just a quick note from our sponsors i'm just kidding there is no sponsor but if you are enjoying this tax video so far please give it a big thumbs up so that other people can also find it and enjoy it and if you are new to the channel please consider subscribing so that you can get fresh excel and power bi tips right to your youtube list on to our next journey one of the key concepts in dax is that measures are reusable what it means is if you create a measure like total amount you can reuse that to do something else later on in your calculations so for example i have got total amounts i have got shipment count i can calculate a measure that tells me what is the amount per shipment that we are making so right click new measure we can say this is amount per shipment and one way to write this would be you can take the sum of amount and then divide this with count rows of sales and when you add this to the table you will see what that would be so per shipment we make about five thousand dollars uh somewhere further down you may get a grand total level four thousand eight hundred ninety and i can sort this to see which geographies have higher per shipment dollars coming in in on all of that like for example australia is our best country per shipment level new zealand is lower country but if i sort by something else i might see the countries kind of jumble up so this is one way of doing it but you can also do it in another way using the reusability concept so we can right click new measure i'm going to call this as aps amount for shipment 2 and this one is instead of doing the calculation sum and count rows again we can use the square bracket and then select the total amount measure divide this with square bracket shipment count the actual definition of these measures was done earlier so it kind of reuses the logic and everything and this is a smarter way of doing it because you don't have to define the logic every time you just do it once and then reuse the ideas to build more complex things so we'll add this and for now i'm just going to move this guy here and let's just expand this and put a ps2 on the table and as you can see the values match but aps2 is reusing the measures whereas aps the original one does the calculation again so in a way amount per shipment is kind of a bit lazy or it does more work than aps2 internally everything is same but because we are reusing the idea this works in a faster and more optimized fashion so let's kind of understand the reusability idea a bit more by calculating for example amount per boxes so i can do a new measure i'll call this as abs no no abapb amount per boxes and here we can just say total amount divided by total boxes you might be thinking this looks a bit confusing chandu because i'll just add this first in a measure like here we are saying sales table amount column this is how we are writing whereas when i'm defining this i'm not writing sales total amount and sales total boxes why is it you can of course write sales total amount and that is totally fine power bi will not complain but as a best practice whenever you write a measure name in your formulas you always just write the measure name in square brackets without writing the table name this is kind of like a dax best practice this way what happens is if i have a big dax formula where i'm using both table columns and measures i can immediately eyeball and then say that these are my table columns and these are my measures so it kind of evolved like that but internally there is no difference if you write a table name or not i suggest that you don't write table names on measures and use the table name always when you refer to a column so apb is my total amount divided by total boxes and i can add that to my table to see what that is like so it's kind of very close but you know some countries for example new zealand have slightly higher amount per boxes 15 32 cents whereas some other countries have slightly lower values one idea that you can use when you are dividing like this is usually what happens when you are dividing one number with another is in certain combinations it might actually lead to a divide by zero error here at a gross total level we don't see the divide by zero but if we kind of filter down to a very narrow scenario we might actually have a divide by zero happening so a good practice is instead of doing this kind of a division you can also use the divide function so let's do that right click new measure and this is apb2 and then here we can use the divide function and then specify the numerator and denominator so my numerator is total amount and my denominator is total boxes and you can also provide an alternate result if you don't provide this it's gonna be just a blank on the screen but if you provide it will come up with that so for example no ship boxes i think and that would be printed right now with the with our data it's very hard to simulate this so i'm not gonna bother with that but you know we can just add this here and that will also give you the same value and i can put that apb2 into my thing and then it will show me that the beauty of all these measures is when we are creating because we keep them as simple as possible once they are done i can go and use the measures for anything so even though we are doing the analysis here from a geographical perspective i can go to a new page put a table and here i can just see what my amount per box would look like at a sales person level so these are all my sales people and i can just say apb2 and this is how it looks at a gross total level 14.98 is what we make per box of chocolates but if i sort this i'll see we go all the way from 15 15 and a half down to 14.25 so this is a very very good way to analyze the data we could also do the same for aps so i'm just going to add that thing there and then again apply a shortcut so for example hypothetically let's just say we have an internal torque target that per shipment you should make at least four thousand eight hundred dollars so anybody exceeding the 4800 mark i want to visually identify them and anybody not meeting that i also want to identify them so this is where we can use the if formula because we want to kind of tag people as yes you have met the target or no you haven't met the target so to do this you can create a new measure a ps target achieved and when you are creating a measure it's a good idea to give it as good and as long a name as it needs to be don't need to go overboard but you can specify anything that works so aps target achieved question mark and we can write any formula if and because we can reuse the measures we can just say aps2 if aps2 is greater than 4800 then yes else no so this is the formula here uh it basically checks the aps value whether it is 4800 or more and then returns either s or no value and when you click enter now the aps target achieved indicator is added i can place it here in my table and then i will see that all these people up to gnar have achieved the target and everybody further down have not achieved the target and at a gross total level because my aps2 at an overall data level is 4800 that is also achieved so the total row also shows me what is happening at the overall business level now so far we have created kind of like a handful of measures and you may have noticed a pattern along all of these that is irrespective of how you write the measure a measure should always return a single value let's start this test with total amount measure if you notice this what we are doing is we are taking the amount column in the sales table and then we are adding everything up using the sum function so that we end up with a single value so this kind of an operation is called aggregation that means we take a whole bunch of values the entire amount column and then we aggregate it to come up with a single value it so happens that in the total amount case that single value is a number so sum essentially adds up and then it produces a single number but in case of shipment count it is also a number but in other cases we are able to produce text also i'll come to the text bit in a minute but so here both my total count rows and total amount and total boxes are all operating on the table but if i take these other measures like apb what this is doing is it's taking two other values and doing an arithmetic operation on that to come up with a single value so again it kind of follows that pattern which is the measure is taking a bunch of values producing a single output value so this is how you write a measure anytime you are writing a measure you should always think am i producing a single value if you are producing a single value that means you are produ you are creating a valid measure otherwise you will get into some sort of errors when you are building the measures the value itself can be a number or it can be a text also so here i am using the if logic to figure out the output of s or no and that is still totally cool as long as the final output is a single value it doesn't matter if it is a text value date value number value whatever it is the measure will still work and it will kind of show up on the screen [Music] now here is a little fun trick so instead of doing a yes no indicator we can also show some sort of a visual indicator while s and no is good let's add something a little bit more fun so i'll add a new measure and then this is aps target achieved 2 if aps 2 is greater than 4800 and then within double quotes instead of saying yes or no we'll print an emoji so to print an emoji into the dax measures you can use this little trick hold down your windows key and then the dot key together this opens up the emoji keypad and from here i'm just going to say a smiley face if you meet the target then you're smiling you're happy so i want to show the smiley face close the double quotes and for the else part of if again open the double quotes windows and dot key so if you have not met the target your kind of where is the crying one okay down cast face with sweat whatever that is so these are the two emojis smiling or you are kind of sad and we will add that and then we can show that in my table so i'll get like all these smiley people and then these people are all sad so this is how i can see my individual indicators this is a little bit more fun you may want to try out different emojis i mean smiling is smileys are all right but there are other indicators that you can use so this is one way to do this and because everything is dependent on what is happening on the screen right now this is how it is for my all the employees if i add for example a a slicer on my geography and then let's just see what's happening to my new zealand within new zealand this is how things are and these are the people that are kind of not meeting the target i can go to india or i can kind of hold down control and multi-select countries to see how the shipment targets are achieved within each geography and the way all of this works is because you have filtered internally what power pivot will do is it will filter for the selected countries here it will reduce the number of rows in the sales table for them and then it will go to the people table select the individual person filter the data further down and then calculates the aps2 and emojis and indicators and populates the screen so internally there is a lot of filtering happening even before the calculations are working so far we have created nine different dax measures here now let's take our understanding to the next level by introducing few more words of the dax language let's just say i want to know as you can see here we have got quite a few sales people and we also have i'm gonna go here to my products table all of these different products so i want to know whether each salesperson sells all of these products or they sell only some of them that means for each person i want to just see how many different products they sell so we can kind of do that analysis i'll add a new page here in this page again i'm going to just use tables to demo the dax because this way it's easy for us to visually see everything and understand so here i'm going to add my sales person and for everyone i just want to count how many products they are selling one way to think about this problem is if i go to my table view into my sales table because i have got sales person and product i just want to count for this person how many products are there likewise for this person how many different products are there so one kind of like a silly way of doing this is and this is obviously wrong so we'll correct this new measure count of products and we can say go to the products table and then count number of rows there so we can just say count rows on the products table and then when you add this this is going to be 22 all the way through this is because if i go to my products table i have 22 products you can see this here total 22 rows that's how many products we sell so it's gonna give me that why is it not different maybe at a gross level everybody is selling everything but if i want to filter this down to something then also this would be wrong so i'm going to just add a a slicer and then in this slicer i'm gonna just put a date and then i'm just gonna filter it all the way down to just the first week of january 2021 first month past few weeks even then it tells me that you know everybody sold everything that seems a little unlikely even if i just narrow it all the way down to a single day or you know we just match this exactly even then everybody is selling everything this is kind of impossible in fact if you go and observe the data you will notice that that is wrong this is because the way this calculation is working is we put employ a sales person name on the table and then we are counting how many products are there so salesperson is here product counters is happening here because internally everything is filter and calculation when we put the sales person it's gonna just filter the sales table down to only that employee and then it wants to calculate the count rows on the products table but notice the direction of this filter this filter is going from products to sales not the other way around so because the direction is pointing from here to there not the other way the calculation won't work correctly and it won't give you the desired result so one more way the correct way normally we do this kind of thing is i'll add new measure and then this one we'll call this as count of products right there are multiple right ways to do this so we'll just i'll show you one way is to use a distinct count measure distinct count and then you specify the column name so distinct count of sales product so we want to count the number of distinct products sold within the sales table and then we'll add this thing here so now we start to see a little bit of different values there for example within this window of the first eight eight and a half months of 2021 some people did sell all the products but other people have only sold 14 or 15 products and 13 products for this guy but as i scroll this down you will see that some people have only managed to sell one product like jehu here um between 1st of jan 2021 and 10th of february so almost 40 days they have only sold one product like consistently the same product so this is very interesting whereas some other people have managed to sell 20 or 21 different products so count of products the way this is done is we are saying distinct count of sales product column so if i go here this measure is happening all inside here it just goes to the product column gets all the values and then tells me how many different items appear there so distinct count is a very powerful way to do these kind of unique counts and you know this actually exposes some problems that are otherwise difficult to spot so for example here why is jehu selling only one product so i can then go and investigate that by you know adding some extra graphs here i leave all of that to your imagination [Music] going back to our original example just as we have total or the sum and then the count you also have measures to do various other common arithmetic operations such as min so you have a min formula likewise you have max and median average count count all count blank and count rows we have already seen so i leave some of these for you to figure out but now let's take this to the next level as i mentioned earlier the way power pivot engine works is it kind of does two operations it filters the data and then it calculates the values so using the dax language so far we have been able to control how the calculation part is happening so we are able to tell i want to do a amount per shipment as a calculation and controlling that side of things but what if i want to also control what happens at the filtering level so this is where instead of letting power pivot and power bi do its own thing with filters what if i need to add some extra criteria on top so let's add some real world examples here i'll add a new page and here we'll build a table let's just say i want to see for our products so i'll put my product here all my shipments so this product they're doing 400 shipments like that and then i want to count how many shipments are happening where the boxes are very low so if i go here to my data you can see that our number of boxes kind of go all the way from zero up to 3 360. so some of these are big shipments whereas some of these ones are kind of really small shipments if i'm just sending five boxes of chocolates and a shipment that is a small shipment so i want to count how many shipments are happening where the number of boxes is under 50. we have an internal measure for this it's called low ship low box shipments lbs and i just want to count what is that lbs count is so here we can use the measures to define such a criteria because notice if what is happening is we don't want to count all shipments we want to count the shipments where the number of boxes is under 50 that means we want to add an extra layer of filtering before the calculation happens so this is where a special function in the dax comes into picture it's the calculate function so this measure is low box shipments count lbs count and here i can say calculate and then specify the measure so i want to calculate my shipment count by adding an extra filter condition so we can use the calculate function to kind of alter or change or add or delete filters that are happening on the data so here we want to add a filter so we just specify the filter and the thing might look a bit confusing but once you write it it becomes quite normal so we want to calculate shipment count where my boxes shipment sales boxes is under 50. so this is how you write it calculate shipment count sales box is under 50 add this and now we have low shipment low box shipment count so we can kind of see this and it is a very interesting thing so we can see some products may have a very high low box shipment count whereas certain other products have a very low low box shipment count so now that we know how many total shipments are we are doing and how many low box shipments we are doing we can calculate lbs as a percentage which will help us understand the performance of certain products so we will do that right click new measure lbs percentage is equal to and this is a place where using divide is way better than the regular division numerator is my lbs shipment count and denominator is my shipment count and we can just omit the alternate result add this set the formatting to be a percentage and set this up in the table so i can see what's happening at lbs level and then i can apply a sort order on this so that for example we can see that drinking cocoa has 17 percent low box shipments whereas spicy special slims is only seven percent low box shipments this style of using calculate really opens doors for many powerful and elegant and insightful analysis of your data so for the next part of the video let's understand calculator little bit more by playing with various types of calculate functions our next example is going to be just as we did low shipment count i want to count what percentage of my shipments are for my bar category of products so instead of all shipments i just want to count how much is my bar shipment the product category is at the product table and we have the sales table here so we can right click new measure and then this one is called bar shipments and here i can just say calculate shipment count where my products table category is equal to bars so this is how you do a comparison with text values because bars is a text we'll put that in the double quotes equal to and enter and then we will get a bar shipment here i will add this to my table for the moment i'm just going to remove some of these columns so we can kind of focus on everything so here we can see that only some products have bar shipments whereas others not this is because only some of the products are in the bar category other products are a different category so naturally they won't have any shipments at all in the bar category a better way to read this table is if i have maybe looking at a people level or something so i'm going to take up my product column from this and put sales person there so that we can see how each individual is acting so andrea kempton 43 of her shipments are low box shipments and 207 of her shipments are bar shipments if i put overall shipment count we can kind of see that you know what is that as a proportion of everything just as we did our lbs percentage we could also do a bar shipment percentage and this is a very powerful way to kind of see which of our sales people are doing heavy amount of bar sales versus other sales so we'll do a bar shipment percentage this is equal to and then we can just use the divide divide bar shipments with shipment count and apply a percentage formatting and set that up on the table so we do have around 50 percent overall level and everybody is within that ballpark but if i apply a sort order i'm seeing some variation like kunar is our highest bar shipment percentage and jan is our lower bar shipment percentage needless to say if i apply any filters or if i add any extra page level filter or a slicer all of these values will change so for example on this page i will add a page level filter on my region so i'll add region here and then just select apac and then we can see what's happening within apac for example marnie has very high bar shipment percentage and jan still has very low power shipment percentage so that is how the the additional filters on the screen will be still respected by calculate calculate also has the ability to change the filter so for example here notice that within the page level we're seeing a pack sales but if i do something else like what is my america's percentage here then it kind of overrides the apac filter there so let's just do that we'll add a new measure a pack we'll just stay with shipments but whatever you do you can still do it with total amount or boxes or anything else apac shipments is calculate shipment count where region is equal to america's i did say a back here it should be americans and close that so we now have an america's shipment value added here and i can put that in the table and then i'll see these numbers here 132 3146 notice that even though at a page level we said only look at apac data because calculate is telling power bi that it wants to do this analysis for america's it kind of overwrites the filter that is coming from the screen so even though the screen is saying evaluation context is apac calculate says no give me what is happening in america's so that is the power of calculate it can not only introduce a filter it can override a filter with anything else that is happening on the screen [Music] we can use the dax language not just to create new measures but also to introduce a column in the table or even create a table itself if you go to the table tools ribbon you will see that there is a new column and new table option i will just cover the new column alone in this video but you can also use dax to generate a table altogether so let's say for some analysis i want to know how many shipments we do or how much amount we generate on fridays you can see that from the date we do have the weekday and the date and month and everything but if i want to for example apply a filter based on the calculate so for example calculate total amount where my date is friday then i do need a column here that tells me what is the day of week so such a calculation can be done by using the new column option we'll do new column here and again we will follow the same pattern which is what is the column name and what is the tax expression so the column name here would be weekday and then column value is weekday function of my sales date this is it the weekday function is going to return a number corresponding to to the day of week so tuesday is three and friday is six if you want you can provide an alternative result here uh this will kind of tell power pivot how you want your week days to be to begin with whether they should start on sunday or monday or start from zero it doesn't matter because what we need is just a weekday and then i can build my condition on the six [Music] a note of caution here when you write weekday of sales date or anything in a column fashion here internally powerpivot uses a concept called row context so for each row it will evaluate the calculation so for this row the date is tuesday january 5th so my weekday would be number three whereas for this row it is february 26th friday so here my weekday will be six so there is a row context applied in this scenario there is a little bit more theory behind all of this and i'll explain that later on at the end of the video so now that we have got a weekday as an extra column introduced into the table i can then use that to ask a question of what is our total amount from the friday sales so let's do all of that in a new page here we'll put a table in this table i want to see by our products what is our total amount as well as how much amount is coming from fridays so let's add a measure now new measure friday amount is equal to calculate total amount and then you can specify friday amount as weekday of sales is equal to six six means friday and when you click okay that gets added here and then i'll just introduce that into the table and we can see how much is my friday amounts we can apply the same currency formatting and zero decimals so that you can see that overall 46 million about 9 million so one fifth of the sales come on friday and you can even calculate this as a fraction or do a similar friday shipment count or friday boxes here is an interesting thing you could use calculate like this but recently power bi has introduced a different shorter syntax for doing this i personally prefer this long syntax but i'll show you the shorter syntax so you can add a new measure this is friday amount 2 exactly same as friday amount but instead of using the entire calculating we just open bracket select the measure so i want total amount and then within curly bracket you can specify the within the brackets you can specify the condition so the condition is sales weekday is equal to six so here you can pass on any additional filters like this internally it gets translated into calculate like i mentioned i prefer the original syntax of this with calculate and all of that but sometimes you might find this kind of a tax code as well both of them mean the same thing [Music] another concept within dax is we can use variables so let's say we will add a new variable new measure here in this measure i want to add up what is our total amount for new zealand and australia so we we have an r condition here that country can be either new zealand or australia and then we want to add up that thing so we can say nzau sales amount as the measure name and then here we can declare two variables so where and normally when you have variables involved it is a good idea to write this dax in multiple lines to get into a new line you can press shift enter and then that will add a new line and then you can kind of use the tab to indent the code so where nz amount is equal to calculate total amount geography is new zealand so we get the first value like that like that var a u amount is equal to i'm just gonna ctrl c ctrl v that and then just change this from new zealand to australia so now that we have created two variables and z demand and au amount i will return nz amount plus au amount so here we are using two variables to capture what is happening within new zealand and what is happening within australia and then generate a final output as the sum of both values you could sum this up you could build like an if condition whatever you want and you can do that so we will do it like this i'll show you another way to get the same answer as well that that is that doesn't require variables but here the idea is to introduce the concept of variables to you so in this table let's just add the nz au amount i'm gonna quickly apply some formatting on this as well let's double check this i'm gonna go to a new page and in this page let's just add a column chart australia is 7.8 million new zealand is 7.8 so it is about 55 15.6 which is what we are getting here 15.7 so it does look alright to me so this is one way of doing here the idea is to give you that you know we can use the variables to get this kind of an answer but that's not really necessary we could have asked the same question with a single calculate so i'll just quickly show you that as well so let's create this measure new measure and nza you sales 2 and then here calculate total amount geography locations table jio column in and then curly bracket within double quotes you specify the options so new zealand australia so using the in operator you can kind of provide a list of items and it's going to check if the location is in any one of them it will add up so this is same as calculating the new zealand value separately australia value separately and then adding them up and we can just see that value it will match with whatever we have in this column we don't have the formatting applied but you can see that the numbers do match [Music] so there you go we have created 20 different measures in fact probably a little more than that to see all the measures you can look at the table field list here or you can go to the model view and expand the sales table i did put all the measures into the sales table and here we can see that these are all the measures that we have created and the weekday was the column so we started the session with total amount and total boxes these are simple measures that tells me what is the sum of amount column what is the sum of boxes column and then we use it count rows to count the shipment count and then we kind of understood the concept of reusability of measures by for example defining amount per boxes and using the divide operator to do amount per boxes again and then we also did amount per shipment amount per shipment two different versions of the measures like that and we also looked at how to do like amount per shipment target achieved as a text value yes no indicator whether if it is more than 4800 or not and to use an emoji as an indicator and then we kind of did some various counts and count distinct to understand how they work and what happens to the filter criteria behind scenes and and all of that and we then went and understood the concept of calculate by defining extra filter conditions on low box shipment count or bar shipments or bar shipment as a percentage or america's shipments and we also understood how to add a column so we calculated weekday and then figured out what is my friday amount in two different ways using alternate syntax and finally we calculated nz australia sales value using a variable as well as using the in clause i hope you found all of this useful and helpful but i want to remind that when it comes to dax whatever i have covered so far is just the tip of the iceberg dax is actually a very in-depth and wide language with hundreds of functions and business applications so how to take your tax knowledge to a truly professional level i've got three suggestions for you number one use the practice workbook and the data set that i provided you and practice the concepts that i just covered the concepts that i demo today are the foundational concepts of dax if you become comfortable in that then you can achieve great things in your tax journey so get your foundation strong by practicing number two use a book to polish and improve your tax knowledge specifically i recommend two books number one is supercharged power bi by matt allington and number two is the definitive dax guide by marco russo and alberto ferrari both of these books are really good and they go into great detail on various aspects of dax i highly recommend setting aside some money to purchasing these books i've got links for these books in the video description so feel free to check that out the third suggestion i have for you is go for a course i run a power bi complete course that teaches you how to use power pivot and dax in a greater detail along with power query and how to use power bi to build business reports and data analysis so if you are looking for a course to kind of build your entire knowledge of power bi then do check out that course the link for that is also in the video description now i wish you all the very best driving your new car i'll catch you somewhere else [Music]

Channel: Chandoo

Views: 169,758

Keywords: chandoo, chandoo.org, dax, powerpivot, how to use powerpivot, how to use dax, dax beginner, how to write dax measures, introduction to dax, evaluation context explained, filter context explained, measure vs column, how to use CALCULATE in DAX, power bi, power bi tutorial, power bi tutorial for beginners, calculate function, dax formulas, data analysis expressions

Id: PQ5lALdFHCU

Length: 53min 55sec (3235 seconds)

Published: Tue Jun 14 2022