Complete DAX Course - From Basics to the Advanced Level | DAX Tutorial | BI Consulting Pro| Power BI

hi there welcome to this brand new tutorial on tax if you want to make your career on power bi then you must know dax next would help you to perform the advanced analytics and calculations in power bi whether you are a beginner or you are an intermediate user this tutorial is going to help you a lot to grow successfully as a powerbeat developer or to perform calculations in power bi so let's get started [Music] over here we will also discuss about filter and row context which is a very important concept index and if you don't know that then definitely you would face a lot of challenges while working on decks calculations the very first question comes into our mind what is a dax well guys dex or data analysis expression is a library of functions and it's a formula based language which has operators that can be combined to build formulas and expressions in power bi analysis services which is going to be your tabular model and powerpivot in excel data models now let's get to know why dex is so important as i just mentioned you can perform your dynamic calculations or advanced analytics with the help of dex formulas learning how to create an effective text formula will help you to make the most out of your data when you have all the information you need you can start solving business problems that affect the profitability of your business in order to learn banks you really don't need any prerequisites however if you have worked on excel and you have used a formula that is definitely gonna be an advantage for you if you really want to learn dax then you have to use certain tools as well and the best tool that you can learn with power bi would be your deck studio and tabular editor if you are a member of our channel or you are watching our videos on youtube then you must know that in my many of the decks and these videos i have used desktop desktop2 website this is a website by sql bi and over there you can practice your decks you don't need any other software tool or you don't need any kind of data everything is all there you have to simply go to this website desktop2 and there you can start working on dax now let's understand the fundamentals of dex on your screen right now you can see one formula is written over there total says equal to sum then there is a table name and the column name over here the very first would be your name of your measure so whenever we are going to perform any calculation in power bi using tax we call it a measure so a would be your name of the measure then b basically represents the beginning of the formula which is going to be your equal sign or you can also call it an operator then next which is c is the x function that you are gonna use to perform a certain calculation over here we are just using a very simple x formula which is calculating the total sales and we have a column in the sales table with the name sales amount so if we do the sum of that whole column we will get the total sales now next to that is d that means the parenthesis so parenthesis basically are going to surround an expression containing one or more arguments next would be e that is going to be your table name and f is going to be your column name so this is all that you need to know in order to understand the fundamentals of text you should know where to write the name of your measure you should know how to separate them from the formula or the expression you should know where to use your index function or where to use your table or column name next we are going to understand context index we have two context filter context and row context both are very important they are going to enable you to build high performing dynamic analysis on your screen you can see one very simple example where we are calculating the total sales for united states we are using our total sales measure where calculate is our dex function which is also known as filter modifier over here we are choosing the country equals to us and this is going to be our filter so let's understand it a bit further very first comes the row context whenever you have to perform calculations row by row by row that's known as your row context for example in a table as a visualization you have all the country names and you want to calculate their total sales so as soon as you would drag and drop the total sales measure into that table then all the row context where in a row we have the different country names that's going to apply over there and that would be your broken text however if we talk about the filter context a filter context is one or more filters applied in a calculation which determine a single value or result you can use filter context to reduce the values that are included in a calculation now let's see an example over here over here as i mentioned the row context you can consider this example that you can see on your screen where i'm using sum x so sum x is another dex function which is help you to evaluate some of an expression so over here i'm using sales which is my table then sales amount minus cost amount so this is going to give me the calculation in the context of row row context calculation for each row with the values within that row now if we talk about the filter context filter context is set of filters that applied before that table arise for use that means final table would have already the filters that we have applied on it for example over here we are calculating the sum of the sales amount and if we are going to apply certain filters on that so that would be your filter context because even before calculating the sum if i'm applying over there the country equals to us or something like that then this would be your filter context so calculation is going to happen but before that we are also applying a filter on it so over here you can see that if i'm going to apply the different filters on this total sales amount from the filter spin in power bi for example er country or city that would be a filter context now let's discuss about the different functions that are available in dax the very first kind of functions are aggregate functions these functions calculate a scalar value such as count sum average minimum or maximum for all rows in a column or a table as defined by the expression so at the time of publishing this video the functions that you can see on your screen are all the text functions that are available you can pause your video right now and can read about it next would be the date and time functions these functions helps you to create calculations based on dates and time many of the functions index are similar to the excel date and time functions however next functions use a datetime data type and can take values from a column as an argument on your screen right now you can see all the date and time functions such as calendar calendar auto that are going to help you to create a table then we have date date difference date value d edate etc filter functions these functions help you to return a specific data types look up values in related tables and filter by related values lookup functions walk by using tables and relationships between them filtering functions lets you manipulate data context to create dynamic calculations and please be patient soon you will see all of them in action during our demo session these are the filter functions where you can see all the different kinds of filter functions available index such as all all cross filter all except all selected etc next if you have to perform the financial calculations then definitely you should have some of the text functions too that can do that for you financial functions index are used in formulas and perform financial calculations such as net present value and rate of return these functions are similar to financial functions used in microsoft excel so if you are coming from the excel background then definitely you know about that or if you are from an accounting background then i'm sure you already know about them so these are some of the functions that you can see as a financial functions on your screen next we also have information functions index while writing the calculations you need these kind of functions text information functions look at the cell or row that is provided as an argument and tells you whether the value matches the expected type for example the is error function returns true if the value that you reference contains an error on your screen right now you can see all the information functions available in tax for example contains contains row contains string etc now let's talk about the logical functions index we have logical functions that act upon an expression to return information about the values or set in the expression for example you can use the if function to check the result of an expression and create a conditional results this is going to be very important or you can also use a switch text function as you can see on your screen and these are the logical functions available index that you would use in your day-to-day life while performing calculations or advanced analytics or dynamic analysis on the data next comes the math and trigger functions so we have different use of calculations in different perspective for example some people want to perform the mathematical calculations some wants to perform the financial calculations or maybe some just want to perform the time related calculations so we have all the different kinds of text functions available in tags and one of them is math and trigger functions the mathematical functions in data analysis expression or text are very similar to excel mathematical and trigonometric functions over here you will find all the maths and trigger functions which now you can see on your screen next comes the parent and child functions and this is one of the most important x function where you can use this function to get to know what are all the managers who are employees as well in your organization or you want to check the hierarchy into your organization etc these functions manage data that is presented as a parent-child hierarchies or sometimes you have to create that kind of hierarchy to get the more information out of the data over here you would find path path contains path item path item reverse and path line next comes the relationship functions functions in this category are for managing and utilizing relationship between tables in your data model you have different kinds of relationships such as one to one one too many or many to one and these functions are going to help you to utilize those relationship to perform the calculations that are going to require to get the information out of the data in case of relationship functions we have cross filter related related table and use relationship now let's talk about the statistical functions statistical functions calculate values related to statistical distributions and probability such as standard deviation on your screen you can see all the statistical functions available index now let's talk about the table manipulation functions these functions return a table or manipulate existing tables over here we have different functions such as add columns detail rows filters generate series group by etc and these are going to help you to perform a lot of advanced calculations while working or creating the measures in power bi index includes a set of text functions based on the library of string functions in excel but which have been modified to work with tables and columns in tabular models this section describe text functions available in the text language and over here we would find functions such as combined values concatenate values fine fixed format left length etc these are the functions that you used in your day-to-day life to perform many calculations for example when you have to combine two strings then in that case you use concatenate function or if you need to return the starting position of one text string with another text string then you can use the find x function again you would see some of them in action while we are going to perform our demonstration and lastly over here is the time intelligence functions so data analysis expression or text include time intelligence functions that enable you to manipulate data using time period including days months quarters and years and then build and compare calculations for those times however you should remember that in order to perform calculations accurately in power bi using time intelligence text functions you must mark your table as a date table otherwise they are not gonna work over here we have different x functions such as dates mtd dates qtd dates ytd etc now it's time for the demo where we are going to write our different text expressions and we are going to perform amazing calculations so stay tuned with me till the end of this video filters are the core part of dex where we are going to filter the data based on certain conditions but how these filters works that means how these filters propagate so we are going to discuss about all this filter propagation concept index and we will talk about what is the filter propagation in that then we are going to talk about the cross filtering visuals then we will talk about the reading the initial filter context index and then also we are going to discuss about its flow so let's head over to my laptop and see how does it actually works before starting i would like to introduce you with my current data model that i'm gonna use i have this internet sales table which i'm calling the sales table it's my fact table and then we have the date product customer directories are my dimensional tables and also you can see there's one-to-many relationships and it's under star schema now let's move on to part where we are going to discuss about what is filter propagation so basically what happens whenever we are trying to filtering out our data we have to see what is the direction of this filtering how the filtering is going on so we call it filter propagation how filters are working inside your model so for example if i talk about over here here you will see this one too many relationship between the date dimension table and the sales which is my effect table and here you will see its direction of the arrow this arrow is going from date to sales table that means the filter is propagating from date or the dimensional table from one side of the relationship to the many side of the relationship one-to-many relationship simply means that you have one particular date in the date dimensional table and corresponding to that particular date there can be multiple rows in my sales or my fact table this is my table where you can see that i have my model name which is from the product table then i have total number of customers where i have created a measure which is gonna give me the count of total customers and their total sales so in each of these model name you will see that the total number of customers are coming same but their total sales are different even for for the first two products there is no sale over here when we are talking about the total number of customers that means we are not filtering over here the number of customers based on the model name we are talking about the total number of customers from our entire data set or the customer dimension table where we are using a measure but here there is no filtering is working on over here if i click on the relationship you will see the cross filtering direction is single the value of the total number of customers is the same for each of the product model name in the matrix the technical reason this happens is because the row labels in the matrix are filtering the model name in the product table in the data model before this measure is evaluated but these same rows are not filtering the customer table at all so that means there is no relationship between the product table and the customer table so our second concept over here was to discuss over the cross filtering visuals this is my table and if i bring any other visual for example i'm gonna bring a slicer over here and this slicer i'm gonna put a value for example let's say size so we are gonna check the size over here as a filter and you will see we have different size so if i select any of the size it's filtering my correspondingly table or the matrix where i have my data so that is cross filtering you can stop it if you will go over there in the format pane then you have edit in interaction so because with the help of this you can even stop it cross filtering means you are selecting something from one of the visual and the correspondingly other visuals are getting affected or they are getting filtered the filtering in a matrix is called initial filter context over here you will see this is my matrix and based on the model name my total sales are getting filtering out correspondingly for every model we have a different sales and that's filtering out that is known as your very first initial filter context so the initially filtered context is the standard filtering coming from a matrix before any possible modifications are applied from dax formulas using calculate what all that means that whenever we are putting any dimension over here and then the correspondingly measures or the values then the very first filter that is happening that is happening because of this initial filter context in the matrix so if i'll go back again in my data model over here you will notice that we have this product table from where i'm getting my model number and then i have a sales from where i'm getting the sales amount as my sales total sales for those particular model names so the filter is propagating over here using one too many relationships and they are the model names so corresponding to those model names i am getting the different sales amount those sales amounts are coming as a part of the initial filter context but apart from that there are other filters that we can apply for example just now i applied the slices of the size so this is the outer filter context that i'm gonna apply over here but not only that if i need to apply some more filters then i can use the calculated x function and using which i can apply those filtering because calculate function is basically used to modify the filter context now let's talk about the reading part of this initial filter context how we can read it so in this matrix you can see i have the different model name once more and the total customers and this one total customers are not getting affected reason being over here if i'll show you over here total number of customers so i'm applying dex over here to calculate a measure and this is gonna apply on the whole data set so that's why you will get the same number of total number of customers and it's not just corresponding to this actually this total number of customers are the total number coming from the customer table and there is no effect on this initial filter context from this one however it's gonna affect the total sales because it's gonna get the individual sales for each model name once you know what the initial filter context is you can mentally apply the following steps to your data model and check how the filters flow through relationships the very first we are gonna talk about the initial filter context coming from the visual applied to the underlying table in the data model which i explained to you earlier whenever we have this product table so from there one product is getting selected and corresponding to that many uh rows of the sales are there and which we are calculating over here and because of that we are getting this total sales so in total sales also we have applied the sum aggregation function dex function over here once we have this applied so this model name no matter what under this model name how many products are coming all of those sales are gonna appear over here so that is the very first step we need to always keep in mind how the filter is working the second one is the filter applied to the product table automatically propagates through the relationship between the tables flowing downhill to the connected tables so again you will see over here it's coming as a downhill we call it the downhill because we always put our dimensional tables on the top and since there is a one relationship and the arrow is also coming this way so we call it just like the downhill third is the connected table the sales table is then also filtered correspondingly to that particular product so if these two tables if you will see they are connected using the product key for one particular product or the model name definitely there is a one product key for one product key correspondingly there will be you will find a lot of product keys or the rows over there and hence it's gonna get filtered it out over here so let's now include one more field in this table or the matrix which i'm gonna call the total number of products i'm going to create a new measure and say total product what i'm gonna apply i'm gonna distinct count because it's uh that is individual product so i can just apply the count over here not the sum that's function under this mountain 100 model name you will see there are 396 number of products however for the mountain 200 and road 250 they are the different number of products so now it's giving me the count of those products and how it's actually happening what i'm doing i'm just calculating the number of products or the based on the number of product key based on this initial table filter you should be able to read this filter context so we call it whenever we are working on this type of matrix or whenever we are putting something in the table or matrix and the very first column the dimension column which is filtering out the data so we'll say it on this table model name is the initial filter context for total number of customers total sales or the number of products that's how we can easily read it even if this doesn't seem right to you yet realize that it is working as design understanding gives you power so stick with it until you are clear about how it works read it again and again go through this video again and again try to understand what is filter propagation how it is actually working then you will get to know exactly what is filter propagation concept index you should always keep in mind the relationship where the arrow is pointing if you will see back side of the arrow and the face of the arrow towards the sales table so that simply means your date table is filtering out the sales table which is your factable and database of dimension table as you can see on my screen i have calculated two measures one is using calculate index function and another using calculate table next function however you have noticed over here both are returning the same value so what is the difference between these two how they are getting calculated so let's figure it out here you can see calculate dex function evaluates an expression in a modified filter context and its syntax is calculate expression then filters on the other hand calculate table it's going to evaluate a table expression in a modified filter context while its syntax seems the same but you have noticed calculate is gonna evaluate an expression it's gonna return you a single value however calculate table evaluates a table expression it's gonna need a table expression calculate performs exactly the same functionality except it modifies the filter context applied to an expression that returns a scalar value so guys always keep in your mind whenever you need to return a single value or a scalar value please use calculate function however if you need to return a table as an expression then please use the calculate table now let's get started and see how to calculate this before going further let me just delete these two measures which i created already now you can see i don't have any of those measures so we are going to create these two measures once again using calculate and calculate table index functions now very first is calculate total sales i'm going to choose the country name which is spain so we have to write calculate it's gonna evaluate an expression in a context of modified filters and it's gonna return us a single value or a scalar value so write sum what sum is gonna do sum is the text function which is gonna add all the numbers in a column and which column do we want to add it's the sales sales order sales and now i'm going to apply a filter for the country so we can write filter table name again sales folder and country equals to spin so guys you can see over here in this measure i'm gonna create the total sales for spain using the calculate x function first i applied the calculate next function then i'm gonna use another expression inside it which is a sum gonna add the total numbers in the sales column and then i'm applying the filter where country is spin hit enter and now we can drag it in our field so let's do it now you can see that total sales for spain it's 1.22 million now let's use another dex function which is calculatable but before that i just want to show you something dex calculate table function can used to return a table as i mentioned earlier and you can see over here it evaluates a table expression in a modified filter context here you can see i have created another table using calculate table function where i wrote down my code calculate table calculate table is the dex function then sales order which is the table name and country equals to spain so if i go in my table view you can see that this sales order for spain which i just calculated using calculate table function it's going to return me all the values or all the rows only for the country spin however in sales order table if you will check the country column you will get to know it's returning all the countries over here because it's our base table and using this calculate table function we created another table sales orders pane and in this we are just filtering out data for the country where its country name is spain now let's calculate the total sales for the spain using calculate table x function so what we have to do over here we have to again create a new measure and i'm gonna give it a name calculate table total sales spin now remember one thing whenever we use calculate x function we write the expression inside the after the calculator but here we have to write outside so sum x sum x is gonna return it returns the sum of an expression evaluated for each row in a table so it's gonna apply on a table our table name we can either use the sales order for spin or here we can again use the calculate table function so now you can see that i am writing this text function on this text formula again in order to generate one table which is equivalent to the table that i just showed you comma now we have to give it an expression so expression over here the expression would be over here sales and let's check it out it's saying there's an error which should not be there so let me just check oh yes there should be one more bracket and here it goes now you can see that we have calculate table total sales for spain and the calculate total sales pin both are the same by means to say that they both are gonna perform the same functionality except so in case of uh calculate table the filter context applied to an expression is gonna return your table object however in case of calculate it's gonna be a single value in this video we will talk about maximum and minimum decks offer three different functions for finding maximum value and minimum value we will explore them in our demonstration file first let's talk about maximum decks offer a total of three different functions for finding maximum value max max a and max x here is the syntax for each function for max a we can give the column name that we are interested to find the maximum values or we can give two expressions where we want to find the larger value for max a it accepts only the column name for maxx which is an iterator function we have to give either a table name or a table expression so that iteration can happen in that table together with an expression that we want the function to calculate for each row in that table or table expression now let's see what data types are supported for each function we can use max or numbers dates and text also max consider blank values and it's assumed blank value as 0. for max a it can support numbers dates and logical values so rows that evaluate to true count as 1 and rules that evaluate to false count as 0 for max expansion empty cells are ignored for max a's it supports numbers dates and tags blank values are skipped and true or false values are not supported and the next thing to take note is that both these functions max a and mass x are not supported for use in directory mode when using calculated column or row level security rls rules now let's go to our demonstration file to see how we can use these three functions in our data analysis is my demonstration file let's first focus on the three cut visuals on the top so for the first one i use the maximum function on my discount amount column which is a numbers column so it returns me the maximum discount amount in my table so the second one i use it i use the same maximum function on my entry name column which is a text column so as this is the text column united states which has the highest value for alphabetical order is returned for the third one i use it on my date column which is the transaction state in my sales table if i use it on that column i get the last transaction date that i have in my data table which is 31st of december 2013. now let's move on to the next one this one is the maximum quantity between the two different expressions so this is just to demonstrate that you can use max based on two expressions so for my expression is my measure for the discard quantity and the second one is my measure for the sales quantity so among these two max will return me the larger value and the next two is for demonstration that max takes into account blank values so for the first one i use the max between number 1 and blank so max consider blank as 0 so it will return the values of 1. the next one i use it between -1 and blank max consider blank as 0 so maximum between -1 and 0 is 0 so it retains the length so these two is just to demonstrate that math function consider the blank values into account let's move on to the next one which is max a function for the first one i use the same formula with different function max a on the discount amount which is my number column it returns me the same value as before but for the task column which is my gantry name it returns me 0 so please take note that if you want the maximum value in a text function please use max max a cannot operate like this and the third one the same thing max a can be used on the gate column similar to the max function let's move on to the last one which is max a's for the first one i try to use it on my sales table and my expression is unit price into this quantity minus discount amount so i'm telling the max expansion that go to the sales table calculate this expression for each row in the sales table and returns me the largest value among them so i got this 235 by 4k for the maximum sales amount what if we use a table expression instead of a real table so i do it in my second cut value which is my maximum source for online channel in this i don't use any table name instead i use a table expression which is my filter function together with my related function so i try to filter out the soul table with the related value from the channel name which is online and then i try to calculate the same expression as before here in this formula i am asking the function to filter the sales table first according to the related channel name which is online only after that i ask the function to calculate the expression for each row in that filter table and finally return me the largest value among them so i got this value 127.32k which is the maximum sales value for my online channel so these are the examples we can use x max a and maxx in our data analysis the next one is finding the minimum values similarly we have three different expansions called mean mean a and minx and the syntax they all have similar characteristics to their max function so for mean we have to give either column name or two expressions for mean a we can give od the column and for mean x we can give the table altera expression together with a real context and the data types they can operate on they are all the same as the previous max functions so for mean a we can work it on numbers takes and text and the same it can consider the blank values for mean a it can work on numbers states and logical values for min x it can work on numbers takes and tags both min a and min x they ignore the blank values and these two functions they cannot be used in directory mode in a calculator column or with all level security rules as these three functions have similar characteristics as the max functions we won't do any demonstration for these three as you can see this is my sheet where i'm gonna demonstrate about all all selected and all except index functions before that here you can see i have two slices one is for the product and another is for the country there is a table as well where i'm gonna fetch all the measures so let's start creating our very first major the very first measure that we are gonna create is about the total sales so let me give it a name total sales that would be equal to sum of sales so this would give me the total sales now if i fetch this major in this table you will see it's giving me the total sales for all the products the second measure that i'm gonna create is all so i'll say sales in bracket all that all means is the filter that we are going to use in this one for that first we need to use the calculate which is going to evaluate an expression in the context of the modified filters so what we are going to modify here is the total sales the measure that we just created and you can just put the comma and directly apply that filter so all now you can see we can either give a table name or a column name so whichever is the your concern over here that we can use it for example in this table i'm gonna use the products so let's use a product column i'm not using the whole table i'm just using one column out of this whole table but if you want to use the whole table you can use it i'm gonna tell you what is the difference between the whole table using and using this one column so basically this all function it's gonna remove all the filters that are gonna be on this product column instead of that if we are going to use the whole table table name rather than the one particular column name that means it's going to remove all the filters that are going to apply on this table let's close this one and bring it over here so now you have noticed over here that it's giving me the same value for all the products there is a reason because it's calculating the total sales and removing all the filters on this product column that means it's not gonna consider any of these products over there while if i just select any of the products you will see none of the value is varying for the total sales one because it's constant there is no effect of any filter by the name of product column or there if i select all product and i select any of the country you will see the value is changing this is changing only because we use the all filter function removing the all the filters that are being applied on the product column not on the country or any other column i'll just quickly show you if i just use the table name not the any particular column name you will notice that no matter what i do suppose this is the total selected now either i unselect the country or the product sales all is going to be the same let's switch it back to the product column only for our further demonstration next is all selected so we are going to create for this one we have to first again use the calculate which is going to evaluate our expression we can directly use our measure which is sales total sales then we are going to use this filter all selected you can see in the description it's saying returns all the rows in a table or all the values in a column ignoring any filters that might have been applied in the inside the query that means it's going to ignore the filter which you have applied inside the query while keeping filters that are coming from outside the query so all selected it's again you gonna use either the column one particular column or the table so i'm going to use my product column again and close this now let's select it you can see it's giving you the total sales based on the all the selected values now if i just select all in this product table you will see the values of sales all and sales all selected is the same now we have all the values over here this none of the value has been filtered out let's see how it behaves if i just select canada let me just select the canada so you can see that it's giving you all the selected value sum it's considering products fine now it's only giving you this value for the canada this total sales is for the canada for all the products that means whatever values now it has been selected in the slicers or by filtering out it's only going to give the values for those only all selected you can also consider like all the selected values only for example now let's consider one another case if i just select umbrella so it's going to give you only the values of umbrella product for all the countries and in this case you will notice that total sales and all selected is same why reason is very simple because this total sales is for one product only umbrella and for all the countries so let's create a new measure again first we have to apply the calculate in order to evaluate our expression we are going to use total sales and let's apply this filter all accept you can see all accepts are going to return all the rows in the table except for those rows that are not affected by specified column filter it wants to convey the idea that it would clear all the filters except on one particular column on a table or a multiple columns which you are going to mention over here so what i'm going to mention over here i'm going to mention over here the product field so it would only consider the filter on product but if i'll apply any filter on countries on any other column it's not going to consider them it's gonna ignore them so let's first the table name financials which is my table and then the product is the column name now you have noticed this it's gonna calculate the values row by row by row all values are different as in total sales in total sales that was the case because we haven't apply any filter so it was considering all the filters that we have applied but over here right now all countries are selected and all products that's how it's screaming but what if i just going to remove any of the country let's see let's say only canada it's still the same only friends it's still the same only mexico is the same that means it's ignoring all other filters or the fields that we have applied in the filter except the product but while if i just you know going to select any product so it's going to give you only for this particular product it's going to consider those filters the reason is over here in our all except we said that all except remove all the filters except the one that we have applied over here suppose instead of product filter i'm going to apply any other filter let's consider the ear for example i'm gonna create one more filter by the name here and in this all except i'm gonna use the year column note the product now select all now i'm going to make it multi select all now guys if you have noticed if i'm selecting here value it's changing but now you can see it's a same value for this why the reason is that it's ignoring all the filters by the products they are the different filter and different products on my very first column under the product and since our filter is only gonna work on the ear because that's what we have provided in the all accept filter condition that's how it's going to give you the same value while if you will change the year it's only going to consider this one so let's do one more thing what i'm gonna do instead of just one column i'm gonna give it a one more column and in this time i'm gonna give it a product name as well so you can see that values are different if i uncheck any product it's gonna go it's gonna give values according to that see it's varying value now again and if i just uh change my filter over here it's again varying so that's how all except works all except simple says i'm gonna remove all the filters from that table except in the parenthesis the filter that you are gonna mention to consider in this case we consider only product and the year so that's how it's varying let's first discuss the definitions of all and remove filters all filter is gonna return you all the rows in a table or all the values in a column ignoring any filters that might have been applied this function is useful for clearing filters and creating calculations on all the rows in a table so many of the time you need to remove the filters from a table or from any column so that time you can use that similarly remove filters is also there which was introduced in 2019 after the all filter index it clears filters from the specified tables or columns but there are substantial difference between these two which i'm gonna let you know in a while now let's discuss about the syntax of these two filters as you can see on my screen for both of the filters syntaxes are the same except the name of the text function so for all it starts with the all then your table name or column name and then correspondingly other columns and same for the remove filters here you have to write first the remove filters and then your table name column and then so on so this is the way that you can write the syntax for these two filter functions now we're going to talk about the parameters in both of these filters so whenever you are working on these two filtered x functions then you have to always provide either the table or the column name table the table that you want to clear the filters on similarly the column the column that you want to clear the filters on so those are the parameters for both of these text functions now let's talk about the return value so whenever we are writing a text to create any measures in that case we always expect some value to come out of that measure so in case of all its gonna return you the table or the column with filters removed however in case of remove filters you won't get anything it's just gonna be there to remove the filters from the table or column but you cannot get anything in return now there are couple of things that you should remember while working on these two decks filter functions the very first is that whenever you are working on all this function is not used by itself but serves as an intermediate function that can be used to change the set of results over which some other calculations is performed by mean to say that you have to always use by giving some reference like the calculate and then you have to mention your measure or your expression then you have to write this all and then you can use it however in case of remove filters remove filters can only be used to clear filters but not to return a table now for all dex filter function you should always remember that the normal behavior of dex expression containing the all function is that any filter supplied will be ignored however there are some scenarios where this is not the case because of auto exist [Music] [Music] in case of remove filters you should always remember that this function is not spotted for use in direct query mode when used in calculated column or raw level security so guys these were the couple of things that you should remember whenever you are working on these two text filter functions and now we are gonna move towards demo session where i'm gonna let you know how you can use this and what is exactly the difference when you are gonna apply these dex functions into your measures while creating your beautiful power bi reports so let's head over to the demos part so guys as you can see this is my power bi file over here again over here i'm using my adventure works dw where i am using fact internet sales table as my fact table and then there are certain other dimensional tables such as dim product customer dim date dim currency team sales territory and team promotion i am going to use this model to create certain measures using these two text functions which are going to help you to understand how you can exactly use these two dex filter functions and also you can understand actually the difference between these two text functions so let me come over here into my report section and here the very first is my alt x function so what i have done i have created two measures here total sales and total sales all and plus you can see there are two slices one is for country another is for color if i'll drop it down you can see all the country names are over here and in the color all the colors names are over here these two are coming from the gym product and themselves territory table correspondingly as you can see color is coming from the dim product and country is coming from the dim cell strategy now on this page i have two slices and two cars where i have in the cars i have values of total sales and total sales solved so let me show you what i have done in these two majors so if i'll go in the total sales one in the total sales one is a very simple measure where i'm calculating the sum of the sales amount from the fact internet sales table nothing else and which is over here now the second one is using the all dex filter function over here what i have done i am using my total sales and then what i am doing i am removing the filter from the dim product table and the dim sales territory table why i am doing so so that my sales is not gonna affect it by these two slices which i have over here in my reporting page so let me show you how it actually works so let's suppose i select a country over here which is australia now you can clearly see that this total sales has been filtered out or has been sliced using my country filter similarly if i'll use a value from the color slicer then it's also gonna get further sliced down but you would notice there that these two slices over here are not affecting my right hand side card it's still the same value which was originally before applying these slices on the very first card so if i'm gonna again clear it out both of these slicers you will see both values are same so that's how we can apply the all next filter function to remove any filter on our measure that we are creating over here so if you are going to apply any of the filters then you can easily remove them using all so that is the first example now we would do some more deep dive into this one where i'm gonna show you how it actually affects the other values as well so suppose a scenario where you have to calculate the percentage of the sales for example i have my some colors and based on that i want to calculate the sales of each color in terms of percentage so for that what i need i need my sales of that particular color and then after that i'm gonna need the total sales so if i'm gonna divide the sales of that color by total sales i can get its percentage so that's what i have done over here so if you will see in my another example the very first table over here this is just gonna use my total sales measure so you can see i have here and then my colors which are on the top you can see in the columns and then i have total sales so it's gonna give me just total sales that's perfectly all right we are not gonna do anything now in the second one what i'm gonna do i'm over here using total sales but here i'm dividing it because it's the percentage of the colors so this is my color sales measure which i created over here what i'm doing over here i'm using some x it's just for the expression we can use it and if you don't know what is the difference between sum x and sum and all you can check out the other videos link are in the description section and also you can go to our youtube channel and there you can find it easily so what i'm doing over here i'm using my fact internet sales table and then i'm using sales amount from here for any particular color and then dividing by the same total sales amount however i am not removing the filters from that so there is a concept of condex transition so whenever i have suppose there are the colors over here black blue and any red etc on this table so whenever the sales would come over here it would automatically get soluted out based on these and this is the reason that over here my total sales which is over here it's also getting the same sales of that particular color in terms of the total sales it's not actually the total size it's being filtered out based on these columns and that's why you will see if i divide the same amount of sales by the total sales which is equals to the same sales then it's gonna be one and if it's one that means in percentage it's hundred percent so basically in another words if i'll say this total sales at the denominator is actually the same sales of that particular color and that's why it's coming as one or hundred percent so what is the solution around that so the solution is that we should remove those filters that means the context transitioning we have to remove from here and for that what i can do i created another measure and in this measure you can see clearly i'm using all on this fact internet sales table that means i'm instructing this expression over here that use this all dex filter function to remove all the filters on this table and then give me my total sales amount and that's what is happening over here you will see i'm using the measure over here which is my color sales percentage using all and here i'm getting the exactly what i'm expecting over here so guys whenever you want to get your total sales or whenever you want to remove other filters then you can use the all and that's how you can use in to get your desired results okay now i hope that all next filter function is clear to you now we are going to move forward towards our remove filters text function so similarly the two cards i created previously here i created the same and now instead of all i am using the remove filters and it's gonna work the similar way that means i'm removing the filter so this filter if i'm gonna apply over here like country or color they are not gonna affect my right hand side card you can see no matter what i apply it's gonna remain the same and let me show you the major oil this is my total says remove filters measure that i have created over here and here what i'm doing i'm using remove filters dex function on dim product and dim cell strategy table like previously i was using all instead of remove filters but this is the one part what if you will ask me that oh why we are not using in the previous calculation remove filters so let's see what happens so this is my color sales percentage using all and here i'm using all but just try yourself if you would like to use over here the remove filters whether it's gonna work or not so let me just try over here you will see as soon as i just try i cannot get any remove filters text function over here so this is what i was discussing previously in the definition part that you cannot use like this here only you can use all you cannot use the remove filter sedex function over here on your screen you can see that i am using a website desktop too so if you are also one of the person who really wants to learn dax that you can use this website not only that there's another website desk.guide i have already explained you in my previous videos if you really want to learn decks or you want to try them out then please go to these two websites where you can get the complete information about the text functions and also over here on desktop2 you can try it yourself you should note that to add a calculation to your query like measures and variables that you generally do you would use the optional defined keyword at the start of the query like i have done over here so i have defined it that my misery is going to be sales customers or here this is my measure name then it's going to count the total number of rows from the customer table before going into this measure let's first see our data model so that you can have a better understanding how it's working over here you can see that i have a customer table i have a sales table and i have a product table so product two sales there is a one-to-many relationship similarly from my customer to sales there is a one-to-many relationship so that's how you can see your model now if i have to filter it out my product that means maybe there is a brand or a product and i want to propagate my folder to the customer table then definitely there should be a relationship exist between these two tables through our sales table and if there is a no relationship between for example customer in the sales table then no matter what i do calculation it's not gonna affect the total number of customers so that's what we are gonna learn over here so in order to make it both directional or one way or maybe you want to define no relationship between them in those kind of scenarios we can use the cross filter index function so now coming back to my code over here initially what i'm doing i'm defining one measure which is going to calculate the total number of customers from the customer table once it's done now the second part is where i'm gonna define a relationship between the sales and the customer table through the customer key in this major you can clearly see that i'm applying cross filter next function that is gonna define first the relationship between the sales and the customer table using customer key and it's a both directional remember it it's a both directional in the expression part we are defining what kind of relationship we are looking into over here secondly i'm using calculate because my core host filter is a calculate modifier it's gonna change my calculate functions calculation now in the another part what i want i don't want to filter my sales and product table that means over here my relationship is none so let's see how it's going to behave then and at last i am going to evaluate it by summarizing everything into a table where i am taking the brands from the product table total customers are coming from this one which is going to be my customers major then i have customers filter this measure that i have defined over here and at last there is a products do not filter which is this major and so now i'm going to run this and we are going to have a look at the calculations i can expand this window and here are all the calculations so this is the brand which is coming from the product table then there are the total number of customers which is this one and there's no relationship or nothing has been defined over here then the other customer says that means in my model i'm applying both the directional relationship between the products and the customers through the sales table now over here you can see that because of the both directional relationship that we applied over here between the sales and customers it's filtering for each brand so like for example contoso here you are seeing customer sales that means customers in the sales table relationship is working as well as it's working with the product however at the last part you see we have broke down that relationship that means we said like there should be no relationship between the product and sales table and that's why over here you are getting the total number of customers which are not being filtered so over here we are just calculating the total number of customers only without any sales or anything so that's how you can evaluate your dex expression in the desktop and you can see how your output is coming out so now i'm sure you know how to apply this cross filtered x function into your calculations while working on it please always remember while working with cross filtered x function in the case of 101 relationship there is no difference between the one and both direction secondly cross filter can only be used in functions that take a filter as in arguments for example your calculated x function thirdly cross filter uses existing relationship in the model identifying relationships by their ending point columns if there is no relationship in the model it's not going to work number fourth in cross filter the cross filtering setting of a relationship is not important that is whether the relationship is set to filter one or both directions in the model does not affect the usage of the function this means cross filter will override any existing cross filtering settings over there furthermore an error is returned if any of the column named as an argument is not a part of a relationship or the argument belong to a different relationship also you should remember over here if calculate expressions are nested and more than one calculate expression contain a cross filter function then the inner most cross filter is the one that prevails in case of and conflict or ambiguity and lastly as most of our other dex functions this function is not spotted for use in the directory mode when used in calculated columns or row level security rules so this is my power bi file where we are going to calculate the different measures using key filters and filters and then we are gonna see where we are gonna need the key filters before proceeding further i would like to show you this is my uh data model where i am working currently with the help of this model i am going to depict you how to use it now going further today we are going to talk about the key filters so i would like to tell you k filter is only necessary when you have arbitrarily shaped filters that you do not want to lose by applying additional filters on individual items by this i mean to say that whenever you have one or more filters on a column and you want to apply some more filters but you don't want to lose the previous filters so in that case we are going to use t filters so now let's start with the with our very first expressions where we are going to calculate total sales where the product color is black so what i'm gonna write i'm gonna create a new measure black color sales which is and then total sales because i have already total sales which is a major so in this case let's start with first product color equals to black so let me drag in this table and here you can see that so this code is actually equivalent to this one black color is all i'm just giving it a name so please don't mind it is exactly working like so just hit the enter and now let me grab it over there so you would see both of these are equivalent they are giving you exactly the same results and this is the reason that the very first measure that i created black color says it was giving me values for each and every color the same value it is giving you the value for one particular filter that applied but is it it is giving for all because it's ignoring all other filters that have been applied but what we can do now now suppose we just need value for this one particular color black that means i don't want two other values to appear over here like in other columns instead of in in case of blue it's again giving me the same same i don't want this i just want my total sales of black color and in front of that black color only so what i can do for that so for that we can use this key filter so how to use that there are two ways either we can use the values function over here values text function instead of all or we can use the key filter so let's try one by one let's say first with values that is i need to again copy this one now instead of all i'm gonna use values click ok and close and now let's see how it is so you can see that clearly now it's no putting values in each and every cell so that's how we can get it and it's equivalent to the key filters that i already explained so why to use it well the semantic of the filters removes any existing filter applied to the same column replacing it with the new filter defined in the predicate condition if you do not want to lose the existing filter we are gonna replace with all with the values as i just showed you so now let's use the key filters as well in this one and we'll see how it varies so what i'm gonna do now i'm gonna create again a new measure so if i'm gonna keep it over there now you can see it's exactly working as a just calculated previous one once you specify over here key filters so in this case it's also gonna keep other filters and the filter that you have applied it's not going to remove all the existing filter now there's one more thing whenever we apply key filters so you should always note that the key filter functions this allows you to modify this behavior that means the behavior of calculate function when you use key filters any existing filter in the current context are compared with the columns in the filter argument and the intersection of those arguments is used as the context of evaluating the expression that means suppose we have two or more filters applied on the same column in that case the intersection of those filters is gonna come out so how to check that so for that we have to consider one more example where we have to apply more than one filter and then we'll see how it works so i have again the same table with my uh one two columns only right now in my table which is color and sales so now we are gonna see like how this uh key filters works in different scenarios i'm gonna create a new measure now in this case what i'll say i'm gonna take an example and now i have to apply my filters so i have to apply first two filters that is in the or condition so how can i apply those or i'll say blue and again closes so yeah it's gonna say when my product color is black or blue it's gonna give me the total values so you can see that when my color is black or blue my total cells is this now i'm just gonna check the sales for black one which we already calculated so let me check black one two and convert it into a card it's 27.9 million let me place it over there so please be with me because it's a bit longer example and you need to understand what is exactly the difference first part we have already seen how actually the key filters work but here i'm trying to make you understand how we can achieve the intersection of multiple filters when we apply over there so another one i'm gonna use the same but in this case what i'm gonna do i'm gonna just calculate the another color as well with this one so here i have black and blue but i'm also going to calculate the black and red so here you observe we have three different cards with the three different values till now and also right now our values are not matching three different values we are getting from these three different measures now what i'm gonna do i'm gonna apply simultaneously two filters in one calculation that means this black or blue and black and red i'm gonna apply in one measure so how to achieve that now my first filter what uh what it's saying it's gonna calculate this one but on this calculate i'm gonna apply one more calculation which is gonna say calculate and in this calculate i'm gonna do one more calculation so that means we are applying here two filters so let's see what is the result of this one so let me get it over there so here if you have noticed that the total sales amount for black and blue and black blue or red is same now you are wondering how it is happening so let me recap you when we calculated for black or blue which is over here we just apply all filter over there and it was calculating correctly however when you apply multiple filters like in this case first you have calculate and then again you are calculating something so what it's doing it's overriding this context that means the outer calculate expression includes a filter for our black and red color so the default behavior of calculate overrides the outer filters that we have applied over here this is the reason that it's gonna give you the same value as the black or blue rather than it's gonna give you the values for black or red so always you have to be very careful while you are applying these filters now the last point that i really want to discuss that how can we use the key filters and how this intersection really works so i'm just gonna use this same and in this one i'm gonna mention you how to use that so for that i'm not gonna do much coding over here instead of that i'm just applying over here paste it over there and say key filters and here in this context i have to just say keep my these filters over here we have to say k filters and then we need to close the bracket let me one more and here we go so now it would check the intersection of the two filters between inner and outer ones so we can clearly see that black is the common one so it should calculate the value exactly for the black color here because and you can see clearly it is calculating exactly the same value as for black color sales so guys whenever we are applying the key filters it modifies the default behavior of calculate and adds an additional filter because the intersection of filters is used and due to that now we have to get uh the intersection of these two filters and over here the black line is a common one so that's how it's only gonna calculate the value for this applied expression that means the total is for the black color product as you can see on my screen look of valued x functions returns the value for the row that meets all criteria specified by one or more search conditions if we talk about the syntax of this text function we have to mention first the lookup value index function after that the column that we are looking for or the values that we are looking for then we have to mention search column name which is the criteria by which we have to search and third would be the search value that can be a column or it can be a particular value also we can provide the alternate result as well for example if it's not giving or returning any value then what should it return that's what we can specify in alternate result criteria now let's see the model so over here first i have my employee table and the master table there is no relationship between these two if we talk about the data inside this table employee table has employee name company name position name state and my master table this has the state and the country what i want to do i want to create a new column in the employee table in which it can return the new column where i can have corresponding to every state i have the country names so what what i can do i click over here create a new column which is my calculated column and i give it a name country so first as per the syntax we have to use the lookup value index function name and now the result column name that means the result that we are looking for so we are over over here looking for the country name from the master table so the very first is mine this one now the search by criteria so over here we are looking for search by state which is my again the master table state name and now the search value the values that we are looking for since we are looking for the states in employee table so we have to mention this again the states in the employee table as i mentioned it can be one particular value or it can be a column so over here we are using the column and just hit the enter as soon as you will hit the enter you will see corresponding to every state country names are populating that's how we can use local value function in order to create a column over here now this was the one case another case is for example i have my another model where i have a fact internet sales table and if i'll go in this table so in this table we have the customer keys and also correspondingly we have the sales order number as well as the order quantity and some other columns as well so what i'm looking over here i want to find out the sales order number corresponding to one particular customer key for this i can go in my key measure table and i can create a new measure which would be my sales order number customer key and here again i'm going to use my lookup value dex function where first what i'm trying to find over here i'm looking for sales order number from the fact table so let's mention over here now it's going to be search by criteria over here so my search by criteria is customer key from the fact table and now the value that i'm looking for so my value is 14870 and the alternate result would be any that means not available just hit the enter now let me drag it over here on my card and you can see it's going to return me a value as of 5190 so let me go back again and i can clearly see that corresponding to customer key 14870 my sales order number is so 51900 which is exactly the same that i'm getting over here that's how we can use lookup value function in two different ways maybe there can be more cases for example when we are looking for exchange rates corresponding to one particular currency and all we can find it out and there can be multiple use cases more i would also like to highlight couple of points if there is a relationship between the result and search table in most cases using related function instead of lookup value is more efficient and provides better performance secondly the search value and alternate result parameters are evaluated before the function iterates through the rows of the search table and lastly this function is not supported for use in direct query mode when used in calculated column or row level security rules as you can see on my screen top end function returns the top end roles of the specified table the syntax of top-end function is and value that means the first parameter will determine the how many top rows you want to return from this one it's a singular value a singular value will be written where the expressions will evaluate multiple rows the second expression or the second parameter over here is the table on which table you want to apply it so this is the table from where you would like to evaluate values thirdly the order by expression this may consist of any dex expression by which the result of the expression will sort and evaluate each row of the table and lastly it will sort your expression by ascending or descending order so that's the topic function now let's move on the demo over here you can see that i have already created three measures which is the total sales total cost and total profit before beginning of this demo part i would like to introduce you with my data model which i have explained in my previous videos as well so on the top you can see that i have dimensional tables where one is dateable another is product then i have a customer table territories table which is connected to the hemisphere bond and then i have my sales table so these are the tables that i'm using sales table is not affectable while the others are my dimensional table so here connected in the star schema as i explained to you earlier i have three measures and there is a table where i am gonna put my values after connecting with that but initially i have just dropped over here the year column from my date table as you can see is over here before proceeding further let me show you what i have done for these three different measures which are my total sales total cost and total profit you can see my total cost is the sum of the total product post i'm using is blank because if there are certain null values in in my sales table so it's going to return me 0 otherwise it's going to give me the aggregated sum after applying the aggregated sum function over there it's gonna give me the total value or the total cost similarly i have done for the profit profit is basically total sales minus total cost and total sales is the sum of all the sales amount so i believe these are the quite simple one i need not to explain much away so i'll straightforward jump on the top top function so what i'm gonna do over here the very first we are going to create top 10 for the total sales so how we can create it the very first you have to create a new measure over here once you will create you can give it a name top 10 sales or not top 10 because i don't think so we have so many values in my data so i'll go for top five total sales now we have to write our expression since we are calculating a measure and it would be definitely a single singular value so we have to apply the calculate function otherwise i'll show you the top end basically returns a table it doesn't return a single value so in order to convert into a singular or single value we have to apply the calculate function so let me apply this calculate and over here it's gonna be my total sales and then i can apply my top end function over here so top end here you can see that it returns a given number of top rows according to a specified expression so we'll write this now we have to write n value means how many top rows do you want to return so in our case we have the five rows that we want to return and then i have to mention the table name table name would be the sales table because we are working on the total sales amount or the total sales and after that it's gonna ask you the order by expression so what would be the order so we want to order it by total sales so let's put this one and descending order let me go through this once again for this formula so you can see that i am returning top 5 total sales where i am calculating my total sales and getting top n that means the n value is over here 5 returning from the sales table then order by expression is also the total sales you can change the order by like by profit or by cost the way you want and then it's in the descending order that's it just close it hit ok and now i'm gonna drag and drop it over here [Music] so you can see over here in my table for the year i'm getting the top five values of the total sales now you will say okay there are more than five values are paying definitely there more than five values are being reason being force you are getting the distinct one and the fifth one is zero so these values are the blank values which i replaced in my total sales as the zero and it's also considered as one value and all are the same so it's gonna return it as next value so now you can even do this sorting over here if you want to similarly what i can do i can create two more for you so that you can understand [Music] [Music] so these are my top five profits once more we will calculate total cost as well so top five cost so what we can do we'll gonna create a new major and then i'm gonna use a sales value and this time i'm going to make a little bit change the expression that i have to use i'm gonna use the total sales i don't want my total profit or total cost i want by total sales and descending and then just hit the enter button it's gonna take automatically and place it over here so these are the top five total cost by sales now this is a simple calculation and as i explained you earlier top end actually returns a table it does not return you a single value so how we can see that if you just want to work on a table and you need a table so what you can do you can come over here you can create a new table and just say top five sales from the sales table and here we we can write an expression so we have to write off top end then we need top 5 so 5 table name sales table and the expression would be by total sales or you can write even using some some x function you want to write it you can write that as well if you wish to and descending and just hit the enter button and you will see you have all those rows which has the top five sets now what we are going to do we are gonna play with this a bit more and now we want to make it dynamic we don't want to calculate always all the values like i have to put the values in my all the text measures like top 5 top 10 and something something i want to do it dynamically [Music] first i need to create a table so let me go there and create my new table [Music] in the home tab you will enter the data or you can load it from the excel if you want to i'm gonna give it a name probe n here value that you need to give so let's say i give to 5 top 10 then say 20 30 [Music] 50. so these are the different values that we want to give dynamically so load this table so this is our top and table where i have the different values now what i can do i have to create a new measure open this and create here a measure i'm calling it selected value here what i'm gonna do i'm gonna get the selected value so that i can use these values from the top end table in a slicer to dynamically change and here i'm gonna give this value so whenever i select any value it's gonna select that particular value so let me bring a slicer over here [Music] so currently in my these measures that i have created over here top 5.5.5 they all are using this five as the hard coded one but i don't want to use this hard body instead of that in the same i'm gonna replace it with the selected value same for this one [Music] so this would get automatically changed so i'm not gonna change in this one but let's see we have to change in this one too so let's say it's gonna be now top n and again this would be my selected value measure that i just created again [Music] so i have to change in two so let me change in this one too and let's make it time to make it select it value one last thing there is one more remaining over here which i just need to rename it to n because in this one also i have used my selected value so that's all we need to do now according to this whenever we will change this it's going to select the value but since i don't have the sufficient data for top 30 top 10 top and numbers of values it's not there but this is the way we can make it dynamically so whenever we will select any values from here this value is gonna go into your expression over here in the selected value which was earlier a hardcoded value and according to that you can make it diamond i believe it's gonna be very useful for you whenever you are performing calculations though and if you are if you will ask me there is an inbuilt uh function as well which you can use top n and then you can drag any field from there then you can use that as well but there are certain cases where we don't want to use this topic and we have to use in the calculations our inbuilt dex function itself so in that case that would be very helpful in power bi decks offer a total of eight different clock functions that we can choose from for different scenarios so in this video we will explore all of them for better understanding we will group the eight different types of count into three groups in the first group we have count count a count blank and count rules let's look at the first two functions count and count e both these functions have the same context we have to give the name of the column that we want to count to these functions the difference between the two is that count mention can work on numbers dates and strings but count a can work on one more data type which is boolean so that means count a can count the true or false values both functions skip null or blank values if they are present in the column but they both count the empty strings values so the question here is what's the difference between the null or black values and the empty strings so to get the idea of what's the difference between these two let's look at the demonstration file so this is my demonstration file for this tutorial so let's look at my geography table under the city column if i just uh open it up expand this we can see that we have two different values on the top the first one is blank and the second one is uh it doesn't give you anything that means the first one is our null or blank values the second one is our empty strings so if you want to know these two how many rows we have here i can just select the blank values and i can filter it out so it's shown here that i have four or three now values in my table in this column and what about the second one i have two dot of 22 empty string rules in this city column so for both account and count a they will count this empty string values but they will skip the first one which is length values so let's go back to my report view i have already calculated my count ct which is the count of my ct column and count a which is the count a of my ct column the same so if i just drag and drop onto my report canvas they will give me the same values because this column is just test column so they will just give me the same values so the count and count a they can be used interchangeably if the data types are numbered dates or string values let's move on to our next two functions which are count length and cub rules so for the first fraction count rules we have to give the table name because this function specifically counts the total number of rows in the table so if we want to know how many recalls we have in a specific table we can use count rules instead of count functions and the next one count blank it counts the null blank or empty string values in a specific column if we go back to our demonstration file i have added these two which is the count blank of the city column and another one is the account rule of my geography table if i drag it and edit my two cards here i have two dollars 425 blank values plus empty string values in my city column and a total of 68 000 records in my geography table the next useful scenario where we can use the code rules function is in a row context let's say i have these two tables manufacturers and products so these two tables are joined by one too many so if i want to know how many products a manufacturer have i can use these count rules machines together with my related table so this is my formula for counting how many products a manufacturer have so let's add a table visual for this so we can easily see how many products does a manufacturer produce in our second group we have count x and k expansions as these four functions end in egg that means both of them are iterator functions so we have to give the name of the table that we want the iterations to go on and an expression to calculate on each row of the table so if the expression evaluates a final value of non-blank values those values are counted and returned by these functions so the syntax for these two are the same we have to give the table name and an expression that we want to check and regarding the data types these two are similar to their original count and count eventions so count x can work on numbers states and strings where counted x can work them as well as on logical values so let's go to our demonstration file and check how to use this so here in my data files i have relationship between manufacturer and products which is one-to-many relationship so this time let's say i want to know the number of manufacturers who produce a specific type of product categories so i have four types of product category down there so let's say i am interested in the youth product category so i want to know how many manufacturers produce different products and the youth category in this case i have created my measure like this so i use count x in this case because my the column that i'm interested in is not logical values data type so for my table this is my table that i give to the account expansion so i take the related table from the products and i filter it according to the product category which is my interested category so this is my table and the second part my expression is this so my expression is whenever the product has the manufacturer id i will count the product so if i add a table fish wall once i add this so the manufacturer name is filter accordingly to how many youth categories they have among my 14 total manufacturers i only have six manufacturers who produce products and a youth category in our last group we have distinct count and distinct code no blank so it's simple distinct count they count the number of distinct values in a column but they include the blank values as one record but for distinct node blank is do not count the blank values so that's the only difference between these two so the syntax is the same we have to give the column that we are interested in and distinct count they include the blank values as one record and this thing got no blend they totally skip the length values but the thing is all of them they count the empty string value as one record so let's look at our example so here in my demonstration file i have added two distinct count so the first one is my distinct count city based on the city column in geography table and the second one is my distinct count no blank based on the city column again and the geography table so as we have seen earlier in our city column we have both blank values and empty string values so for distinct count we have 29350 values but for distinct cut no blank the blank value is ignored so we have only 29349 but the thing is both of them count the empty string values so to double check this one i have added my slicer of city on my right hand side so the first one is my blank values if i click on it this will return as one so that means distinct count include the blank values but for distance no blanks it doesn't count the blank values but for empty string if i click on it both of them give me as one record so both these functions consider empty string values as one record now let's look at some points that we have to know before we use confessions so if the function finds no rules to count it will return a blank but if there are rows to count and none of them meet the specified criteria or expression the function will return a zero all of the carlton functions total eight of them are not supported for use in direct query mode when used in a calculated column or row level security rules right now on my screen you can see what is the definition of these two dex functions and what are these two dex functions that means in this video we are gonna discuss about distinct and values x function these two dex functions are similar except one close in case of distinct you can only get the distinct values but you cannot count any blank rows if it's there in a column or a table however with the help of values.x function you can count the blank row as well so let's see how to do that if you are using desktop 2 then you can also use the same code because this code is also available on desktop guide website these two websites are going to help you very much if you would like to learn the decks so this is the code and if you would run this code then you would realize that at the bottom you would see the results so let me just fetch the result over here here you can clearly see that i have my store continent column that means i'm using a table that's name is store over here you can see this is my table store and over here i have different continents north america europe and asia and there is a blank row why this is the blank row because there is one to many relationship if you would like to check the data model as well you can just click over here and you can see the diagram over here you would see your store table this is your store table this is your sales table and there is one too many relationship now let me close it over here over here the main difference is this one that means no blank row is coming with the help of distinct dax function that means distinct dex function is always going to return you the unique values but it won't return any of the count of the rows that are blank however in case of values it's going to count the blank rows as well that you can see in the last row in the last row when there is a blank one then there is no count over here for the distinct index function however in case of values text function it's going to be count as 1 so that's the only difference between these two dex functions which you can use into different scenarios while you are working in your power way dashboard and reports and you are creating the different measures now let's consider the another scenario in the another scenario what we are going to do we are going to execute this code and with the help of this code we would try to understand how it's going to perform on tables that means we can also perform distinct and values dex function on the tables and over here let me execute this code as soon as i'm gonna execute this code you would realize that again it's going to count the number of blank rows in the last part what we are going to do we are going to use these two dex functions in the variables remember that you can use distinct index function in the variable but you cannot use the values and why is that so because whenever you would try to execute as a variable the distinct index function it's going to return you the error so let me try to execute this code as soon as i'm gonna execute this code here you would get this error the values function expects a column reference expression or a table reference expression for an argument so this code definitely is not going to work so what i can do i'm just going to comment it out let me remove this one and here we'll see how it's going to work so let me run it again so over here you can see that when i'm using only count rows it's going to give me the total number of rows from the table and it's counting the duplicate rows as well however as soon as i apply distinct function which is going to return the unique rows from a table without any blanks so over here you can see there are only three rows why because there are only three containers that we are talking about here on the first column you can see that i am selecting columns the continent and in my first scenario you would notice over here there are only three continents that's why it's going to return only three rows however since my store table has many duplicate values of the continents that's why over here you would see it's going to count all the duplicate rules and that's why it's returning a value of 302 as you know that in our power bi reports on day-to-day basis and or even any analytical reports we need to use certain measures we have to create certain measures and also in order to create those measures we have to use certain text functions so in this video what i'm gonna do i'm gonna let you know how you can create measures for mtd qtd and ytd and how you can use them you should remember that reports are integral to measure the performance of any digital marketing strategy they help in identifying actionable steps to meet your business goals well-built reports unveil new pockets for growth and help you make bolder decisions and take advantage of new opportunities so the very first we are going to discuss over here is mtd what is mtd month to date is a period that starts from the beginning of a current calendar month and ends at the current date but it does not include the date of the present day this is because the end of the business for that current day has not yet occurred suppose if the present day is the 19th september then your mtd will cover the data from the time period of 1st september to 18th of september only mtd report provides you the information regarding your campaign for that particular period of time the report helps in comparing the campaign's current period's performance to that of past periods mtd can help you identify trends and predict future behavior now there are different metrics and key performance indexes to measure and compare so plotting and reviewing the data on the mtd scale can help you identify problems with real time data now i am sure you know everything about mtd that you need to know and if you have still any question regarding mtd you can let me know the second one comes the qtd what is qtd well guys qtd or quarter to date is a period that starts at the beginning of the current quarter and ends at the current date be mindful while making qtd reports as a quarter to date is used in many contexts which is the beginning of the calendar of fiscal quarter qtd is like a financial tool that captures all the activities of the company that had occurred from the start of the quarter up to the date at which the activities were collected during that period its information is collected in order to allow management to see the quarterly performance and also gives you an overall view of the quarter shapes up however you should remember about some of the facts about qtd the very first is qtd is a three month period during a financial year hence there are four quarters in each fiscal year also qtd analysis is always useful later in the quarter when there is a substantial data to analyze so now you would be wondering why qtd matters a lot well or here i would like to tell you quarter to date comparison is quite useful as you can look for trends and can measure the performance in the context of finance quarter to date provides financial statements including details of the performance of a business moreover it highlights all the activities that are undertaken by the company from the start date of the quarter to end up to which the information was gathered hence it informs the corporate decision makers about the progress in the business in that particular quarter that's why it is very important for you to know about the qtd finally guys we are going to discuss about ytd that means year to date so what is it year to date is a period beginning from the first day of the current year either its calendar year or fiscal year continuing up to the current day a calendar year is a period from january 1st to 31st december where the fiscal year is a time period lasting for year but beginning from january 1st is not necessary the information gathered from white it is quite useful for analyzing the improvement in the business campaign the performance data analyzing business trends and determining investment returns etc here there are couple of facts that you should remember the very first is in some organization fiscal year may begin on any other day than january 1st for example in india is generally from the 1st of april and in many countries it starts from the 1st of july the 2nd one is that ytd analysis is beneficial for the managers to review the current campaign performance in comparison to previous campaign performance so this was all about mtd qtd and ytd now we are gonna have a look at our data model with the help of which i'm gonna create these three measures so let's have a look over here you will see i have right now three table sales table is the hidden table but majorly i'm gonna work on this customer table and date table as i always suggest you please don't forget to mark date table and for that what you can do you can come over here you can just select your table that is your date table and select it then you will see there is an option is marked as state table so please mark it as a date table whichever column you are going to use for date otherwise these time intelligence functions that is total mtd total qtd and total ytd are not gonna fork into your power bi report secondly you have to create you have to create certain measures using these and those are very simple measures that i have already created over here so the very first is our total income so now you can see that i'm just using some dex function on my yearly income column and i'm creating that similarly i'm gonna create three more measures over here that is my mtd income qtd income and ytd income so how i'm doing that so let me just show you so guys this is my mtd income over here you can clearly see that i'm using total mtd dex function i have used it in many previous videos as well if you haven't watched them please go and watch them so there you have to use first total mtd which is your dex function then your another measure or you can even write an expression over here and then your date column that's it and for the others as well you have to just change it to the qtd and ytd correspondingly and then the last one is our ytd one so if i'll go over here ytd income you will see there is a total ytt over here which is appearing clearly that's all you need to do in order to create these three measures basically we are using time intelligence based tax functions that is total mtd total ytt and total qtd over here then you have to use your expression or if you have already a measure then also you can use inside them and then your date column so now let's check how does it appear actually if we are going to create a matrix or a report so in my demo i have already have this year slicer then i have my total income which is my column then i have my mtd then my qtd and ytd so over here if i'll go you will see that the first month is over here that is the march so in march my total is just 70 so my mtd is coming is total 70 only the sum starting from that date till the present day it's going to be like that so it's going to go row by row so i over here the context is row similarly ytd is going to show you the total ytd for this year so similarly white area is also going to be like that so last ytd over here in this column should be this 880 so let's go down and over here we will check this last some amount is 880. so that means for the starting of that year till the last day of that year that's how it's gonna count so guys this is very important for you to calculate and to know how to calculate mtdqtd and ytd so guys similarly you can calculate mtd qtd and ytd for any year and in large if we talk about the summary so mtd is a period that starts from the beginning of the current month and ends at the current date but it does not include the day of present day if we talk about the qtd quarter to date is a period starting at the beginning of the current quarter and ends at the current date its information is collected to see the quarterly performance and ytd which is year to date is a period beginning from first day of the current year either calendar year of fiscal year continuing up to current day so these are the three very important concept that you should need to know if you are going to work on analytical reports whether you are working on power bi or any other data visualization tool so as you can see on my screen over here i have uh written down some of the information that is gonna be very useful when you are referring to the related table text function i'm not gonna write over here for the related because i have already discussed in my previous video what is related x function its syntax and its parameter as well and what what is the value that it's gonna return so basically it's gonna return a scalar value the functions related and related table are typically used in calculated columns to reference relevant records in other tables although they can be used in measures too we can take advantage of related and related table text functions which can use the row context when to use the related and related functions you need to understand what each one returns so as i mentioned earlier the related index function would returns a single value from another table and we talk about the related table it's gonna returns the related tables filtered so that it only includes the related rows so one function is gonna return the single value and another function is going to return us a table you should also remember over here that the relationship between tables in power bi are normally of the type one too many so if i'm gonna move over my mouse over my data model over here you will see on the fact table sales table over here is my fact table and over here all the relationships are in many if i zoom it or a bit and i'm gonna show you that all these stars star means these have the relationships of the many side however if i'll see on my dimensional table over here these above tables are my dimensional tables if i'll zoom out a bit so product customer directories and even my date table all are my dimensional tables and they have the relationships as one on the one side as a part of best practice you should always ensure that in your model you have one too many relationships one side would be the dimensional side and many side would be your fact side the two related functions allow you to refer to columns in another connected table so when you think about it if you want to add a custom column in a table on the one side of a relationship that is added a new column in a lookup table then it is highly likely that there will be multiple rows on the many side of the relationship so you can see that if i talk about the product one product has a many number of rows in my sales table because there can be multiple transactions for that one particular product so when writing a formula in a calculated column on a lookup table you must use the related table because there are multiple values are coming on so whenever we are going to uh use the related table function that should be used in the dimensional tables where you have one value because one value is gonna fetch the multiple values and multiple values can only come in your table so related table always gonna give us a multiple values the related table dex function it evaluates a table expression in a context modified by the given filter its syntax is very simple it's just the related table and the table name where table name is the name of an existing table using standard x it cannot be an expression this is my model and in this model i have one table territories and another is hemisphere and there is a relationship between the territories table and hemisphere which is one to many again and between sales table and the title table it's also one-to-many relationship now if i show you the data between these two table criteria tables has the keys column then region country and group however if i talk about my hemisphere table it has only group and hemisphere there's no more values what we will try to do over here we will try to use the related function because related functions is going to return as the single value and what we will try to find over here will trying to find the hemisphere for all the groups over here because we have a hemisphere table in our data model and now we want to return the value and in this case we need a single value so if we need a single value what we can do we'll go over here we'll create a new column and this new column named like hemisphere and also if i'll just go once more back into my data model and i as i told you earlier because once we are gonna use the related function related function is always gonna use at the many side of the table over here in the territories table because here you will see this relationship between the territory and my hemisphere table and you will find over here that these two table these tables have a one-to-many relationship and many is from the territory side so always always keep this thing in your mind that whenever you have to use the related function related function should be on the many side of the relationship in your data model reason being why it should be there because many relationships or the many values in the many side of this table can only have a single value in the one side of the relationship for example over here you will see in my table i have many values for the north america is repeating over multiple times but it has only one value in my hemisphere table so that's why we can use over here related function in the comment section in my previous video one of you have asked me this question like at which side i should use the related or which side of the relationship i should go for the related table so this is your answer whenever you are trying to use related function it should be only used in the many side of the relationship and related table always at the one side of the relationship one side of the relationship can have many value corresponding to that and that can only be accommodate in a table table can have the multiple values so we can accommodate using the related table however inside of many side we have only one value related to the one side of the table over here this is my many side of the table because it's my territories and here i can see in the group column there are many values for the north america and corresponding to these values only there is a one value so we need to use over here the related function only we'll go over there we'll put related and then we have to just give our column name what we are going to use over here and hit enter now you will see it has populated all the hemisphere for this particular column now we have seen how the related function is working over here the second question comes how the related table function is gonna work the related table text function and it should be always at the one side of the relationship and it has to calculate the many rows for example now you will see this product table product table has a one-sided and the sales table has many sites and these are related using product key what i'm going to do i'm gonna create a calculated column in my product table and generally i don't recommend that you do this like creating a calculated column using the related table because you can do it in a measure as well but there may be a valid reason to do it in some cases for example what we are going to do over here i'm going in my product table in my product table i'm gonna create a calculated column which i'm gonna say for each of the product how many times that product has been repeated in my sales table i want to calculate the number of rows now we are at the one side of the relationship not the many sides so remember this and if we are at the one side of the relationship we should always use only related table dex function that's the only function is going to work over here so related table now which table only the table name you have to mention over here and here our table is sales table and also you can see the text over here related table function returns the related tables filtered so that it only includes the related roles and we have already a relationship so these functions are built in functions which can automatically detect the relationship and based on that they will give you the table but over here we don't need the table exactly what we need we have to count the number of rows one product have been repeated how many times so for that we will use count rows function this would convert this table or basically this would just extract the number of times that rows has been repeated in that particular table and hit the enter button once you will hit enter button you see oh my god there's nothing nothing is appearing over here how is that happening but don't worry there can be certain products which are available right now in the product table but there are no corresponding rows in the sales table so once i'm gonna go over here and i filter i see oh yeah there are rows they are blank and there are other rows as well so i'll just filter out the blanks in in order to just to show you and over here you will notice okay 386 363 and there are so many other rows over here so this is the functionality of the related table text function you do not need to use calculate with related table to force context transition and convert the rows context to a filter context related table will work on its own so that is the main difference between the related and related table index functions there can be so many other cases where you can use it but that depends on the your requirements or what you are trying to achieve over there very first we have to create two new measures using our sum function and distinct count let me show you how to do that create a new measure with the name sales total and we are gonna calculate sum and choose the sales column over here in my data that's it we have calculated the sales total now another measure that i want to create for the order quantity so for that i'll do order quantity order total let's say and over here distinct count over here either you have to use the count or distinct count so distinct count is going to give the distinct count of the number of values are present in that particular column so i have a column order number so this would give me the total order quantity now we have to calculate the running total as one in order to calculate the running total we have to calculate first a variable for the filterization we have to filter the data and then we have to use the same formula as we used for the order total first of all we have to create another measure running order order total for that we have to create a new variable where which i am going to call filter period that means we are going to create over here a filter based on the order date so for that i am going to use the filter function which is used to filter the data first we need to use the table and the expression after that one so let me get the table name sales order because my order date is in this table so i'm gonna use the sales order and since i have to remove all the filters so i'm gonna say all selected if there is a filter that i'm applying manually then it's okay otherwise it's gonna remove the all the filters over there and then i'm gonna say order date it should be less than equals to the maximum order rate which is being used to calculate the running total now return a value based on this one i'm sorry there's one more bracket is going to come return a value what we want to return from this one we want to return distinct count calculate distinct count of order number close this and now we have to filter the data so we have already created a variable and then we can use it because this is going to filter our data then we can hit the enter it's being created and let's check it out so you can see that now it's going to uh calculate your cumulative or running total so 5 5 5 plus 3 8 8 plus 6 14 and so on let me create another measure over here sales running total and what we have to do over here again we have to create a filter uh variable so we have uh calculated this variable now we have to return the value and in the return value we need calculate function which is going to return some sum of total sales so we have sales over here this is the sales now we have to filter this and we have already created one filter period variable so let's use it once you hit the enter then close this and take this running sales sales running total in this one and now if i'll just put it up you will see it's gonna return as the running total for the sales and you can check at large both values are the same so let's plot a graph for this one so what i can do over here i'll take this line graph and here now you can clearly see that we have different values sales total sales running total order total and order running total and we can easily compare those values and change your scale to log scale and now you can see every values very clearly now uh let's discuss about the summarize function how we can use the summarize function and why this is so important my manager asked me that you have to uh give me a table in which i can see the country and then the city and their corresponding sales and the order quantity so in that case summarize function is very helpful and also you would face different scenarios in your future while working on the power bi and you will find the summarize function is really great and it helps a lot so let's do that for that i'm gonna create a new table over here i'm gonna go to the modeling tab new table i'm gonna say summarize table bracket country so what you have to do first of all whenever you would write a function index function summarize you will see it's gonna say we need first a table name from on the basis of which we are going to create this summarized table then we have to give it a name of the column then we have to apply certain expressions and if we are not going to apply any expressions in the statement it's going to return us the error our table name is sales order what i'm gonna do over here i need first my country so select the country then i need the city let's take the city and now what i need we are gonna apply the sum function on sales so which is gonna give me the total sales it's grouping by country and the city and then i also need the order now i need the order as well so let me take that too it's saying something is wrong parameter is not the correct type oh yes because i didn't give it a name so i have to give a name total sales now i need the order quantity so i'll write order quantity and my expression over here would count distinct distinct count over here and the order number so now you just need to close close it let's see how does that table looks so over here in the fields pane you will see summarize table has been created but how does it actually looks we have to go in the table view you will see there is a summarize table and over here we have a country field then we have a city and our total sales and order quantity has been summarized so this is a way to create a new summarize table using the summarize function so what is the difference between these two text functions exactly well as you can see on your screen utc now is going to return you the current date and time in daytime format expressed in universal coordinated time or coordinated universal time you can say it either way but remember the utc utc is your universal coordinated time while every other country have their own time zone so always this kind of question comes into our mind how to convert your utc time into your local time zone because whenever you are going to publish your reports on public services that always consider the utc time so in that scenario how to overcome that kind of constraint well i'm gonna let you know in this video but before going into that let's consider the syntax for utc now it's very simple just write utc now it's going to return you a single daytime value that would be in utc date and time format however you should always remember that the results of the utc now function changes only when the formula is refreshed that means when your data set is gonna refresh when you are using either this function into your column or you are using into a measure by column i mean to say a calculated column similarly utc today is gonna give you a date it's not gonna give you time you would see the time over there like the 12 am however you should remember that this is not the exact time at the time you have refreshed your data set or at the time you have used or recalculated your formula the result of the utc today function changes only when the column that contains the formula is refreshed this is not going to update continuously so please do remember these always we are also going to talk about now and today now and today are exactly same as utc now and utc today except they are gonna calculate based on your local time zone that you have set onto your machine or on your laptop so now let's have a look over here if i'll go to the demo tab you would find i have already written down these forex functions that is now utc now today and utc today now over here you can see the now time is 1103 34 am which is same as my local time over here you can see on the right hand side top corner however the utc time is different that is 2r behind my local time so this is the main difference if i'm gonna publish this same report on the power bi services you will notice that it's gonna return the same time whether it's now or today both are gonna come under the utc format so let's check for that i have to publish this report onto my power bi services i'm gonna click over here publish save it and i have many workspaces over here as you can see so what i'm gonna do i'm going to publish it initially into my workspace let's select this one and publish it over there now in order to check that whether these functions are working correctly or not or how they are behaving we have to go to power base services and for that you can directly click on this link it's going to pop up to sign in into your account and for that we are gonna sign in into our account and i have to use a different account so let me just use it and here you go so over here you can notice that now utc now today and utc today all are same there's no difference and why is that it's because your power base service portal always going to consider the utc data and format and it's gonna display it over here now in order to display your local date time format or the data set refresh time then you have to come back to your power bi file and here we need to create another measure as you can see if i am going to refresh this file there is a time difference of two hours between my local time and the udc time format my local time is two hours ahead of the utc time format so in order to display my local time on to power base service portal we have to create another measure and i'm going to call it local time and i'm going to add two hours into this one why i'm adding two hours into this one because my local time is to us ahead then the utc time format and in order to add us you have to write the number of files you want to add and divide it by 24 now you would ask me why i'm doing so because if you are gonna add simply any numbers it's gonna add number of days into your utc now date time and if you need to add the rs you have to divide by 24 because in one day we have 24 hours always remember that and it's going to be very helpful for you now simply hit the enter button and this function is going to be created over here now you can see that i have local time so i am going to drag and drop it over here and here you will notice my this now function and the local time now is displaying the same now our expectation is that whenever i'm going to publish this report into my powerbase service portal i should get my local time over there and that would be my refresh time and for that let's once again publish this report so now we are checking it over here and you can see that my local time now is the local time that i have created this report which you can see at the bottom right hand side corner as well so this is the way you can use the utc now versus utc today or now versus today as well functions it just depends what you are expecting and how would you like to display your date time onto your powerbase service portal for your different reports and dashboards for the very first closing balance month as you can see on your screen it's going to evaluates the specified expression for the date corresponding to the end of the current month after applying specified filters this text function is very useful when you are working in the banking or anything related to payroll or statistics something like that its syntax is pretty simple you just have to write closing balance month then your expression then date and if there is any filter that you would like to apply you can use that as well it's going to return a single value of any type a scalar value that represents the expression evaluated at the last date of the month in the current context however you should remember over here that in order to use any time intelligence calculation you need a well-formed date table and that means your table always should start from the 1st of january and ends at the 31st of december it can help either a single year or multiple year there should be no dates missing and always don't forget to mark it as date table now let's move forward and check for the closing balance quarter it's very much similar to the closing balance month and also guys please remember that in the demo part we would also see the opening balance month opening balance quarter and opening balance year as well in case of closing balance quarter we are going to consider the quarter that means at the end of the quarter we are going to calculate the total in case of year we are going to consider the year that means the context is going to be year rather than individual month or quarter so this one is for the year where you can see that closing balance here is our text function then you have to write your expression then dates filter and also you have to provide the year and date at last now we are gonna have a look at the demo part where i have one code that we are going to execute but before gonna execute that we are gonna get you know how i have created that one so let's head over to desktop2 website and try to execute that one over here you can see that first i'm defining my measures the very first major is sales ytd which is checking sales amount sales amount is nothing it's just the total sales amount where i'm using some tax functions so this is going to give me sales amount so on your left hand side you can see that there is a sales table under the sales table there is a sales amount measure and this is going to be exactly the same that i'm using over here and then i'm using dates ytd over here so what this states white it is going to return me well guys as you know that we have different time intelligence text functions and dates ytd is one of them it's going to return a set of the dates in the year up to the last date visible in the filter context it's going to return a table containing a single column of date values so this is going to help us to calculate the sales ytd now as i mentioned to you earlier we are going to use both closing and opening in case of month quarter and year so i have written down all my measures over here let's discuss one for the month then quarter and year are going to be the same so the very first in case of the closing balance month you can see that first i'm calculating opening balance month so it's just a difference of your next function that you are going to use over here and here again i'm going to use my sales ytd as my base measure and then i'm using date date which is going to be added over here in order to complete the syntax over here so as i mentioned you previously syntax is simply first you have to write your expression expression over here this measure then you have to write your dates and then you can mention the filters as well which is optional if you really want to but we really don't want over here so that's all we are gonna calculate over here opening balance month closing balance month same goes for the quarter and in case of here if you really would like to add one end of the year filter as well you can do that so in case of here we have this closing balance here and opening balance here so that's how i am calculating all measures over here that means the closing balance month opening balance month closing balance quarter opening balance quarter and lastly closing balance year and opening valencia at last i'm going to evaluate my all the measures that i have created and i want to see them in a table that's why i am using calculate table and summarize column text functions over here and then i'm also applying a filter where i need all the dates which are basically going to be less than equals to 2709 that means september 2007. now after writing then you can simply execute it now here as you can see that this is going to be my calendar year month number then my sales then my sales ytd then sales opening balance month then closing balance month then opening balance quarter because first quarter is going to be null then it's gonna start from the second one and similarly over here for the opening balance here and closing balance here you can see all the measures are being calculated over here the very first we are going to use a new text function that is network days it returns the number of whole work days between two dates and both dates are going to be inclusive in this we can also define parameters which and how many days or weekend days plus you can also include the holidays as an optional parameter over here on your screen you can see that its syntax is we have to first write down the dex function name which is network days then you have to define or you have to write down your start date end date and then you have to specify your weekends additionally you can also mention your holidays or there so now let's have a look about the parameters over here the very first is going to be your start date which is going to be a date that represents the start date where you have to perform your calculation in case of end date it's going to be the date that represents the end date now let's talk about the weekends so suppose in different countries we have different weekends for example generally or by default we considered one which is going to be on saturday and sunday and then in some another country let's say the weekend is going to be friday and saturday so on your screen you can see some of the number in the weekend parameter so you can use these number while writing your calculation additionally as an optional parameter you have holidays holidays is going to be a column table for one or more dates that are to be excluded from the working days calendar now let's also focus on some of the remarks while using this text function the very first would be that this function is definitely similar to the excel function which is network disk.intl and networkdays if startdate and ended both are blank then definitely the output value is also going to be blank if either started or ended is blank for example your start date is blank or your end date is blank the blank start date or end it will be treated as 30th of december 1899. lastly dates must be specified by using date function or as the result of another expression for example you can use either use the date function over there which is a text function again to convert your value into date or you can use another format that microsoft introduced lately that is dt and it's also going to be recognized by this text function now we are gonna move to the demo part where i'm gonna show you how you can literally use this text function right now as you can see i'm into my power bi file over here we are going to use this text function on my right hand side i have measure table where i have only one measure so let's create a new measure over here i believe this is going to be visible so very first we have to write our network this dex function next over here you will see that this function is not recognized cannot find the network days function and why is that because it's a new text function so if you are also going to face the same challenge you have to download the latest version of microsoft power desktop application now i'm back on the same file and over here on my right hand side you can see that this measure is still not complete that's why it's showing the warning sign over here and we have already one measure that is total sales now let's click on this one and here i'm just going to remove it so last time what was happening that my desktop our way desktop application wasn't able to recognize this new text function so what we have done we have just updated our power bi desktop application now let's create this text function so first of all you have to write your text function name which is appearing over here network days and here you can clearly see that it's asking you to mention your start date and date weekend and optionally holidays parameter and now let me start writing my start date and for that i'm going to use this date text function again and here i can mention my year month and my day and then comma then you have to mention your end date then you have to also specify your weekend and for this demo purpose i'm just taking initially one only where saturday and sunday are going to be our weekend day and i'm not going to mention any holiday as of now we'll try again into the different x function so let's hit the enter button over here so it's going to return you an integer number of whole days for that i can check this card so these are going to be your net working days those are going to be 97. now let's try to do something different this time and this time what i'm gonna do i'm gonna copy this text function from here i'm going to take two dates and both dates i'm going to be weekend additionally i'm going to specify another optional parameter that is holidays so for that let's create a new measure so we are going to take two dates that is 28th over here you can see 28th of may is going to be a saturday 29th is sunday and then 30th is the monday so we are going to we are going to take these three dates over here and we are gonna keep the weekend same as saturday and sunday so in this case you have to mention the month so let's see what happens over here so this is going to be a saturday sunday is going to be 29th of may and monday is 30th now i'm also going to mention another parameter that is going to be my date parameter for the holidays and here as i mentioned in case of the holidays you have to have a column of dates over there a table that is going to be specified as the column of dates let me format it a bit so what i'm doing over here i'm trying to calculate the networking days from start date which is going to be 28th of may end date which is going to be 30th of may but the 30th of may since it's a monday and over here i'm mentioning it as my holiday so that means all three days are going to be holiday and in this case my expected return value would be zero because there is no working day at all in between them and just hit the enter button or click over here so here you can clearly see that there are zero work days between these two dates so let's recap over here the very first we calculated a measure that is networking days where you have to mention your start date then end date both date formats are supported over here you can also specified as a literal format your date or you can use this most commonly used date format so both are going to work then also you have to mention a number over here which is going to define your weekend whether it's saturday sunday friday saturday etc you can check all the values i'm gonna provide you the reference link in the description section secondly we calculated using holidays first day is saturday then this day is a monday but in the holidays parameter we have already defined this is as our holiday that's why it was returning us a zero value over here that's all guys for this video i hope now you have a clear understanding how to use networked is dex function which is new and i'm sure this is going to help you a lot whenever you have to work on the employee data or you have to create certain measures where you have to consider only the networking days name of text function is going to return the name of a column or a measure the syntax is pretty simple over here you can see that name of and then you have to provide the value of your column or a measure where value is going to be any fully qualified column or major reference it's going to return you a single string value which is going to be scalar value in my recent video where i have explained the top 3 updates from me 2022 i have already explained how you can use the field parameters and that's the case study for this one so how you can use it well let's take first very simple example where we are gonna return a column value for that i'll come in under my fields pane over here i'm gonna create a new measure under the store table in the containers name measure what i want to return i just want to return the name of the continent column for that i can use name of text function which you can see over here and then i have to just write down my column name so let me see over here continent and here under the store table we have this column name continent and just hit the enter button over here now in order to visualize this you have multiple options you can visualize this in a table or maybe in a card because it's a single column so let's see with the table and let me locate my major which is here continent name and here you can see that it's returning store continent which is my column name from the store table for that you have to come under this modeling tab and here you will find this tab new parameters you can select this fields one which is going to exist the fields used to build visuals for reference index so i'm gonna click over here here you can give it a name i'll just call it my dynamic access and then you can select the fields and for the fields i'm gonna select again the continent and then i'm gonna select the store name over here so let's select only two if you would like to rename it you can just click over here you can do that and if you want to remove it just click on the cross button and you can remove them here also i have checked this option add slices to the page so that it's me it's very convenient for me to show you or visualize the data so just click on create button now you would see there is a slicer over here now if you would like to format this slicer you can do that it's up to you so now i would like to visualize my sales per continent and store name in order to visualize the sales what i'm gonna do i'm gonna use this clustered column chart let's click over here you can expand this now let's select sales amount and now what i have to do i have to go for my dynamic axis which is over here and select this dynamic axis in the access field that's all you need to do by default it's going to select that both the fields and if you would see there all the fields have been selected from your slicer that means all the values has been selected from your slicer and if you would like to expand it you can expand and it's going to show you continent and the store name but we don't want to do that so what you can do you can just select one by one too these are your store names and if you would open step up it's your container name or alternatively you can select over here store names these are gonna appear your store names if you want to visualize the container name it's going to appear here as well so from this name of text function what we have learnt so far if you would like to return a value of a column or a measure then you can use it and the best use case for this one would be to apply in the field parameters in power bi reports this is my file where you can clearly see the syntax for selected value tax is selected value we have to take the column name and the optional alternate result alternate result is an optional field whether you want to mention it or not that's up to you also you can see at the bottom i have mentioned an equivalent expression for selected value in this file i have a sales data table so if i'm gonna show you this is my sales data table where i have order number quantity order price of each order number sales order date and so on so you can clearly see we have different fields in this one now what i have to do i have to first create another table which is i'm gonna call my major table so what we can do we can create by start entering the data i'm gonna say it measure table and first column name is my measure name second column name is optional if you want to create it just create it if you don't want then don't create it but i usually prefer it reason being if i have to sort measure name so i can use it with the help of this one so i'll say sort order and i'm gonna create the three different measures one is sales second is cost and third is margin also i'm going to give it to order one two and three so let's click the load button it will take couple of seconds okay so now we can see in our data modeling pin we have two tables one is sales table another is the major table that we just created and there is no relationship between them whenever there is no relationship between the tables those are known as disconnected tables selected valued x function returns the value when the context for column name has been filtered down to one distinct value only which means it is not going to return multiple values otherwise it's going to return the alternate result which we just mentioned over here let's create a couple of measures over here the very first would be total sales so this is gonna give us the total sales major the second measure that i'm gonna create is my cost so cost is so guys before creating the measure we need a column of the cost which we don't have right now so how we can do it we can do it uh after multiplying the quantity order to the price of each i have to create a new column that particular product so here we have the values my second major was the course so i can calculate here now there are two measures existing now in my table one is my cost another is my total sales third measure that i'm going to create is the margin so let me visualize this so guys this is my table let me do some the second part is that i want to select my measure and according to that this should vary guys now this is uh we have my major name we have my table where i'm having product total sales cost and margin so what i want whenever i select a major or here this one should vary but if you gonna look at this it's not gonna vary according to that what i want i want if i select sales only sales should appear if i select margin only margin should appear if i select post only cost should appear so how to do that we have to create another measure and that measure is going to do this work for us i'm gonna create a measure that's in my selection first of all i need to create my variable where my selection this is the main part where i'm going to use the selected value next function so i'll say selected value you will see in the drop down select this one i have to select from here like whenever i select a measure in the slicer that is gonna come from the measure table so what would be that that would be my major name so i'll say whenever i select this it should return me the name otherwise it just should return total sales total sales so you can give any optional one that you would like to give over here and it's gonna work my variable has been created now the another part is that what i want to return i want to return i'm gonna use switch statement here switch which is very easy to use it's like a file statement only so i'm gonna say switch true that means once the condition is gonna meet only then if my variable that i just created my vary my variable which is my selection is equals to total sales it should return me y um measure that i have created that is my total sales major otherwise if my selection is equals to the second measure that i've created that is cos then it should return me the cost lastly if my selection equals to margin the third measure so which measure i want to return that is my margin one we can even give the alternate you know if none of them is meeting so which one we we want to give so we can just say okay if none of them are meeting then we can say just return me the total sales that's how it's gonna work hit enter let's go through this once more where i'm gonna make it a bit shorter which is easier for read so guys what did i do over here first i created a variable over here which is underscore my selection here i use this here i used selected value dex function which is gonna give me the value that i have selected in my uh particular major slicer so whatever major name i'm going to select over there it's going to return me that if not selected then it would return me the total sales one after that i use the switch statement where i'm saying if my days equals to total sales then you tell me the total sales measure if it's the cost it's gonna return me the cost measure if it's the margin it's gonna return me the margin one otherwise total sales so that's done let me control and paste so this is my another table right now you will see whatever i'm selecting values are unchanged nothing is varying so in this one rather than picking individually what i'm gonna do i'm just removing them and i'm gonna bring my newly created selection measure which is over here right now you will say nothing is selected or in this my selection by default you can see that if nothing is selected or it's returning more than one value it's gonna return me the total sales that's what is happening over here total sales is one seven six it's not returning me one seven six it's for motorcycle five six zero five four four two three it's gonna return me the same now see the magic happens over here if i select margin so see it's gonna give me the margin values if i'm gonna select the cost it's gonna return me the cost values so this is how we can use selected value to create a slicer where i can use the measures and those measures can be used to toggle the different values next step in the next step you have to stay tuned on our youtube channel for our decks and these videos well we publish a new text video every sunday and we bring something new index for you so that you can learn something new index and if you have any question or concern please don't forget to connect with us you can also visit our website that is www.biconsultingpro.com for all the trainings and consultation if you are new over here please don't forget to subscribe our channel and hit the bell icon for all the latest videos and updates [Music]

Channel: BI Consulting Pro

Views: 55,632

Keywords: dax concepts, dax formulas, beginner to dax, dax demo, dax power bi, dax training, dax language, calculate function, dax fundamentals, dax calculations, complete power bi dax course, dax basics in power bi, power bi dax tutorial, power bi dax for beginners, power bi dax studio, dax functions, what is dax, power bi, dax, learn dax, time intelligence, tutorial, microsoft power bi, how to use power bi, power bi for beginners, dax in power bi, power bi dax, dax crash course

Id: Z_sK10luXQI

Length: 178min 54sec (10734 seconds)

Published: Sun Aug 14 2022