[[ 1 HOUR ]] Complete Power BI DAX End to End - Power BI DAX Tutorial - Full Course by Satyajit

hi in this video we shall be learning about dax dax which is nothing but data analysis expressions so let's get started [Music] [Music] in order to say if you know dax you need to know at least these kind of functions the functions are sum average min max count count rows calculate filter these are the basic backs functions which almost everybody knows at least because we have been using these kind of functions in excel and we will just be using the same functions in power bay as well so if you learn these things then you can confidently say that you know tax of course there are more than 200 dax functions so knowing each one of them is not important it's not feasible and it's not required as well if you know at least 40 to 50 dax functions that should be more than enough okay apart from that we should also learn about context which is row context and filter context we shall be learning about all these things and then of course we'll be learning about the basic formatting options best practices in power bi what are x versus non x functions in the next couple of videos now let's try to build our first dax expression i are you guys excited about it i'm pretty much excited let's start with it so dax what is dax dax basically is data analysis expression there are two different business logics which we need to work on one of them is measures and one of them is calculated columns now as per google measures and calculated columns both uses tax expressions the difference is the context of evaluation a measure is evaluated in the context of the cell evaluated in a report or in a tax query whereas a calculated column is computed at the row level within the table it belongs to now the theoretical definition might be very difficult to understand let me understand you in layman's term let me explain you in layman's term so first of all is measures for example you are dealing with a data set let's say you are doing some sales analysis now if somebody asks you can you tell me the total count of the records now in excel what we do is select count star from table name simple that gives us the total count of the records now if somebody asks you can you tell me the total uh salary of all the employees what we do simply we do select sum of salary from table name now these kind of figures which are calculated across the table and the value is of course a single value right let's say somebody asks you what is the total salary for people who are females that means select sum of salary from table name where gender equals to female simple even if you are using a where clause a filter or something like that but based on the filter at least you are calculating over the table right so that's basically a measure and calculated columns are some sort of calculated columns there are additional columns let's say you have two columns which is first name and last name and you want to combine them to create a new column which is full name right so that's where your calculated column will come into picture so anything you need if you think that i want to create a new column out of it use a calculated column if you want to do some calculations from the tabular level then you have to create a measure okay once we get into the practical parts more and more understanding will be you know there most of our doubts will also be cleared out so let's start and create our first dax function now we'll talk about calculated columns they represent a single value for per data model they are computed at runtime results are dynamic based on filters this is called the filter context not attached to any table so we'll quickly skip to this part but yeah of course i will be showing you what is the basic difference between calculated columns and measures so let's try to create some calculated columns now we are already using a churn modeling data set you can see if you want to see the data this is how the data looks like simply customer id age bills let me just have everything as much as i okay so everything is there i'll just push my row number to the top one just like this and surname again to the top so this is how my data looks like okay this is how my data looks like i have row number i have customer id i have everything now this is how the data looks like now if somebody asks me can you tell me the total salary of all the of all the candidates that means i want to do a submission of all the entries right so what we do in in in excel is we just select the entire column and do a sum operation equals to sum of something simple right similarly here also what we need to do is we need to create a new measure and that measure is total salary here i am going to call my sum so when the exact same piece of thing which we do in excel we are doing it in power bi now the beauty of power bi is that whenever you are confused about any dax functions you just have to type in and then it will show you everything like it will show you what what it is expecting what is the exact meaning of this function you can see i have just typed some and i also see sum is nothing but adds all the numbers in a column right so i will be calling sum on a column name now what is that column i will be using estimated salary so i will be calling on estimated salary that's how it is of course if you ask me what should be the total salary for uh a particular gender let's say male or female so that's where your filter operations will come into picture your where clauses right in sql we do select sum of salary from table name where gender equals to male so that where clause is basically a filter right we will be learning about filter in couple of next sessions but at the moment let's try to understand the basic difference between calculated column and a measure now we already have created the total salary now calculated measures you can easily use them as cards you can see i'm initiating a card let's say i'll just keep it here and here i'll just simply drag and drop my total salary and that's how it looks like my total salary is 1 billion if you don't like the units if you want it in millions or in thousands just click on this visualization go to the formatting options data label display units to thousands and this is how it is just expand it this is your total salary right and if you want to give a border and a shadow that's it done so this is how your total salary looks like now this is a measure that is calculated across the table let's say you want to create a calculated column now if you remember okay so let's try to create the easiest calculated column let's say we have tenure right now business-wise everybody knows what is a 10-year tenure means how long the customer is in the system with the company let's say i'm using one network let's say vodafone i'm using it from the past five years that means i'm i'm a customer of vodafone from the past five years so my tenure is going to be 60 that means 5 years or 16 months so here i assume that the data is in months because it's 49.48 it could not be years right i mean this is just my assumption let's say it's in it's in months i want to create a new column which is in years or else vice versa so i will be creating a new column here i will have tenure in years which is i'm going to call 10 year divided by 12 okay and then i have it and then in the table i'm just going to click this simple and you can see this is how my tenure in years looks like of course if you want to round it and all those things everything is possible let's say you want to round it so there is a function called as round function as soon as you hit enter it will show you what it is expecting it expects a number and it is asking you number of digits that means rounds a number to a specified number of digits let's say your number is basically this one right and up after that i want digits let's say i'll have one digit and then close the bracket and tick it will be dynamically updated right now now let's say you want to you don't want to have any digits then it will be rounded off to it will be rounded off to zero to zero decimal places and that's how things work so let's say you want you want to have it in two decimal digits and that's how it is so every function you use in power bi is going to be very simple simple the the moment you type in it will also show you what is it expecting so it makes our life easier and of course you can also check out the power bi microsoft power base official pages they also have a clear explanation of all the dax functions so this is all about the basic calculated columns and measures right this is how you can see the calculated columns are created and of course we have also also shown you how the measures are created the filter functions and all those things will have that covered in the couple of videos now the next topic is going to be implicit measures if we use a calculated column as a value or result it creates an implicit measure okay so that means you are creating a measure where you are using a calculated column let's say i want to create i want to create a measure which is total tenure in years i want to have the total tenure in here so i am doing a sum operation on tenure in years some operation of tenure in years simple and click ok i will create a card here i will just display my new created column so this is my total tenure in years okay of course this is a measure we have also created a measure for this right we have just created a measure but what it says is if you are using a calculated column in a measure as a value it creates an implicit measure so this is of course a measure but it's a special type of measure it is called as an implicit measure which is used based on a calculated column right because our calculated column is 10 or in years right 10 year initials is our calculated column which is based on 10 yar and we have done a round function that's a calculated column and here i'm using the same column to create a measure so that's the reason it is called as a implicit measure simply if we have columns such as 10 year in years or monthly average usage and let's say we want to create the overall average usage for that particular customer let's say churn of 10 year months equals to churn of 10 year into 12 so let's say in case your 10 year is in months or is it in years you're just converting into months or years vice versa so that's where your first calculated column looks like right we have already created that and let's say you are doing some column or creating some measure out of a calculated column that is why it is called as implicit measure now what it basically means is that let's say if i change my original column that means this column is created with respect to this column i mean this column is using tenure 10 year in years is using tenure and total tenure in years is using 10 year in years right that means if you change in the original column that that is your tenure of course your these numbers will change if you want to see that of course we can see just remember the numbers this is 4.18 k okay 4.18 k so here what we will do is we will just change the tenure let's say i will do replace values let's say i'll select the column replace values randomly i'm changing one to ten okay just like that just to check whether the change in tenure is changing the calculated columns and the measures if i click on ok close and apply so it was 4.18 k now the number should change because we have changed the original column so it's loading yeah and the numbers are changed and this is how you can identify a implicit measure or you can create an implicit measure so that is all about implicit measures of course we have a lot of videos related to dax functions and all those things now moving on moving on to the next one which is dax is good at two things now if somebody asks you why dax so you can just simply answer these two things one is aggregations the second one is filtering aggregations of course how to aggregate if we already know statistics and all those things we know that we can aggregate using sum average minimum maximum distinct count these are all your aggregators aggregations is a combination of group of values into one value let's say you want to group by based on gender so you want the total numbers for males and females that means you're aggregating the values right so tax is good at two things one is your aggregations and one is going to be your filtering so let's do a sum operation we have already done a sum operation now my next question is so this is my total salary right and if you want to validate this you can see here also you can validate the total salary is matching this number one more thing what you can do is if you are using this data from an excel sheet open your excel sheet do a equals to sum operation on your estimated salary it will give you the results and you can verify your data and if k in case you are using a live data connection let's say from sql data so of course you have to use some you have to you have to use some select query select sum of estimated salary from table name and whatever number you get just validate with the number you are getting in power p that's how you can validate your numbers you can validate whether you are actually doing it correct or not okay now let's try to understand the tax breakdown now if i'll just create if i'll just open this same thing let's say we'll open the total salary so you can see total sales equals to sum of something that means total salary equals to sum of something now this equals to is basically an assignment operator right we are assigning some values to a particular variable total sales here the total salary is a variable this is the name of the measure or your calculated column you will be using that in the visualization so anywhere you want to use you can use this particular name equals to sum sum is basically your built-in dax formula there are many other formulas average mean max blah blah blah and then in the brackets i am passing my table details like which column so you cannot directly use estimated salary if i just call estimated salary i mean how will power bi know what is this so you can see i'm getting an error right because power view does not know what is estimated salary is that is it a column is it referring to some table we don't know right so we have to always mention table name in the brackets column name it's very simple to python pandas right whenever we are dealing the data with pandas library we know that we are importing pandas as pd and then we do create a data frame which is pd.read csv or your excel up to your data and you provide your file path and that's you that's how you read your data into data frames and then let's say you are creating some total salary in in python so what we do is total salary equals to df we use our columns like this right df of estimated salary right let's say into 100 let's say i'm just creating a new variable which is new variable which is estimated salary into 100 right so i use it like this right table in the brackets my column name that's how you use it in power bi as well table name in the brackets column name okay so this is all about your dax breakdown so these are some of the easy peasy built-in functions of course we will be showing you all these things now before getting ahead with average and date diff let's try to see what is sum average min max count counters date different data add will go one by one so everybody has already seen what is sum right so i will be creating a new measure which is average average salary so here i will create average and you can see average on what average on estimated salary so i'll just click on tick it's done so in the same tax let's say i want to have another card in that i want to add the average salary and this is my average salary of course the averaging is done based on unique customer ids right total value divided by the total number of customers we have if you want to see the total number of customers we can probably show you customer id it is 10 000 10 000 customer id so of course this number divided by this number is your average salary right now we will be creating some other functions which is min and max so let's try to create min and max very simple new measure minimum salary equals to min and then it's asking you which column and you can just pass estimated salary so i will use another card and here i will be using the main salary so the main seller is uh the numbers are not quite visible i'll go to data label formatting data label and then change it to num so this is my minimum salary oh my god somebody's salary is 11.58 that's horrible anyways and let's say uh i'll just just have it like this so that it looks good border shadow everything is done and then we'll have the maximum salary so maximum salary is i will just copy paste this code and create a new measure and here i will have max salary instead of mean i will use max okay so these are some basic things and they are very simple and very easy to do max salary and this is how the maxillary looks like so these are all your basic dax functions some average min max count now let's try to see the count now count is very similar to this one right here also we are doing count of course i mean you can also create a measure let's say i want to create count of records is equals to count counts the number in a column it shows you that count keyword count tax basically counts the numbers in a column so let's say i want to use which column i'm using the customer id because that's my primary key right and done i will create another dax very similar to this one here not using my customer id instead of that i will be using the count of records it's 10k same as this one right if you just want to change the formatting and see if the number matches i'll do the same thing for this one the number is matching right so it's up to you whether you want to create a new measure or not because some things are actually possible on the go itself so yeah this is how it works and the next one is count rows count rows is also a similar function like count let's say you want to do account rows let's say i'll create a new measure count rows equals to count rows counts the number of rows in a table and here we want to provide the table name i will just provide churn modeling okay so trust me count rows and count are similar i'll just cancel this and i will use count rows it's same both are same okay and then comes some of the date function which is date div and date add okay so what is date div and what is data so of course in this example of power bi i don't have any date parameters so what i will be doing is i will be using another data set okay so let's say i have another data set which is air passengers and if you want to see the data this is how the data looks like i'll quickly create a table and i will add month and passengers of course many people knows about this data set this is a very classic data set which we use in any machine learning examples air passengers data so i have month year day and all those things let's say you don't want day and quarter so this is how your data looks like now in this let's try to create some date diff functions what is date diff now of course i think i have date dave and all these things covered in the next classes but it's okay so date div basically tells you the difference in that number of days or any number of years or number of months so let's say i want to create now before creating something just try to imagine what is date day date diff means for a particular date probably it should give me some difference in days or something right difference in date probably so should it be a calculated calculated column or a measure that's the first thing which you need to ask to yourself right now i think that it should be a calculated column because date diff will basically if you have two dates it will tell you the differences right differences in days months years or weeks so here i will be creating a new column oh my bad i shouldn't create it in this one i'll create here a new column which is uh year diff i will have it as heredif and then i'll do date diff see even if you don't know any dated functions or anything if you just type date you can see everything is visible all the recommendations are visible that's how power bi is it's very easy to understand now once i clicked on datedif it shows me it returns the number of units specified in intervals between the input two dates what are my two dates my first date is month right i mean the name of the column itself is month so don't think that it's month right so air passengers month and day two that means it's asking you want to find the difference of your month date like from the original date from the data set and what so let's say i want to use system date so how to call system date in power bi just call today today function today is basically a date and then once you click on comma it's already showing you the list of recommendations be one day hour minute month quarter blah blah blah so let's say we are doing it on year i will use here close the bracket and click on take that means my calculated column is created i will expand it and just drag and drop my new created column so and just click on it my bad yeah that means my year difference is shown like this 1949 till 2021 it's probably 72 years right similarly if you want to do a difference in month just change it to month month difference quarter difference day difference all these things are possible so this is how your date difference function works now your next function is date add let's see what is dated i don't know i think it is adding some days so should be a calculated column let's say i'll create a calculated column uh new date new date equals to date add here it's also giving me which date number of intervals and interval so let's try to see i don't know maybe i'm very new to this kind of things i don't know let's try to explore so what i will give is i will give the date column on click on comma okay date column dot let's say it's asking for year comma number of intervals let's say i will have 10 and then let's say i will have here close bracket pick and then i will okay is not a type of date this is not supported okay okay let's click on this and now my date new date is created i will just add this and this is how it looks like my new created date of course i will not show the day quarter and all those things i want to just show the okay let's let's show the month and here as well so this is my newly created column which is my existing date plus 10 years my existing date is 1949 january my new year is 1959 january now this is how it looks like 1949 1959 so that that means i have just added the number of days if you want to remove it let's say 1949 you want to show 10 years back you still have to use date date ad there is no date subtract function okay you just have to change the values to negative if you click on tick and this is how your new dates will look like so that means the dates are not available right for those dates the dates are not available so for dates which are available you can see here for 1959 it is 9 and 49. for 1959 it's 1949 something like that okay so this is all about your date div and date add functions and moving on to calculated table now calculated table is also very important and we'll see what is a calculated table that means i'm going to create a new table which is on calendar date and i'm going to pass a range of dates so it creates a date table with a date per day between the specified range so let's try to create a new calculated table i hope everybody is clear about all these basic dash functions now there are three tabs here one is report tab one is your data tab and one is your model tab of course we will be teaching you about the model tab in couple of next classes let's go to the data tab so here let's say i want to create a new table based on this calculated table so new table so here i already have the command ready so that we don't want to write and make it a huge video so i have a new table which is calendar of you can see the calendar of range that means i'm going to pass a range so here i have passed two dates 1949 january 1st till 1950 and that means just two years that's it i'm going to create a new table having two years if i click on ok this is how my date column looks like okay if you want to select just keep it there no problem and from here let's say we want to create some new columns let's say year month and month number so let's say i'll calculate a new column so what is my new column year equals to new table date dot year simply my year is created how to create my month simple month equals to new table of date dot month i'll just copy paste it because we are going to reuse it again to create a new column which is month number month number is basically month number done so this is my new table that has been created and let's say i want to somehow merge this table uh or let's say we want to show the data for only these two these two months so what i'm going to do is i'm going to merge this column so this is one of my tables and we have my air passengers data so i'm going to create a relationship okay i'm going to go to model so this is my churn modeling which we are not using in this particular example we have air passengers and we have this new table so this month and this date i want to merge the relationship so just drag and drop it to the date column that means a relationship is created okay now i'll go to my reports for the time being i'm just deleting this table and i will have this table here and here i'm just going to remove all these things so i will just have the basic data all the data okay this is my original data now what i want to do is i want to add the new tables month and let's say i will just deselect the original month that means i want the let's say we'll create a new new table for this let's say we'll create a new table just don't want to have okay we have this table i will have another table okay so these are the two tables that i have now in this table let's say i want the summarized data for only those two years which has been created and let's say only for 1949 and 1950 i want the data now of course there are multiple ways to do that so what i'm going to do is okay let's try to have the month and then we'll have the number of passengers simple so this is how the data looks like now if you ask me april what is 264 is it is it all the april's in the original data let's say the original data is having from 19 how much from 1949 till all these years so is this april data combining the data from all these uh all these particular if your question is is this april data the combination of all the april's no if you click on april you can see it is only the combination of 1949 and 1950 because that is how my new table has been created right i just have 1949 and 1950s data of course you can also have a slicer to have the dates and you can do that as as i told multiple ways are there in power bi and this is how your calendar functions looks like right if you go to my calendar function this is how my calendar function looks like i have created a calendar table which is for 1949 till 1950 and based on that i have created this now if you ask me how to sort these numbers based on the month because it's just sorted based on alphabetically right so we have to go to the table click on month click on month click on sort by columns and month number click on month number sort by columns and month number if i go back now my values are updated this is how my new table looks like so this is a summarized data you can say okay so this is all about the basic dax functions now there are two different type of context one is row context and one is filter context let's try to see what each one of them means we'll talk about row context first honestly speaking if you have already gone through the dax videos that means you might have already done some some sort of calculated columns which is impacted on row 11 right that's basically your row context we already know how it works we have been using it for all our calculated columns so far let's revisit our first dax so when we were creating our first dax i told you right let's say we have a column called as tenure we don't know if it is in months or years so first thing as a data analyst you should do is to clarify whether it is in months or years i'm just assuming it to be in years in to be in months i don't know let's just assume it is in months okay so that was the reason i created a new calculated column which is churn modeling of 10 year divided by 12 and i have just used a round function so that i want to keep the decimal places up to two decimals that's it so we have created a tenure in years and you can see the values are different for different rows so this is basically a row context we expect a value per row in a table this runs at import and gets stored might increase the file size it's not might it will increase the file size the more calculated columns and measures you create the more file size will be and the more memory consumption will be from the power bi side okay because that was the main reason why we don't really use a lot of dax functions we use dax only when it is required or else we just we just try to use the inbuilt functions to get the output okay now what is filter context easy to easy to show with measures now filtering what is filtering when it comes to sql let's say i want to have the total salary of all the candidates that means total salary from table name it will give me the total salary of all the candidates just imagine if somebody asks you can you give me a total salary only for males that means you are restricting the total salary only to males then what we need to do is we need to add a where clause so what i will do in sql is i will add a where clause where gender equals to male simple so let's say you want to have total salary for people staying in mumbai salary from table name where location is equals to mumbai let's say total salary of people for people staying in mumbai and kolkata you use your in keyword in mumbai and kolkata something like this that's what we do right and these are all your filters these are all your filters so we also have a filter keyword in power bi and we will see how to use the filter context so to use filter context we usually use calculate keyword calculate is breaking out of the filter context how it is used variable name equals to calculate of something comma where category name equal to beverages let's try to go to the power bi desktop and create our first filter context variable okay so we already have let me just delete this tag delete this card we already have the total salary which looks like this right some of my estimated salary let's say i want to have the total salary for male and female so what we need to do is i will create a new measure what will be the new measure total salary male so here i need to use my calculate keyword calculate once i use the calculate you can see it expects a expression and a filter evaluates an expression in a context modified by filters so what is your expression going back to the notepad this is your expression and this is your filter okay so see how i'm using the expression i will be using sum of estimated salary bracket closed so this entire thing i am using here okay and after that i will be using the filter what is the filter i'm going to use gender equals to mail so i'll use gender equals to equals to mail i'll close the bracket and i will click on take one that means my measure is created i will just copy it and recreate the measure for female female female take it now by both the measures are created total salary for male total salary for female okay we'll create a card let's try to create a card and i will add total salary of mail copy paste it and i will add total salary of sorry total salary of female simple now this is how it looks like right of course if you want to show these numbers you can also show these numbers without creating a measure let me just do that for you let me just minimize this for a little bit for a while here let me just add a background in the border so that it looks good we have created a new measure or a filter context and this looks good i'm just copy pasting it copy pasting these things now here i will be removing the measures that we have created and we will be trying to use it without creating a measure that means i want the total salary for male and total salary for female so in this box for male what i will do is i will put the estimated salary which is my raw column here i am doing a sum operation everything is good and going back to the sql query what we will do is we will add a filter right so my filter is going to be gender i am dragging my gender into filter and here i will be checking name simple and done this is done same thing for female i will click on my visualization i will drag and drop my estimated salary i will recheck if it is sum done and then i will add my gender into my filters and then select the female you can see how simple it is so some people think that dax is required of course dax is required there are hell lot of situations where tax will be required but there will be a lot of situations where dax is not required at all so if it is not required if there are other ways to do it 100 pick that way because once you start creating backs and start creating measures and calculated columns that means you are increasing the column sizes right well you are adding one new column you are adding one new measures that's going to be impacted as well because your memory will be impacted your file size will be impacted and all those things anyways this is how the filter context looks like i hope everybody understands about the calculate method there is another way how to filter you can also use a filter something equals to something we'll try to do that as well so i will just do it for the male population i will just close it close everything close the filter and then i will go to my formula copy paste it create a new measure now total salary mail using filter okay so here instead of this one what i will do is i will add a filter so i will add filter again in filter it is asking what do you want it returns a table that has already been filtered so in the filter what you are doing is you are by default filtering it so let me just write down and i will explain the difference between this method and that method so filter of table so i will pass my table which is term modeling and then i will ask the filter expression which is gender equals to male if i close this everything should be okay double close and done now in this card i will be using the new variable you can see the numbers are matching all the three numbers are matching now looking at the formula let me just copy paste this code to my notepad and copy paste this code to notepad so that we will see what is the difference between each one of them this this and the second type is this okay these are the two methods which we learned how to filter this is using filter and this is not using the filter keyword so this is basically directly like this okay you can assume this to be this but the first step is nothing but it's a two-step what it does is it first filters the table based on only male and then on top of filter it calculates the sum of the salary from it's basically like this so in a way both are same both will definitely give you the same amount of values but these are just two different ways of filtering it out if you want to do it using filter you can do it without doing filters also it's possible okay so these are both the ways to deal with it now variables var my bar equals to 1 this is my data type this is my variable name and this is my variable value i think everybody has already known about all these things right our next step is going to be understanding more about if else blocks and nested if blocks now i assume that you might have already gone through various python programming languages so you might already be knowing about if else blocks right so for example if you want to write whether a column is whether a value is even or not right if that value let's say is equals to 2 that says that we can print a is even something like that so if else is nothing but print order okay something like that so if else blocks are nothing but if this then do this if not then do something else right so that is the reason uh what we can do is we can create a first method first we can create a new column let's say i want to create a age bin okay so we can create a new column and this will be age bins now before creating it i hope everybody knows that there is a column called as age right let's say we will create a new column which is age bins using if else blocks so what i need to do is if logical test if my age is greater than equals to 60 then what i will do is above 60 else below 60. simple it's a very simple if block that means if this then this else this that means anybody's h who's more than 60 then i'm going to create a new column which will say above 60 other than that all the values will be below 60 simple so let me just add that newly created column which is age bins and this is how it is all the values are below 60 and if you just filter out the edge like this it is above 60 so oh age is being ah there are some above 60 as well right and let's say you want to take it to the next level that means you want different bins for example you want to create bins of 0 to 20 edge 21 to 40 41 to 60 and 60 plus something like that how can we do it simply i will just copy paste this code that means if this they do this else else if greater than 40 then mark it as 40 to 60 else if greater than 20 mark it as 20 to 40 else below 20 something like that so you can see if this if this then do this else if this then do this else if this then do this or else do this now i'll just change it and you can see the age greens are updated now if you want to just create a new bar chart and want to see how the distribution looks like based on age bins let's say i will have my age bins in my axis and let's say i want to have the total number of customer id i will have it in count yeah this is how it looks like you can see this is how the data looks like 20 to 40 what is 60 above 16 below 20. of course as i already told you there are multiple ways to do this right without this is how you have created using if else blocks you can also do make your life simple and do it using the power query editor go to the power query editor add a column create a conditional column let's say new age bin and this is how we can also create if age is greater than 60 above 60 else if age is greater than 40 then 40 to 60. else if h is greater than equals to 20 then 20 to 40 else under 20 something like that and you create it go to home page close and apply and then you can just simply let it be let it finish it's preparing i mean it's loading just one second i will simply expand this one let me just drag it here let me just drag it here i will expand my table so we already have age beans i am going to add the new age greens where is that new edge pins you can see my new age bins is also having the exact values like we have used using the if else blocks now this is all about your advanced dax functions now before getting into time intelligence functions i hope that everybody knows about calculated columns and measures right calculated columns are something which is done on row basis for example if you want to combine first name and last name and you want to create a final name or else if you have ages probably you want to create some bins or else maybe let's say you have 10 year in months you want to convert it back to years or else vice versa so anything that has an impact on a row level is basically a calculated column and measures measures are something which is done on the table data itself for example if you're dealing with a lot of records and let's say you want to find the total count or else you want to find the total sum of the sales or else the total balance so all these kind of things on a tabular format on a table and on a table as a whole we have to summarize everything so that's basically a measure right now what is time intelligence functions time intelligence functions enable you to manipulate data using time periods such as years quarters months days and creating calculations over time those time periods the most common time periods that we encounter in business scenarios are year to date month to date quarter to date last year full year rolling 12 months etc etc there are many other time intelligence functions in dax now in this session we shall be learning about few of them and of course other than that what is what is being taught in this class you can just self practice it because as i already told you power bi is super simple to understand and super simple to implement right so the first function which we are going to use is going to be total ytd now just imagine you have sales data or else sales and you have month let me just increase the format uh and let's say you have month let's say 120 dollars for jan 2019 180 for feb 2019 1230 for march 2019 200 for april 2019 and so on and you have data for like 10 years 20 years whatever you have right ytd basically means let's say i am selecting march 2019 that means year to date that means the entire sum till the date you are providing of course if you provide fab 2018 that means jan and fab's data if we take an example that will make us more you know it we will get get more understanding on these concepts now the formula wise this is how it looks like the left side is my variables which is ytd total sales it could be anything equals to total ytd and then i'm passing that on what on this measure that on top of some of something then dates so let's go back to the power bi desktop to understand more about it i have done a small visualization a small report kind of thing and i have multiple things to cover so first topic is going to be ytd now before ytd i will show you how the data looks like of course churn modeling is something which we already have i think everybody knows about it we have air passengers now churn modeling does not have a date column right so i cannot show you how time intelligence functions work so we have air passengers now this is my month and this is my total passengers simple so here i'll just close it what i have created is i have created a new measure which is total ytd passengers now if you look at it it tells me that hey can you create the total vtd for the number of passengers of course to enable the total ytd you need a slicer that means you want to select the dates right that's the reason i already have a month slicer this table is basically being used by air passengers 2 because that's another query why if we use the same query probably if we filter it out my table will also be filtered right that is the reason i i want to i have shown a table using another query okay same error passengers just copy pasted the queries now going back to the formula total ytd so here we have created total ytd sum of air passengers on my date if you simply don't remember these formulas it's completely okay because once you start writing the formulas in in in power bi whether it is total ytd qtd let's say ytd it already tells you your first should be an expression that means it should be a measure and then dates and then filters so that's the reason we already have all these things okay now if i just oh there is some okay small mistake and done so this is my total ytd passengers now look at this particular table what i will do is i will first select january so for january my white it is going to be january's data only right that means year to date so if i click on january you can see my value scene is 112. if i click on fab that means it's going to aggregate the values 112 plus 118 that has to be 230. right if i do this then 230 now if you ask me what is the difference between yearly date yearly data and ytd now if you are selecting yearly data irrespective of the month if the year is 1949 then the yearly sum will be the entire 1949 data right but white it is something like year to date that means till the date we are selecting what is the annual sales or what is the annual number of passengers right similarly if i just do it for december that means it's going to accumulate from 1949 january till 1949 december very simple again the same concepts if i do january 1950 it's going to show me just january 1950s data 115 and similarly if i do july 1950 it's going to accumulate from jan 1950 till july 1950. as simple as that that is that is a concept of ytd and why i'm not showing you other uh methods like other functions because it's all the similar way if you want you can just simply copy paste the codes and then create another measure to validate let's say total mtd passengers okay because it's monthly data so mtd there is no concept of empty empty means month to date let's say your date your data is in daily format and if you are doing month to date that means if you are selecting 15th of april that means it's going to accumulate from 1st to 15th of april but as we just have monthly data so mtd doesn't really matter but still for your understanding let me do this mtd and just to take and i will create another card for this i will just add mtd see july 1950s mtt data is going to be july 1950 is normal data so it doesn't really matter so it's going to show you the same data so mtd and qtd qtd will be impactful because qtd is just the accumulation of three three months right now what is qtd one year is divided into four quarters and let's say you are selecting fab 2019 or feb any year that means it's going to accumulate jan plus feb's data simple you can do the same thing and just try it out let's say i'll just i did the same thing from mtd to qtd and i will just change it to q today done and you can see for july for feb it's basically accumulating the january and the february data january and february is 2 41. now what will be jan feb march is first quarter april may june is second quarter let's say i do may so may you can see for may the ytd data is the accumulation of january february march april may that means 6 42 but for qtd it's going to be only april and may because that's basically your second quarter right and this is how you can all create the total ytd total qtd and total mtd so this was one of the major time intelligence function apart from that we have another function called as previous month again we also have previous year previous day next month next day so if you learn one of them it's very easy for you to learn other things as well okay so for previous month you can see the formula is total sales something that means i want the total sales for the previous month simple let's say my slicer has been selected for may and i can i can see my this month's sales i can also see my previous month sales as simple as that so we are doing calculate what what are we calculating the measure that means sum of some column and then there is a there is a function called as previous month okay again so here you can see on the screen i have three three things let's say i'm talking about may right now in may what is my current value 125 you can see this is showing me the passengers this month 125. what is my previous value 135 this is showing me the previous value which is 135 what is my next value 149 this piece of card is showing me the next value let's see what each one of them is let's let's click on this one first previous passengers which is using pass prev measure if i click on my measure this is how the measure looks like i have calculate calculate what i am calculating the total sum of the air passengers now we already know how this thing works right this is nothing but some of all the air passengers right and then i'm using this so this basically is acting as a filter right you can use this or as you can also do filter month equal to you know date add so there are multiple ways to do this kind of things as well i will not make it complex we are just focusing on the time intelligence problems so that's the reason let's talk about previous month itself so here some of this comma previous month see if you are not if you don't understand you can simply type in previous month and it will show you what exactly it is it returns a previous month if you are providing dates it's going to return you the previous month that's it if selection is made your output will be april simple and then this is how it is and the difference between this and this is nothing just a single keyword if i click on this i'm using pass next if i click on pass next i'm using next month very simple let's say you want to create another kind of card to show which month is selected month so i'll create a card let's say i'll create a card and here i will create another measure which is previous month is equals to previous month on what on my date column my date column is month the columns name itself is month don't get confused month and then i click on this done and then i drag my previous month and this is how it looks like okay of course you can just have the you know you can format it as well it's up to you okay and this is the previous month and similarly you can also add the current selection or else the next month as well let's say let's create the next month next month is equals to next month on what on month done click on the visualization just drag the next month done simple so it basically tells you that my current selection is may my previous month is this and my next month is this now this is how we can actually use the time intelligence functions it's very useful for let's say you are doing sales analysis or any kind of analysis where you are actually comparing your previous values and your current values right then definitely it's going to be useful and so yeah i mean that's it about the time intelligence functions [Music] what is x and non x which basically means there are many functions like sum average max and all those things and they have their x variants as well for some we have some x for average we have average x and there are some other x functions as well but in this session we shall be learning about few of them now what is the difference between sum and sum x sum as we already know that it's basically a measure right for example if we want to find the total sum of sales or total sum of some column whether it is on the entire table or whether it is having some filter clause but any of the scenarios you use sum right now what is sum x sum x is basically an iterator function it works row by row so it is again a measure but it works row by row and iteratively it calculates so we will talk about that from the practical's point of view now sum x has awareness of rows in a table hence can reference the intersection of each row with any columns in the table so we'll get get started with the sum and sum x example for this particular exercise i am using this supermarket sales data set you just have to search supermarket sales data set kaggle and you will get the link in the first link itself super sales data set and this is what the data set is i have i'm using the same data set so let's get started with it we'll jump on to power bi you can see here i already have a small dashboard or a report this is my total revenue by date let me just show you the data now this is how the data looks like we have invoice id branch city customer type gender product line unit price quantity and all those things now just imagine you have the total number of products sold and the unit price of a product that means each product cost 10 dollars and you have 10 products so what is the total sales for that product 10 into 10 right 10 dollars into 10 products so similarly here we have a column called as total which basically is unit price multiplied with quantity we can just validate it for the first record 74.69 multiplied with 7 which gives me something plus the tax which is 26.1415 that makes it the total column now can we also have the total on the go without creating this column without having this column what is the advantage the advantage is that instead of n number of columns you will be using n minus one columns which is a win-win scenario right because the more columns you cut down the more memory you can get for your activities of course because more columns more memory consumption right so how to achieve it using sum x so let me take you to the main screen which basically shows some graph total revenue by date and i have a simple table which is like date and the total column we already know how sum works if i just have to show you so total sum is equal to total sum of sales is equal to sum of what let's say sum of total because we already have this column right can we use any other columns no we cannot do that we cannot do something cross something and then do a sum on sum on it so if i do this and then if i have a card on it this is how the total sum of sales looks like but our main activity was can we get a similar column like total without using the total column right so here what we will be doing is we will be creating a sum x measure [Music] total sum x which is sum x you can see when i'm using the queries when i'm calling the dax functions power bi is automatically suggesting me that you need to pass a table and an expression okay returns the sum of an expression evaluated for each row in a table right that means if you have unit price quantity and something else and you are doing some mathematical operations on it and you want to have a new column of course if you are not using some x function you can create your own conditional column like total right but here we don't want to use total we want to get rid of it so here i will pass my table name which is my super sales and then i will pass my expression what is my expression i already explained what is total total is quantity multiplied with unit price plus tax simple so i will be doing quantity multiplied with unit price plus tax and then i'll close the bracket and then i'll tick that means my measure is done i'll click on the table and then i will just add this column and you can see that it is exactly the same like total now even if you delete this total column you can still have this function now what is the advantage of this one over the first technique which is creating a separate column the advantage is that when you use your x functions like sum x the data is created in memory so when you are calling the sum x function it does it for each rows right so what happens is in back end it creates the table in memory and once you are using it in form of a table then that means you are using the data from the memory directly so you don't have to create a separate column out of it that is the advantage of it right so you can simply go and just delete the column as we have already validated that i don't you can see both are same right so i don't need my total column you can simply go to your power query editor and then simply drop it off because we don't need it and then close it done of course this will be a failure because this column does not exist so i'll just close it off it's loading so i hope everybody is clear that you can see everything is done so okay the idea is that without having a new column we are still able to calculate some functions using the sum sum x method right so i'll just remove this step because we can get this back again okay now this is done let me just add it so that we have it in the in the in the table itself now our next function is you can see this is how it is used sum versus sum x next one is we'll talk about average average a and average x now average we already know average means averaging out the data right so if you want the total sales let's say here you have the total sum x let's say i'll be using this particular table so i have product line unit price quantity and tax right let's say i want to use the total column here so here you have the total column and you just want to change it to average right so this is your average average of total i mean we already know how average is calculated right now what is the difference between average and average a so i'll take help of excel to explain you what is the difference between average and average a because using power bi uh it will be little bit difficult to explain but using this we can easily explain the concepts are same so let's say i have a product and then i have sales so let's say product is let's say i'm talking about some products let's say soap pencil pen eraser something like this and then product x1 and product x2 we have six products here and then let's say okay we'll take help of [Applause] sheets.google.com so let's say i'm using a blank sheet let's say i have product and i have sales so let's let's take some random values a b c b e f these are my six products let's say 10 15 20 25 something and 10 right now what is average average and average a okay so average is nothing but if i do average of this it basically calculates 16 right now what is average a let's say i have 5 records what will be the average 10 plus 15 plus 20 plus 25 plus 10 divided by 5 simple it's 16 right average a will also return me 16 average a of this will return me 16 same so what is the difference the difference is that sometimes in your numerical columns there could be some null value there could be some string values or some sort of text values right for example you have a text like x y z now if i hit enter that means this one is considered as 0 as per average a but average still does not take care of it so you can see once i am using a text variable here average is still considering only five elements so your average is still 16 but average a treats it as so this is what average a trades like this now the total sum is 680 and then it is 80 divided by 6 which is 13.33 so that is the difference between your average and average simple right now moving on to average x what is average x the idea remains the same the idea is that can we have this column without creating the column like without creating a physical column can we have can we create the data can we use the data maybe using some memory memory uh usage right so here i'll be doing the same thing i'm just calling my new measure where i will be having average x which is average x like total average x okay average x of what if you type in you are seeing that we have to pass a table and an expression right simple it's going to be same so the table is super sales and the expression is unit price multiplied with uh quantity plus your tax do this and then close the bracket click on take and then in this table i will be adding that and now you can see both the data are same so whether you are doing an average of your that column or you are creating it in memory so the idea remains the same so why don't we use the x function so these are the advantages of x function we can simply use x functions without creating an extra column which saves your memory which saves your cpu utilization and it's i mean there are a lot of advantages of it so you can explore this kind of x and non x function to understand more about it but generically speaking x functions basically saves the you know space you can create the table in memory and then you can use it on the go so these are all about your basics of your x versus non x function in the next videos we shall be learning about some next topics thank you [Music] [Music] you

Channel: Satyajit Pattnaik

Views: 47,094

Keywords: power bi tutorial, power bi desktop, power bi dashboard, power bi training, power bi demo, power bi course, power bi project, power bi, powerbi, analytics with nags, dax, power bi dax, end, end to, end to end, full, course, dax tutorial, dax expressions, dax functions, functions, expressions, satyajit pattnaik dax, satyajit pattnaik power bi, satyajit power bi, power bi dax 1 hour, dax in 1 hour, learn power bi dax in 1 hour, dax 1 hour, power bi 1 hour, dax tutorial full

Id: Ar6hSITP-w4

Length: 82min 26sec (4946 seconds)

Published: Fri Sep 17 2021