The Top 5 DAX you need to know before your next Data Analyst Interview

hey guys satished patnaik here and welcome back to my channel we all know how important dax is dax which basically stands for data analysis expressions is one of the most important topics in power bi many people are confused what exactly are tax in case you want to know what is dax and you want to understand each and every thing about dax i already have a one hour video especially on decks you can watch out that video the link will be in the description below but in this video i shall be talking about five most important tax functions which you need to know before going for your interviews so let's get started [Music] data analysis expression dax in power bi was practically designed to simplify data analysis crunch numbers visualize patterns and do a lot more dax allows users to customize certain variables and even generate new ones as a beginner it is absolutely important to understand exactly how to use or implement various dax functions in power bi which may seem like a tough task the entire power bi dashboard is user friendly and you can learn how to do a lot more with it but first you have to understand precisely how to use even the most basic dax functions as i already told the basic dax functions video is already there on my channel you can watch out and check out the link in the description below to go through the basic two advanced dax functions well in this video i shall be talking about the five most important dax functions number five calculate function [Music] let's try to load the data and then we will see how to work with calculate function so we are using a random data set here which is telco churn something and let's load the data within couple of seconds the data will be ready now we're getting started with the calculate function now calculate to be very honest is one of the most important functions in power bi and is a very important function so in order to use calculate first we need to understand what exactly is calculate precisely speaking calculate is just an english word right basically it means you have to calculate something now for an example you want to calculate the total sum of cells in a particular area that means you are calculating the sum of sales in a particular area which is where area equals to something based on a filter now what exactly is filter will be talking about in the next part but let's just focus on the calculate part so in order to calculate use calculate let's try to create a measure so we will be creating total do we have total sales we have total charges so we'll try to create total charges total charges and i will mention it as ms to notify that this is something which we are creating on the go in this class so ms equals to the moment i start typing in calculate you can see it is already showing me that calculate evaluates an expression in a context modified by filters what is a filter how to use a filter is going to be taught in the next part but right now we'll just focus on the calculate part calculate and then you can see calculate is taking expression as a first argument and then it takes a filter as a second argument i will try to pass an expression what is an expression expression could be something like sum average count these are all expressions right let's say i want to calculate the total sum of total charges the sum of total charges i press enter and then brackets close the moment i type in comma you can see the filter will be activated comma filter is activated now what is the filter do you want to apply let's say i want to find the total charges for male customers or for female customers so here what i will do is gender equals to equals to male bracket close and then this is what the query of calculate looks like and the second part is basically the filter part but here i'm not using the filter keyword i'm just doing a normal filter i'm not using the filter keyword so once i click on this that means my total charges ms is created how to validate what are the numbers create a card and try to use the newly created failed to it 8.1 million total charges is basically by the male customers in case you want to do the same thing for the female customers copy it create another measure let's say i will notify it as female ms and then i will here change it to f e m click on tick done i will copy paste my card in this card i will remove the old field and i will add the newly created field and here you go so this is how you use calculate keyword or the calculate function it's a very powerful dax function but honestly speaking power bi is very simple and easy to understand the moment you start typing in the functions you will automatically automatically get the suggestions and your life is sorted so that's all about calculate function number four filter function now we shall be talking about filter function filter dax function now the way we created calculated calculate a keyword basically we used calculate then an expression then a filter now just imagine you are working on multiple tables and multiple tables are connected with each other using multiple other columns and you want to apply a specific filter on a specific table in that case you will have to use the filter keyword i will just quickly change this piece of code for the time being i will note it down in my notepad so that we can compare the results you can see calculate i will just convert it into a separate line and this is how my calculate function looks like right so similarly i will create another function another measure using filter function now you can see i have just used the same one fms but using filter i will notify that my newly measured new measure is basically this one okay so that we can have the difference now here as i told instead of this i will be using the filter keyword now how to use filter don't worry power bi will definitely help you with that so i'll start typing in filter f i l t e r filter returns a table that has been filtered so i'll click on this it is asking which table do you want to filter on i'm you i'm going to use the telco churn filter itself so i'll be using the same table name comma what is the filter expression so here i will pass gender equals to equals to female brackets close done brackets close you can see why i use two brackets because in this bracket this bracket is getting closed so this bracket has not closed yet so close it tick done your new variable is created your new measure is created how to validate it i will create a separate card and then i will drag my newly created total charges fms using filter drag it the number should match 7.96 million and it is matching you can see i will just take this piece of code and i will place it here the only difference here is here automatically the interpreter is understanding that this particular column belongs to teleco churn belongs to this table but let's say you are using multiple tables multiple tables are having multiple relationships you are getting some of some particular column from table a but you want to filter on table b that can be done as well using the filter keyword that's all about filter keyword it's very easy to understand and just takes little bit of time to practice that's it see you in the next function number three related function so we learn about related function related function is basically one of those dax functions used to relate something from another table right so for this exercise i have already taken two data sets one is going to be a table one which has all the employee information id name gender salary manager id and another table which is containing the manager details it's going to be manager id manager name division column 4 so if you want we can remove this column we don't need it and i'll just rename it to manager and here i will rename it to employee done i'll just close and apply and then i will try to create a related column so here i'm going to this data folder data section i'll click on manager and let's try to create a new column let's say i want to create the new column let's say manager underscore salary okay now here just imagine i am creating manager underscore salary and i'm going to relate that from the employee table how can we do it i'll simply use related the moment i start typing in related you can see related returns a related value from another table how do we use it i'll simply click on this and i'll start it is asking me column name so i'll start typing in column name so column name should be salary okay so you can see there is an error that means salary cannot be found and just quickly check i'll just copy paste it i will quickly check it is salary okay so why we are not able to create a column called as salary okay so now i got it that probably there is no relationship between these two tables so i'll go to the relationships part and i'll try to build a relationship okay now my relationship is created i will go to my table here i will again start typing in salary the moment i now type in salary it is already giving me a result do you want employee salary yes i want that and then records close click on tick and now you can see three salaries are populated against each manager now this is a very simple use case of related there are multiple other scenarios where related is used related is also used in many complex scenarios as well but make sure you try to go through it particularly before going for an interview number two x functions which includes sum x average x min x max x what exactly are x and non x functions well we'll not be talking about non x functions which are basically your sum average mean max and so on but we will be talking about some of the x functions now we'll take an example of some x function what is sum sum is an aggregate function that we already know sum is an aggregator function it works like a measure calculating based on the current filter context but sum x is an iterator function it works row by row sum x has awareness of rows in a table and can reference the intersection of each row with any columns in the table sounds little bit confusing but i will try to make it more simpler just exact just imagine there is an example like this i'll show you the data now this is how the data looks like i have super sales power bi data now just imagine there is a column called as unit price a column called as quantity a column called as tax and there is a total column now total is nothing but unit price multiplied with quantity plus tax if you just want to validate it you can validate it for the first record 74.69 multiplied with 7 plus 26.14 which is 548.97 and that is all about total now just imagine as you have created this column called as total when you are creating a new column your memory is consumed consumed memory consumption is more computationally it will be more expensive can we create a column like this without creating a column basically without creating a physical column can we have this data in memory the answer is yes using sum x function you can create exactly a column like total you don't need that particular column how can we do that simply close and apply as you can see here let me just cancel it out now i will simply show you the formula i have already created the sum x measure now this is what the sum x measure looks like measure name equals to sum x and then i have passed out the formula that's it so if you want to create on the go maybe i can create it as well sum x measure equals to sum x the moment you type in sum x it returns the sum of an expression evaluated for row each row in a table okay how what is the formula unit price [Music] okay it's asking for table super sales okay unit price multiplied with quantity plus tax that's it bracket close and done my sum x measure is created now in the same graph i already have date and i have total we will try to add the newly created measure and will try to compare whether we are getting the same results or not so if i just quickly take it you can see the results are completely same exactly like the way some x function works average x min x max x also works the same fashion number one and one of the most important function is going to be time intelligence function so here we will be covering ytd functions mtd functions and qtd functions coming on to the number one function the most important dax function in power bi is going to be time intelligence functions so time intelligence functions are by far the most important dax functions in power pay one of the mostly asked questions in interviews especially in the data analyst and the power bi interviews what exactly are time intelligence functions enables user to manipulate data using time periods such as years quarters months and days creating calculations over those time periods the most common time periods are year to date quarter to date month to date last year full year all these things now try to understand the scenario what exactly is to date just imagine you have data from january february march april may june let's take six months january let's say 100 let's say 50 60 50 thirty hundred now if somebody asks you what is the total sales it's quite easy hundred plus fifty hundred fifty plus sixty two hundred ten two hundred sixty two hundred ninety three hundred ninety very simple right what you need to do is just simply call some of this entire column done 390. but what if your manager asks or the company demands that we have to give the flexibility to the users if the user has selected let's say may that means the user has selected me or the client has selected main that means the client wants to check what is the sales client or hire management whatever let's say my manager wants to check may that means he wants to check what is the data till date what is the values till date what is the sales till date not beyond that right so in that case june will be not considered so this is where your till date functionality comes if you talk about may the ytd values the year till date values will be these 5 records it's going to be 290 the month till date is going to be maze record which is 30. the quarter till date is going to be april may and june falls under quarter 2 but the user has selected till may so june is unseen data so here the output will be 80. so this is all about time intelligence functions now how to implement time intelligence functions very easy i already have a pbix file for you probably you can also check out my other video as i already explained in the beginning of the video that i have an end to end dax tutorial which is around 90 minutes and it is more than enough for you to understand each and everything that is required index well tax is like an ocean if you start reading about it probably you will take a lot of time finishing it but i have covered almost everything in that video coming back to this video coming back to this topic we have this particular pbx file where i have captured some of the time intelligence functions now you can see on the left hand side the month slicer i have selected may 1950 you can see total mtd may 1950 total mtd is 125 which is visible here what will be total ytd it's going to be a combination of jan till may what is giant till may 115 plus 120 641 plus 141 282 282 135 something plus 125 so if you combine them it's going to be 642 115 plus 126 plus 141 plus 135 plus 125 that's going to be 642 and you can see 642. cool right so qtd is going to be combination of april and main now we'll see what is the formula it's very simple formula if you click on ytd i have just called total ytd see as i told you writing functions in power bi is super simple you just call the function it will automatically tell you everything pass the expression then dates then filters right so it's going to be super simple but we have already written down the queries total ytd is nothing but this is the expression and then after expression this is your date that's it and it is able to calculate the total ytd what if you want the total ytd of the last year that means if the user has selected 1950 may this year's ytd is clear i also want to check last year how was our performance till may in that case see imagine this way you already have the total ytd you just need to take the total ytd variable or the measure pass it to a calculate function and then add a filter that month equals year equals to year minus one everybody is clear clear so here i will open the ytd previous you can see in calculate my first expression is going to be the total ytd that i have created here in the filter i am using here equals to here minus 1 and here equals to here minus 1 is nothing but a dax function called as same period last year internally does the same thing same period last year okay so these are the most important functions these are the five most important function that we wanted to discuss and that's it that's all about this video in case you like it please like share and subscribe the channel and see you in the next video till then bye bye [Music]

Channel: Satyajit Pattnaik

Views: 27,925

Keywords: satyajit pattnaik, data science, data analytics, machine learning, data analyst, artificial intelligence, Projects, top 5 dax, top 5 dax power bi, best power bi dax for interview, advanced dax, advanced dax interview, powerbi interview dax, power bi interview questions, top 5 dax functions in power bi, satyajit power bi, satyajit da, satyajit dax, satyajit top 5 dax, satyajit power bi free, top 5 dax in power bi, best dax in power bi, dax interview, advanced dax top 5

Id: 6bk3JXm6JU0

Length: 23min 13sec (1393 seconds)

Published: Thu Mar 31 2022