Power BI DAX Tutorial - Beginner to Advanced [Full Course]

Dax or data analysis expression is a powerful language that allows you to create custom calculations and measures in powerbi it's essential for any powerbi developer or data analyst to know how to use Dax efficiently now these are the main categories of Dax functions that we will cover in this video after watching this video in case you feel that I missed any particular Dax then do let me know in the comment section and I will be happy to look into that and take that up in the next video this video is designed to be a go-to guide for anyone who is preparing for a power interview learning DXs from scratch or just needs a refresher this will provide you a clear and concise explanation of each function as well as examples of how to use them in real world scenarios [Music] so before we start just a quick disclaimer we'll be using powerbi to cover Dax related topics but the concepts are the same if you would like to use Excel Power Pit or power query all right so let's just get started so today's video I have divided it into several topics and each of the topic are individually important to understand that's why I'm going to go each one of them in detail today okay we're talking about DXs today and and introduction to Dax so it's important to first understand how do we structure it so similar to how we write Excel function Dax also has predefined functions and operators which are clued together or used individually to create your final Dax formula now this is an example of Dax function which sums two columns separately and then divide them to get the final result okay now here we have used the sum function which is a predefined function within Dax similar to excel with the division operator to get the final result okay now since we're talking about operators it's important to know the list of operators that are used in Dax so here is a list of operators that are used in Dax on a high level we have arithmetic operators comparison operators text operators and logical operators so now where can we use DX functions so there are two areas or ways where how we can use a DX formulas first one is inside a calculated column and the second one is inside a calculated measure and we'll understand each of them separately okay when you want your calculations to be done on a row-level basis then we enter Dax into a calculated column now let me explain this with an example to understand this example we'll first import the data set that we're going to use today the links will be in the description in in case you want to follow along so the requirement here is we need to create a new column which will show the revenue for each row item in the data using the price USD column and the sales column so in this case since we need to calculate the revenue for each row separately and store it inside a table physically we will create a calculated column now usually while creating a calculated column I prefer creating it from the data view because it gives us a visual preview of the data however there are other ways as well which are as follows so the first method is simply to go to the report view go to the modeling Tab and click on new column the second option is to go to the table where you want to create your new column in the Field section and click on this three dot icon and click on new column okay and the third method which I usually prefer is to go to the data View and right click anywhere on the table where you want to create the column and click on new column okay so I'm going to use my preferred method here and once you done that once you clicked on new column just enter this formula here so I'm going to name this formula instead of column I'm going to name this as revenue and I'm going to use price and sales column to get my final Revenue I'm going to just simply write price and pia has a intelligence feature uh so you can just select it from here you can either click it here or press tab so I'm going to just click here and price multiplied by sales okay so I'm going to use this sales column here so the way Dax is formed is if you notice it gives you the table name and then the column name that's how Dax is structured this is helpful because if the same column name exist in more than one table then it will be easy for you to identify which column you want to refer once you have entered the formula press enter or click on this stick icon here and you have successfully created your first calculated column okay now coming to the second place where you can use your Dax is calculated measures so when you want to do a calculation on an aggregated level and don't particularly want to store information on row level basis then that is when you want to use calculated measures now in comparison to calculated columns measures don't create physical values in your table hence do not increase the file size so that is an important point to note okay now coming to measures uh I'll explain it with an example let's say the pizza shop ran a 15% discount on each pizza so what we need to do is we need to calculate the final revenue for all pza sold using the price USD column sales column and the discount percentage now in this case obviously we'll be using a calculated measure because we don't particularly want to store the information in a table and instead want to calculate the aggregated value now measures will not be visible anywhere on the data view okay so hence I prefer to use measures or create measures obviously in the report view because since measures are not stored physically on a table you would not be able to see the measures physically in anywhere on the table unless you drag that measure into a chart or a matrix kind of a visualization okay so to create a measure what we need to do is I come to the report view here and click on the three dots icon here and click on new measure and to fulfill our requirement what we need to do is first of all we'll give a name I'm going to use rev discounted here okay and one main difference between a calculated colum man measure is while using a calculated column you don't have to specifically run or apply a aggregation function with with your column name but with your measure an aggregation function is is mandatory you need to encapsulate your column name with a aggregation function and my measure has been created now the final topic for today is implicit measure versus explicit measure okay now this is just a concept that you should keep in mind by default when you drag a column into the report layout Bia automatically creates an aggregation measure which is known as implicit measure okay you might not have realized this but let's say I create a chart here so when I dragged sales and P name onto this chart in the x-axis you must have noticed that it automatically aggregated my sales so that my output has a grouped effect on it okay so it applied a sum function I can also apply an average function minimum function and all those functions so these are predefined function funs which pia has and when you drag a column onto the report layout or the you know the visualization pain it automatically applies that implicit measure onto your metric now on the other hand explicit measure are the measure which we create by entering a custom formula in the formula bar like we did earlier while creating the revenue and R discounted columns now both type of measures serve their own purpose but explicit measures are much more flexible and help in building complex Logics for custom analysis on your metrics and kpis so continuing our journey into Dax functions we're going to see today how we can create a simple table by writing a Dax formula yeah you heard me right we can create a table using Dax and specifically we're going to create a table for calendar a single column table which will have calendar dates in it I'm going to use the same data that we used last time so that it's easier to understand and relate what we're doing so in order to create a new table from Dax First Step that we need to do is we need to go to the modeling tab okay and inside the modeling tab you will see a button called new table all right so click on this button called new table instantly you will see a formula bar where we will be entering our Dax formula okay so we're going to cover two basic Dax functions today first one is calendar and second one is calendar Auto okay so I'm going to start with calendar so what calendar does is it creates a table for you from a starting point and an end point of date that you give to the Dax function so let's say the starting point for my Dax is 1st of January 2022 and the end point is 28th of Feb 2022 okay okay and close so I've given it a starting point and an end point all right and I press enter the moment I press enter it created a table here okay you can rename the table as per your liking but if I go to the data view as you see it has created a table for me I'm going to quickly format this into a date format instead of a date time format okay and as you see it created a list of dates for me starting from 1st of January 2022 till 28th Feb 2020 2 okay now if you remember couple of videos back we learned how to create rolling dates using M query the difference between that approach and this approach is that in that approach we had a specific parameter for defining duration okay so I mean suppose you want the dates dates to have you know one day duration or two day duration or 3 days duration you can use that approach but if you do not want that and just want a simple start date and end date and want to list down all the dates between those dates then you can use this approach instead of that one so this one is simpler in case you do not want that advanced capabilities coming to the second Dax which is calendar Auto so I'm going to delete this table and I'm going to tell you why I'm deleting this table okay I'm going to delete this from model so what calendar Auto does is it's kind of a intelligent feature or intelligent Dax within powerbi which detects the existing dates within your model so right now in my model if you consider this as a model although we don't not have any additional tables here but if you consider this as a model uh this table has a date column which has dates from 1st January till 5th January 2023 okay what calendar Auto will do is it will detect the dates that are existing in your model right and create a range of dates which which includes those dates okay and the dates that it creates will be covering an entire year from JN to December so let me show you how okay let me just create a table once again so I'm going to go to my modeling Tab and I'm going to click on new table and I'm going to write calendar Auto and I'm not going to supply any parameter here you can just close the brackets and press enter okay it has created a table and if I go to my table and format it to a date format and see the results I've got every date starting from 1st of January 2023 till 31st of December 2023 that is because calendar Auto generates a list of dates which covers an entire year but it ensures that it covers the dates which were present in my model okay so let's just test this Theory out okay I'll go to my transform data section and within transform data I'm going to add a new step to replace 1st of January 2023 to 1st of January 2022 let's go to my data View and if you see my calendar Auto function automatically detected that my first date the data which my model contains is starting from 1st of January 2022 so that what that's why it included 2022 in here right it started from 1st of January 2022 and ended till 2023 31st December because my end date Falls in 2023 that's why it has included that year as [Music] well we're going to see couple of more datetime DX functions which we can use in our daily uh reporting or analysis purpose now the three backs that we're going to discuss today are date date diff and date value okay and I'm going to show you how each one of them can be used in certain scenarios with examples so we're going to start with date okay so to give you a simple idea what date does is it Returns the specified date in a datetime format okay so let's see this with an example I'm going to create a new measure here and in this measure I'm going to write date okay and it's asking for three parameters year month and day so I'm going to supply it with that exact thing so 2023 year month let's say Feb and day 15 okay now it has created a measure for me with this exact date okay so I'm going to drag this measure onto my canvas and I'm going to drag it into a card I'm going to format it into a date format now if you say it's showing me the results as 15 F 2023 so what it did was it converted the numbers that I provided for each parameter and converted into a date but now when you think about it how can this be useful because it's just a simple static date that you've created right so this can be useful when you use it in combination with a different column okay so let's see that with an example I'm going to go to my data View and instead of creating a measure now I'm going to create a new column okay so new column and with within this new column I'm going to use that same function but now I'm going to use that date function alongside the date column that I have in this table now the requirement here is I want the date to have the same year as this date column I want the month to have the same month as this date column but the day should always be the first day okay so I'm going to do what I'm going to do is date year for in the year parameter I'm going to Simply write ear ear is another function that you can use and within that I'm going to use this pizza sales date column okay in the month parameter again I'm going to use a function called month and within that month function I'm going to use this same pza sales date column and then day parameter I'm just going to say one okay because I want the first of every date that I have in this column and I'm going to press enter so and I'm going to format this into a date again so if you see what I've done is I've successfully created a dynamic column which will always give me first of the month of whatever date you have in this column so depending on the year and the month that it has it will always use that year and month but along with that it's going to change the date to First cool right coming to the second Dax date diff okay now I'm going to create another column here new column okay now what date diff does is it Returns the number of interval boundaries between two dates basically it Returns the difference between two dates and it also has the option to take a interval parameter okay so I'm going to show you how so in this what I want is I want to calculate the difference between these two dates okay so I'm going to use date diff the first date is the starting point um so I'm going to use this column so P sales column the second parameter is the end date so we're going to use the date column here so pza sales date column and the interval now you can provide whether you want the interval to be in days hours or minutes right or even months quarter seconds year as well right so in this case let's take it in hours because the difference between these dates are not that great so I'm going to use hours here and press enter now if you see I know that these two dates are the same so zero hours difference but if you calculate the difference between these two dates it's exactly 24 hours apart and similarly similarly we have the uh respective hours for each date calculating the difference I can also change this to day to check how many days difference is there between those two dates and it showing me the correct results here right all right perfect so now coming to the Third and the final DX which is date value now what date value does is it converts a date in the form of text to a date in date time format now you might be thinking that you know we can do this even by using the date function that we learned um just before this so let's understand with a different data set here so I've imported a small data set which has three columns in it day month and year okay and each one of them is in a different format so first one is an whole number second one is a text third one is again a whole number okay now if I try to use the date Dax here so let me just create a new column and I'm going to use date and Supply the year as in the parameters the first parameter is year obviously so I'm going to supply it with year second parameter is month I'm going to supply it as month and third parameters day and if I try to create this it will give me an error because date function cannot convert this text into a integer automatically okay so instead what we'll do is we'll use the date value function and for the date text we need to First ensure that all the text are compiled together to compile these three columns together we will use the concatenate operator which is ERS okay so this is how we will concatenate all three columns so to create a date kind of text what we'll do is we'll use the First Column as day so sheet 1. day and erson sign and a forward slash okay similar to what we have in dat dates then again an erson sign and we're going to use the month again an erson sign in double codes forward slash ample send and then we're going to use final column here okay and press enter now if you see this text has been automatically converted into a date column now so we can now consider this as a date and format it accordingly so this is going to be the last video within the datetime daxes that we're going to cover apart from the DXs that we've already covered there are many other DXs within datetime function and feel free to check them out by going to the link in the description which will take you to the Microsoft website uh where you can you know view all those daxes and we will not be covering those remaining daxes uh within this video or any future video because I believe that those daxes are pretty straightforward and you can follow them just by having a look at it since that's out of the way let's jump into today's topic and the first tax that we're going to cover today is something which is simple enough but this is something that you know we often use to make our formulas or make our daxes Dynamic there are two Dax that we use in this first one is called now okay so let me just create a new column so that you can see what's happening instead of a measure I'm going to create a column and first one is now what this Dax does is it does not take any parameter whatsoever you can just enter now and close the brackets and when you press enter it will give you the current date and time which is there in your system now how does that help when you create a measure or when you want a column or a formula to be dynamic enough so that it gets updated according to the each day of the calendar then you might want to use now and something similar is the second Dax that we're going to cover is today okay so what today does is it instead of giving you a date time it gives you a date the current date okay so again today and it also this does not take any parameters you just open the brackets and close the brackets and you press enter and now it is giving the current date uh you are saying date time because this column is formatted as date time so if I just format it back to date you will just see the date so it's showing you the current date as per your system coming to the third Dax so you might have seen a Dax that I used in the last video which was year and then a month Dax okay where you know we provided that with a date and it extracted the year out of it and then it extracted the month out of it similar to that Dax there is another Dax uh which I thought we should cover because we already covered year and month the Dax is called day all you have to do is just write day and provide it with a date so we're going to use this pizza sales date column and what it does is it extracts the day from the date okay so the first second and third like this right it extracted the day out of the entire date now the fourth Das that we're going to cover today is called E date so what this does is it returns a date which is a number of months before or after the start date okay so I'll show you with an example so I'm going to use the same pza sales date column and in the month's parameter I'm going to write two so what it will give us is it will use the same date here and it will add two months to it in eatee when you give a positive number it means to add that many months however if you give a negative number let's say minus one it minus the number of months from the current date okay so in this case when I said minus one it gave us December 1st of December so it did not change any day or year it just simply minused one month from the current date and then gave us the final output Dax number five EO month now what this does is it's pretty interesting it gives you the last date of the provided date so let's say you want uh from this date so pza date you want the last day of whatever date that we have in this column okay so we know that last day should come as 31st January right so I'm going to write zero because I don't want to add any month to it you know I just want to calculate the last day of the current month that is there in that column so I'm going to press enter and it is giving me 31st January if I gave it a number let's say one what it will do is it will add one month to the existing date and then calculate the last day okay so if you see now it's giving me 28th Feb similar to eate we can also add negative numbers to it so minus 2 let's say and this will give me 30th November now in E month there's an interesting Pro tip instead of the last day of the month you want the first day of the month right so so in that case what you will do is let's say I want first day of whatever date I have I want first day of the next month what I can do is zero which will take it to 31st January and then say plus one which will give me first of febr sixth Dax Network days so what network days does is it gives us the number of working days within your supply dates okay and you have an option to remove week weekends as well from this as well as holidays okay so the starting date for me is 25th of November 2022 okay I'm going to enter it with an a double code so I'm making this a started date the end date is the date that I have here pza date column now in the weekend parameter I'm going to write one so what one means is it's considering Saturday and Sunday as a weekend okay but there are other options as well I'll paste a screenshot on the screen somewhere you can pause the video and check out the other options that are available if in case you want a different weekend so let's say your company or your organization have a different weekend structure let's say you have holidays on Friday and Saturday instead of Saturday and Sunday so you can use that as for now I'm going to use one only okay because I want to consider Saturday Sunday as the weekend and in the last parameter which is holidays what you can do is you can supply it with a list of holidays that you have in your organization so what you need to do is you can create a new table a separate table which has couple of dates in it okay and you can consider those dates as your holidays and this formula would automatically ignore those dates while calculating working days so for the final parameter uh for Holiday I've imported a table which is called holidays and I just have a simple column in that table with couple of dates which will represent the number of holidays that I have in a particular year okay so for now I'm going to use that table itself so holidays okay and close it's calculating number of working days using the starting date that I provided and the end date okay you see a negative number here is because uh the end date that is applied here for these three row items is smaller than the start date that I provided which was uh 25th of November 2022 right so that's why you're seeing a NE negative number but otherwise uh what it did was it ignored Saturday and Sunday because I said one and uh calculated the working days considering the list of holidays that I have in this table okay so we have couple of holidays in this table so it considered that and calculate the final Network days last DX for today is week day and what what weekday does is it returns a number which represents a day in a week okay so let me give you an example pizza sales date and there are three options to it one 2 and three one means your week starts from Sunday and ends on a Saturday so Sunday being 1 and Monday being two and so on and so forth if you choose two here that would mean your week starts on a Monday and ends on a Sunday Monday being one and three means your week starts on a Monday and ends on a Sunday but the Monday will be counted as zero so the numbering would start from zero and then zero for Monday one for Tuesday and so on and so forth so I'm going to choose one where my week starts from Sunday so it has given me a number which represents each day of a week so I know that 2nd January was a Monday so that's why it's showing me [Music] two today we're going to start with the aggregate functions with that we have within Dax okay and uh there are many aggregate functions that are available and I'll place a link in the description where you can access the full list of those functions but today we're going to cover the most important ones that you will ever need while working with aggregate functions okay now starting with the most basic ones that we already know and use in multiple scenarios are sum count average and count a okay so we're going to just quickly see how that works because I just wanted to cover the basics uh although you might already know these functions so feel free to skip ahead in case you already know them but just for the beginners I'm going to just cover them quickly okay so when you talk about sum and I'm going to create a measure to uh showcase all the aggregate functions here I'm using the same data set that we used last time the P data set right so what I'll do is I'll just insert a new measure and within this measure I'm going to first write the sum function okay so sum simply means that in case you want to sum an entire column U you know you can just apply sum function and you know let's say I want to sum the entire sales so you just enter it like this and that is it it will give you a sum of the entire sales column and I'm going to drag this into a card so that you know what's happening okay now remember there are other options to you know apply filters within this measure but we will not be covering those in this video because we know we just want to cover the basics first and then get into those mashup functions where we club together multiple functions together to create amazing results right so we're going to get to that in future videos but for now let's try to focus on the basics now you understood how sum Works uh similarly this is how count works so you can just just write count uh let's say you want to count the number of pasas pza names that you have in your data so it will just give you a count okay the basically it's counting the rows that are there the third function is average so let's say you want the average sales that were there for the for all the rows that we have so you can just apply average so it will give you the average results and then we have uh count a where we can just count number of values in a column which are not blank okay so I can use any column here pza code let's say example and it will give me a count all right now let's jump into the exciting stuff all right um there are a set of functions within da uh especially within aggregate functions which has an X at the end of it now here is a list some X count X count ax average x minan x max X and product X okay now do you understand what that X stands for basically X stands for expression and what these set of functions does is it calculates a certain expression at row level and then apply the final function at the end so let me give you an example so let's say in this data you want to First multiply your sales and price USD together and then the output of that multiplication should be summed up for each and every row so whatever output that you receive uh all those output should be summed up at the end now there are two ways to do it first one is to create a calculated column and in that calculated column you need to you know apply this multiplication and at the end you can apply a sum function right in a in a measure but that that's a longer approach a better way to do it is to apply sumx let me just quickly apply that function and show you how it's done so let's say I'm going to write sumx okay and I want to what I want to do is first of all refer a table uh in this case it's pza sales and the expression that I want to give so expression is simply a formula or some kind of logic that we want to implement within this sumx function okay so let's say I want to use price USD column and I want multiply that with sales okay and then close the bracket it has given me 14k so let's see if the result that we got is correct or not so I'm going to what I'm going to do is I'm going to create a new column here in a new column I'm going to multiply the price USD column with sales column okay so I've got this result column right now what I I will do is in a new measure let me create a new measure measure here I'm going to sum my column okay the new column that we created and I'm going to drag this again into a card and if you see the results are the same right but while applying this method we did an extra step of creating a new column which obviously increased your file size and then you applied another sum function to get this result but in sux we simply applied sumx and got that result now let me give you another example okay instead of sux let's apply average X so again the table name is pza sales and I want to substract sales figure with unsold figures and then get an average of that entire thing what this function is doing is first of all it's subtracting un sold value from the sales and then with that result for each row item it is calculating an average okay so I'm going to create one more column here and within this column I'm going to say sales minus unsold [Music] okay and this column two I'm going to write another measure here using this measure I'm going to say have rage column 2 as you see the results are the same but the first approach is much better right so I hope you understood the expression aggregation functions uh there are a list of functions that you can use including minimum maximum product average count Etc so you can just go through them try it out on your own the concept Remains the Same it will calculate calculate whatever expression you provide for each row item and then apply the final function that you gave it I mean whether it's an average count sum or whatever it is okay all right perfect now coming to the final part of this video which is distinct values okay now it has three main functions to it two of them would be covered in a measure and the final one we will cover it in a table okay so we'll see how it's done so first one is distinct count okay so let's say you want the distinct count of pza names here okay so what you can do is just write distinct count and choose pza name column and it will give you a count of six but I know for a fact that total pzas that I have the type of P that I have are five it is giving me six because there is a blank Row in this data set and it's counting blank as well okay so in order to avoid that you use another function which is called distinct count no blank okay so distinct count no blank again P name and this time the results would be five cool all right perfect and the final function in this is distinct so what distinct does is it returns a table with one column with distinct value of whatever column you require okay so what we'll do is we'll go to modeling Tab and click on this new table and within this new table I'm going to write distinct and I'm going to use pza name column okay so what this Dax does is it creates a table for you you remember a couple of videos back we saw two daxes within the datetime function which can create a table this is a function within aggregate family which can create a table if I go to the data view this is the new table that we created and if you see it has given me the table with distinct values in it now there is a blank value here because as per powerbi blank is also a separate value so that's why it's considering that as a distinct value you can apply additional filters onto this table in case you want to remove that blank value okay today we're going to discuss a very important topic which is logical functions and trust me while doing any type of analysis on your data logical functions are very essential and it becomes a backbone for many complex calculations that you do okay so it's important for you to understand this particular function so we're going to discuss today five basic logical functions today and these are if if error and or and then switch now out of of this five we have already discussed switch in detail so I'm not going to cover that in this video you can check out the Excel switch function video that I made uh a while back links will be in the description which will explain the switch function to you so the concepts are the same in both Excel power pivot and powerbi so whatever applies there goes here as well okay so now we're going to jump into our first function for logical functions which is if now what if does is if checks a condition and returns one value when true otherwise it returns a second value okay so this is how it goes so I'm going to create a new column here now the requirement here is if a particular pizza sales is above 100 then I want to mark that pza sales as a top seller otherwise I want to tag it as an average seller okay so for that I'm going to use this new column and I'm going to say if sales column right which we wanted to use as a logical test test is greater than 100 then the result should be top seller else it should be an average seller so as you see I've given it three parameters first one is obviously The Logical test that we're doing whether the pza sales column is greater than 100 or not so if this statement is true we're going to use this string which is top seller and if it is not true then we're going to use this string which is average seller so we have used these three parameters and if you press enter every sales item which is greater than 100 it has automatically marked as as stop seller now let's look at second function that we will cover here is if error okay so this will be helpful when you want to specifically identify if a particular value or a particular row has some kind of error in it or not and then accordingly assign a certain value to it so to do that I'm going to insert a new column here and to create an error uh manually here uh because I don't have any error in this dat data set so I'm going to create a error manually uh we'll what we'll do is we'll simply divide the sales value by zero okay which will give us an infinity or division error okay now what we'll do is we'll create another column and we'll say if error column 2 right which is the first parameter so we are to we have to define the value that we want to check and if that value has any error I want to mark it as let's say zero okay as you see it has marked all error values as zero and left that blank value because it did not have any errors in it now comes the third function which is and now there are two ways to write an and function you can write it like this and okay or you can write it like this erson sign twice okay this this signifies the same thing but a major difference between both of them is when you write an and function like this you will only be able to give it two logical conditions here okay not more than two but when you use an double erson sign you can use as many logical conditions as you want okay so I'll show you examples of each okay so the first requirement that we have is I want to Simply check if we have pza name as Delux and and second condition is category equal to non which so if both of these condition are met because and means whatever logical conditions that you provide all conditions should be met then it will give you a true value okay so if both of them are true it will give me a true like this here but anywhere else it will give me a false similarly uh I'm going to get back to the and condition again when we using the amples sign but let's cover the or function first so or is similar to and again when you write it like this uh you only have two options two logical options but another way to write or is a double pipe sign and we're going to get to that in a while when you're using a normal R let's say the condition is you want to see if pza name is equal to double cheese or category is equal to wedge in both these cases I want it to have a true value else false so as you see it has given me the correct values here uh if it was a double cheese or if it was a wedge it has given me True Values anywhere else it has given me false values now let's see an example of using and and or inside an if condition okay which is again very powerful so we are now mashing up two functions together right so what we do is if Now The Logical test is I'm going to say and price USD is greater than 10 and column value which we created earlier is equal to top seller bracket close then if both of these condition are met because we using and it would simply imply that both these conditions should be met if both of them are met and it is true then in that case I want it to mark it as star else I want to mark it as budget and close the bracket and press enter so as you see now it has checked all the rows all the conditions and uh accordingly applied the values that I've given it okay now let's see an example where instead of using an add we going to use a double erson sign okay and you will observe that using a double erson is much more readable when you look at a formula condition goes like this if pza sales is greater than and this time I'm going to use equal to 10 and column value is equal to top seller then okay I'm going to remove this this bracket because I don't want this so I'm going to check if both these condition are true then assign it a star else uh mark it as budget so if you see it has applied that now the good thing about ERS sign is I can always add another condition here which was not possible while using the normal and function so I'm going to add a third condition which says and pza sales unsold column is less than 10 so if you see it has accommodated that third criteria as well similarly like erson sign we can use the or function using the pipe sign here because we're using or it will see if any one of the condition is true then it will mark it as a star else it will mark it as a budget so with this I think all of them are stars right so again uh depending on the condition you can choose to have uh if or condition if and condition and then also Club if and and or together by separating them out with brackets so like for example I can do it like this is equal top seller and pza sales unsold is less than 10 okay I can do it like this as well today we're going to look at couple of functions which are in the text function category now as per me the list of text function that we're going to cover today would be more than enough for you to master this particular category of functions but in case you want to explore more text functions then I'll place a link in the description you can go to that link and check out the other functions which are available and explore them all right so without wasting any time let's just quickly jump into the text functions so the first one that we're going to cover today is upper and lower so I'm going to just create a new column like we usually do okay and what upper and lower does is is it change the uh text into an uppercase text or a lowercase text okay so you just write upper and let's say I want to convert all my pza names into an uppercase you just enter it like enter it like this and instantly all of your values will be converted to upper case similarly uh you know you can use lower as well to convert all of them into a lower case unfortunately proper case is not something that we have in Dax so in case Cas you want to convert your text into a proper case you would have to go to Power query and convert it from there okay all right so moving on the second function that we're going to cover is called concatenate basically what this does is it concatenates multiple strings together okay so the way you write it is just say concatenate and then enter your text that you want to concatenate let's say I want to concatenate the pza code and pza name okay so it will give me the concatenated string but one thing to notice here is when you're using concatenate function by writing concatenate only give you option to concatenate two text right um it's it's similar kind of limitation that we had or we we saw in The Logical functions when we used and and or so similar to that uh we can use concatenate in a different way by using erson sign and I'll show you how so all you have to do is me mention your column name so pza code okay and then you write an erson sign and then you write pza name and let's say I want to concatenate uh category as well so I'm going to use that as well okay so if you see now I can concatenate more than two strings if I'm using the erson option now obviously reading the text like this does not make sense you would have to give some space in between so in order to do that you just just write it like this so what I've done is I've added another erson sign in between and added a space to it and let's say in this I want an hyphen sign and here I want a space so when I enter this as you see it it has given me the text that I wanted all right the third function that we're going to cover is uh Len what this function does it it gives you the length of characters or the number of characters that you have in your particular string so let's say I want to identify the number of characters that I have in the pza name column so I'll just write Len pza name and enter so it has given me the length of each string that are there at each row now comes a interesting function uh which is Left Right and mid so and this is something that we you know usually use a lot um even when I when we are using Excel and you must observe that most of these functions that we have we are discussing today are exactly the same in Excel as well so if you know these function in Excel you know you would not have any problem anyhow and if you learn it here you can apply the same thing in Excel as well okay so let's say left what this does is it returns a specified number of characters from the start of a string starting from left okay so how does that work is let's say I want the first four characters from the pza name column okay starting from left so what I'll do is left text would be Pizza name and number of characters that I want is four and press enter so what it will do is it will you know count four characters from the left here and give my give me the output right similarly uh I can use the right string enter right and let's just say I want three characters from the right and as you see it has given me the required characters from the right hand side of my column now then we have a function called mid so what mid does is it returns a string of characters from the middle of a text string you know so we would have to give a starting point and the length of a particular string that you want from that particular column so let's say again pza name okay and I want let's say from the second position okay so I will write two I want three characters okay so starting from the second position I want three characters so if you see it has given me that exact thing so starting from the second which was e in this first row and it has taken three characters starting from the second position okay so it has given me what I wanted from the mid so mid is bit bit more flexible in in case you want to extract certain number of characters from an entire string okay so next is replace function so what replace does is it replaces a part of a text string with a different text string okay so let's say I want to replace the first four letters of whatever string I have with the word City okay so what I'll do is I'll as a old text I I'll give pza name okay starting position I want to keep it as one because I want from the start first position I want to replace whatever there is and the number of characters I want to replace is four and the new text that I want to place here is City okay so basically what I want to do is replace the first four characters with the word City okay so I'm going to press enter and as you see it has replaced the first four characters we can also choose to you know let's say instead of four characters I want to replace eight characters and replace it with City Okay so if you see because in the first row we did not have eight characters in this entire string it replaced everything okay and it just pasted City there removing everything from here wherever we had eight characters or eight or more characters it simply removed the first eight characters and then put in a city there and then the remaining characters like here in the double cheese row it has removed eight characters from the beginning and then put City there now the next fun function is substitute now what it does is it replaces an existing text with a new text in a text string okay now you would ask what is the difference between replace and substitute right the main difference here is substitute can find a particular character in your entire string and then replace that accordingly so here is an example so in this entire string wherever it finds alphabet o and and U I want to replace that with A and N alphabet A and N I'm going to give the text as the column name which is pza name the old text that I want to search is OU okay and the new text that I will give is a n and mind you this is case sensitive so if I capitalize this uh this will not replace this OU here okay because this is in small letters and the instance number this is an additional parameter that we can give instance number simply means when it searchs from the left so it is actually searching the entire string from left and the first instance it finds it's asking whether you want to replace that or you want to replace some other instance that it finds so first instance of the same thing second instance like that right so right now I'm going to replace the first instance and enter okay as you see it has changed OU here because it it was the first instance similarly let's say I want to replace every e with an a now I want to replace the second instance now observe this in Delux I have e at the second position and then e at the last position right and I've said replace e with a only for the second instance and I'm going to press enter now if you see in the result it has not replace the first instance it instead it has replaced the second instance now if I write third instance it will not replace anything for Delux because it did not find any third instance but for double cheese it did find third instance so I hope you understood how this works now the last and the final function that we're going to see is search you basically give it a text that you want to find so I want to find let's say X in pza name column and the starting position that I want to check is I'm going to keep it as one because I want to start searching from the first position itself and not found value that I want to see is if it did not find any text with that particular character uh I want to enter as zero so what this function does is it gives you a position of the character or the text that it found in that entire string so this five simply means it found X on the fifth position in the string so starting from the left if you count five 1 2 3 4 and five it found X on the th position right and the zero means because we've entered zero for no value it simply means that it did not find X anywhere in this particular string the Dax that we're going to cover today is going to be very important for you to understand and this will be used again and again in multiple scenarios whenever you start working with data okay and the da that I'm talking about is called calculate and we we will also see how we can use calculate with a specific Dax function called all and in what scenarios we can Club both of them together to get desired results let's go I have this data set here uh this is same data set that we used in the last video which contains a list of names of couple of students and the subjects that they are studying and the score that they attain in one of their semesters also I have another table with the with the same list of students that I had in semester 1 score table with the scores that they attained in semester 2 and for ease of views I have merged both the tables together so that semester 1 score and semester 2 score are in the same table like this I'm going to rename this as master table now before we get into the topic of how to use calculate and all together it's important to understand what is calculate function now although I have covered this topic previously in one of my videos but just in case you have not seen that video I'm I'm going to give you a quick recap of what calculated function is all about so in essence what calculated function does is it evaluates an expression utilizing a filter criteria that you provide let me give you an example so let's say you want to know the sum of score acquired by a student B in both the semesters okay so for the do that I'm going to insert a new measure here score by B and I'm going to write calculate sum of Master semester 1 score plus sum of Master semesters 2 score in the filter parameter I'm going to write name is equal to B now let me drag this new measure into the canvas to see what's happening I'm going to drag it into a card so now I know that student B has acquired 390 across both the semesters Al also one more important thing to keep in mind right now or filter parameter is not complex it's simply stating a particular table column name and using an equal operator to Define what kind of filter we require but just in case you want to use multiple filters you can do that by applying a comma here and then adding your second or third filter whatever it is right and also one more thing to note is if you want to use a measure inside your filter you cannot directly apply that measure here in that case you will have to use the filter function here and then use the measure that you want to use we'll see an example of that as well so I'm going to create a new measure and it will be a simple measure where I'm going to assign it a value maths now let's say I tweak the requirement a little bit I want to see the total score attained by B in subject object math okay so I'm going to use a second filter criteria and this time I'll try to use a new measure that I created now if I press enter it will give me an error that is because in this filter parameter I cannot directly use a measure in case I want to use a measure I would have to encapsulate that inside a filter function so table would be master and the filter expression would be this one is the first expression and if you remember we learned about operators so double ampon this signifies an and condition so I'm going to say filter master table name is equal to B and I've clubbed it with another condition subject is equal to measure measure is nothing but the text that we entered into that measure which was maths now if you see it has given me the score attained by B for maths in both the semesters now I'm going to just remove this filter here and I'm going to just use master table name is equal to B now since you got an understanding how calculate Works let's understand in which scenario do we require to use all alongside calculate so I'm going to drag in another Visual and this time a matrix and here I'm going to create another measure or let's say we can utilize this measure only and I'm going to add both semester 1 and semester 2 scores together sum of sem 1 score plus sum of sem 2 score okay and I'm going to name it as total score and then I'm going to drag it here as well and then remove semester 1 and semester two score because I don't require it anymore now let's say you want to identify how much percentage does each student have in comparison to the grand total that we have here now for this scenario you might think that we can straight away use percentage of grand total from here right but if you use that this will be creating an implicit measure if you remember we covered what is the difference between implicit and explicit measure in my previous videos right so you understand that implicit measure are not that flexible to use so it's always better to have an explicit measure so that you can drill down into multiple columns and multiple values without any difficulty so for that to happen there are two things that are required first one one I would need a total count somewhere as in a count of this 1,418 somewhere so that I can divide 327 with that grand total to get my percentage because this total exist separately you cannot refer to this total anywhere because this is something that is created by powerbi as an implicit measure and you will not be able to use this in your calculation right so to use this in your explicit measure create a new measure I'm going to call it grand total I'm going to write calculate and then I'm going to use this new measure that we created which was total score and in the filter criteria we're going to use all okay and what all does is it removes any other filter context that is already there on that table so right now in this table the values are splitted on the basis of the name column right so each student has their own particular row item and we do not want that so that's why we're going to write all and inside all I'm going to use this table name which was master table that's it now I'm going to use this grand total inside this table so that you see what's happening so as you see now in the grand total I have this, 1418 but our work is not done I'm going to change the name of this measure grand total percentage and I'm going to use total score divided by the grand total now if I apply and convert this into a percentage you will see out of the total marks these students scored a has 23% of the total and B has 28% C has 23 and D has 26 cool right so this is how you can Club calculate and all together to supersede any particular filter or grouping that we already applied on a particular table or a [Music] visualization So today we're going to look at another important and interesting Dax function which lets you utilize the relationships that you build within your data model and utilize that relationship within your Dax function the Dax that we're talking about today is called related and if you remember a couple of videos back we created an entire data model I showed you how to create a data model right so we're going to use that same example here as you see in my data model section I have couple of tables which are connected to together to create my entire data model Universe okay okay now I'm going to show you how we can use the related Dax function and utilize this data model and the relationships that we built in that data model to get values okay so this is how the related function works so I'm going to insert a new column here in this new column I'm going to write related and this is how the Syntax for related goes so you just simply write related and you give it a column name as simple as that but if you notice right now I'm creat creating this column in the pizza sales table and when I wrote related it is giving me all the columns from other dimension tables as well that is because we already established a relationship between all those tables and I can refer to any column from that another table using the related Dax function okay so this is how so let's say I want to get pza name from the pizza code Dimension table this is just a basic how related function Works related works on a row level basis right but applying related like this goes against everything that we have seen so far as in in terms of data normalization right because it is simply creating a redundant data in my table which I do not specifically require because if I've created a relationship you can simply drag and drop these columns from the other table into any reporting layout or visualization you want so why to have this here right but because related function works on a roow level basis you can utilize this related function in a more efficient way by clubbing it with another Dax function okay so let me give you an example so I'm going to say I have to create a new column and in that new column I have to check if a particular Pizza name is Deluxe or not if it is then give the value of of one else give the value of two okay so for that to happen obviously I need the IF function but when it comes to The Logical test that we need to build because my name of the pizza is not existing in this current table and it is somewhere existing in another table which is related to this table I can use a related function so here it goes so I've written if and then related and I'm going to give the name column name as p a name this is what I need and I'll say equal to Deluxe then if my logical test turns out to be true because it works on a row level basis it will go one row at a time and it will check if it is Deluxe or not if it is Deluxe then I'm need one else I need two and press enter so as you see now this makes sense right now I've created a new information column here utilizing my IF function and I've clubbed related Dax function with if to create something new one more interesting use case for related function is when you want it to return a table okay so let's do that I'm going to go to that table tools here at the top and I'm going to create a new table okay and this in this new table I'm going to write a dask which is called filter okay now what filter does is it returns a table with certain filters that you apply okay so I need it to return pza sales table the entire table and the expression being related Piza name equal to Farmhouse so what it did was it filtered out the data from Pisa sales table using the filter that we provided the filter parameter was simply saying Pisa name should be Farmhouse but if you remember Pisa sales table does not have pza name column so we cannot directly apply uh pza name equal to Farmhouse right that would not work so instead you would have to use a related Dax within this so we have clubbed uh related with filter to get this result also you can utilize related in inside other Dax functions like sumx or calculate and we'll see examples of those in future videos but this is how you should be using related instead of Simply calling it inside a table to create a new column use it in combination with other Dax functions to create interesting output so that you don't create a rendant data and it does not impact your data normalization as well [Music] right today we're going to cover a specific function in Dax which is called related table for covering today's topic I have created a dummy data and that dummy data has four tables in it this data contains particular information about students of a particular school now the first table contains the date of birth for couple of students with their names the second table contains the role number that they have in their classes with their names the third table is the score that they attained for English maths and Science in semester 1 and the second table is again the score that they attained in English maths and Science in semester 2 so the main topic for today's discussion is the difference between related function and related table function to help you understand in a better manner I'll show you examples of each differences with couple of scenarios and the first difference that is there between both the function is the type of data that it returns related function returns a scalar value so when you apply a related function it gives you a single value okay however related table as a name implies it returns a table instead of a single value so let's just quickly see how it impacts us okay so before doing that I'm going to go to my modeling section here and I'm going to connect these two tables so I've connected these two tables the data of birth table and the role number table okay so that we can write our related functions now so let me go to this table date of birth table I'm going to insert a new column I'm going to write related and I'm going to fetch role number from the other table now since my data set is small I'm not worrying too much about data normalization so that's why I'm fetching the column directly from that table as you see because related function returns a scalar value I have couple of values here now let's see what happens if I use related table here instead of related now related table does not give you an option to give a column instead it gives an option to give a table right so that's why I've used RO number here and when you enter this function here you will get an error that is because since this function returns a table it cannot be converted into a scalar value that's why it's giving me an error so how will you use related table then in that case in order to use related table you will encapsulate related table inside a iterative function like for example I can use this in sux or I can use this in count rows or something like that right so I'm going to use this inside a sumx function so it has given me the role number for each student name now coming to the second difference cardinality if you've seen my previous video we have written the related function inside the fact table and fetch the information from the dimension table and not the other way around that is because related function Works only with many to one or one to one relationships so I'll give you an example I'm going to join this semester one score with role number table here so now I've created a many to one relationship here so many being in the semester table and one being in the role number table now if I try to apply the related function inside this so I'm going to say related and I'm going to fetch let's say role number right it will give me that result okay also if you noticed we created a relationship between only the role number table but it is giving me an option to select the column from the date of birth table as well and it will give me correct results for that that is because related function follows the many to one relationship as long as the chain extends so although we created this relationship but this table has a relationship with date of birth table as well that's why we are able to fetch information from there but what happens if I do this other way around so let's say instead of applying my function inside the semester one table I applied my function in role number table and I say related and in the column name it's not giving me the option to select anything from my fact table or anything from my semester one table okay that is because as I said the related function works from many to one not from one to many okay so that's why this will not work but on the contrary related table function works either way so related table can work from 1 to one one to many many to one or many to many okay so it can work on all Direction any cardinality okay that's the difference now if I want here I can use related table okay now I'm going to get if you see I can select semester one score however selecting a table like this does not make sense because it will again give me an error because it's returning a table I want a scaler value so I can encapsulate this inside a sumus function so it is giving me the total of scores clubbing every score together for each student so let's just see how this will work in a many to one relationship so right now we just saw how it worked in a one to many but in a many to one relationship I can go to my score here create a new column and I'm going to write related table and I'm using role number here although in this scenario I can use related as well that will work just fine okay so it has given me the role numbers for each students even though we had many records in this table now let's see an interesting scenario where related table works on a many to many relationship which is not possible using the related function to create a men to many relationship and to make our lives bit more easier I'm going to go to transform data and I'm going to create a a unique key in these tables so I'm going to merge these two columns together so that we can create a unique key okay so to merge you can just go to transform and click on merge columns and I can choose a delimiter so I'm just choosing a delimiter here and press okay it has merged both the columns together similarly for semester 2 score as well I'm going to select both these columns click on merge column select a d limiter and okay okay so now both of my columns H have a unique column which we can use to create that relationship okay so I'm going to close and apply now in the modeling tab as you see the moment we created that key automatically power detected that relationship and applied that okay so we are good with this now I'm going to go back to this table here and I'm going to create a new column and I'm going to write related table and the table being semester 2 okay and I'm going to use a sumx function because we need a iterative function to work with a related table function so I'm not going to write any complicated expression here I just need the semester 2 score here and enter so as you see it has fetched the correct results for me uh and if you want to just check a English is 48 a maths is 55 a science is 55 so I'm going to just go to semester score 48 55 55 so it has fetched the correct results for [Music] me the topic that we're going to discuss today is yet another Dax function specifically in the relationship functions family just like the ones that we covered in previous videos like related and related tables this one is called cross filter now I'm going to show you how cross Filter Works and why do we actually require cross filter but before we begin let's understand the data set that we're going to use today it's the same data set that we used a couple of videos back which was the pza sales data I'm only using three tables here so that it's easier for you to understand the cross filter functionality in a better manner the links for the data set will be in the description below so you can download that data set and follow along if you want the first table in this is uh date table which contains uh three columns one for date month and day right the second table is again a dimension table which is pizza Code table again three columns Pizza code pza name and category of pizza and the third table is a fact table containing three columns which has the sales value for each pza code on a particular date so without wasting any time let's just quickly jump into cross filter functions before we actually write the Dax it's important to understand why we need cross filter okay so for that I'm going to go to this modeling Tab and show show you the relationship that I have currently built for these three tables now in the center you have the fact table which will have multiple records for any single category or a single date we have multiple row items then we have Dimension tables uh namely date and pza code each one of them has unique values in it so when we connect it it creates a one to many relationship so from Pisa code to Pisa sales Pisa code being the one part of it and pza sales has many records in it and you can see that here as well it says one on this and it says star here right so which means one to many you might remember from my previous video when you build a data model when you create relationship between two tables this arrow is pretty important this signifies the type of relation these tables have with each other whe whether it's a single directional relationship or a both directional relationship now that is important because when you apply filter to a certain table if it's a single directional relationship the filter context flows from the one to many which is indicated by this Arrow sign so wherever this arrow is pointing out the filter context will flow in that direction so in this case this P it will flow from pza code to pza sales and same goes for the dimension date table the filter context flows from dimension table to sales pza sales table now why is that important now I'm going to go to my data tab view here so let's say the requirement is I need to create a table where I have date values and adj just into that date value I want some of sales which will come from this pza sales table and I want another column which will give me the count of unique pza names sold in each date I do not need the count of pizza code I want the count of pza names which is existing in this pza Code table now remember the filter context is Flowing from Piza code to Pisa sales not the other way around okay so let's see what happens if we try to create that table as it is okay so I'm going to drag this date column in the reporting View and I'm going to convert it to date and I'm going to drag this sales from here okay I'm going to convert this to a matrix and then let me create another measure okay in this pza code count rows pza code okay from the dimension table now let us drag this here and if you see the count that it gives me is five for each and every item that is because in the pizza Code table I have five types of pizza right and it's giving me a straightaway count it's giving me a count of all basically and not applying any filter context that is because the filter context is Flowing from Pizza code to pizza sales and from Dimension date to pizza sales so when I use a dimension from the dimension date table the filter context flows from this table onto this table which is the fact table but stops there it does not flow through this fiza Code table when we are trying to use a column from this Pisa Code table that is why it's not giving me the correct number but what happens if I double click on this relationship and instead of a single relationship I make it both now if I go back it it is giving me the correct count so let's check what's happening I know that in pizza sales table on 1st of January I have sold three types of pizza namely Farmhouse Deluxe and double cheese which is indicated by this pza code now you might think that in this specific scenario instead of counting the pza names I could have simply counted pza code from here and that would have solved the problem and you're right but I'm trying to explain the scenarios where you would not have that flexibility and you would have to count some other column which does not exist in your main fact table so in that case you would have to use either this method where you're making it as both but the problem with this method is if your data mode model is complex enough and you do not want to make any changes just to do this one calculation then you will find a challenge because just to do this one calculation if you're trying to make a cross filter Direction change in your entire data model that can have weird behaviors in other calculation that you're doing right and you do not want that so I'm going to convert it back to single again okay and I'm going to come back to this reporting View and in this measure I'm going to encapsulate this inside the calculate function because cross filter does not work alone on its own you would need a function something similar to calculate okay so I'm going to write calculate and in the filter section I'm going to write cross filter and the column that I'm referring to right now is pza code from Dimension and pza code from pza sales because these two are the foreign and primary key which we use to create our relationship now in the third parameter here which is cross filter type you have five options both none one way one way left filter right and one way right filter left now in this specific scenario we only need both so I'm going to write both and close the bracket and press enter now as you see it has given me the correct number and it has the same result as if I have manually gone here and changed the relationship to both but as I said scenarios where your data model is complex and you do not want to manually make any relationship changes here because you do not know what negative impact it could have on other calculation that you're doing in those scenarios you can use this cross filter function so with that we have finally concluded our Dax masterclass video in case you found this video helpful then please consider subscribing to the channel because it takes lot of effort to create such videos for you and I need your support and motivation to help this channel reach its full potential now in case you have any suggestions for future videos do let me know in the comment section I'll be happy to take that up next time and as always thank you so much for watching I will see you in the next [Music] one

Channel: Pivotalstats

Views: 37,291

Keywords:

Id: 4rC9Ow76n0U

Length: 86min 33sec (5193 seconds)

Published: Mon Oct 30 2023