👉Beginning Power BI DAX Functions Tutorial [Full Course]

all right we had some technical difficulties so i get to do it again hello my name is mitchell pearson and today we're going to be doing the dax workshop presented by pragmatic works if you want to reach out to me either through email or my youtube channel or through my blog please feel free to get that contact information that you see on the screen if you like what you see today i do a lot of bite size kind of power bi and dax videos on my youtube channel and i'll be releasing a new one every week so subscribe there and you'll be able to see a lot of those as well i am the training manager here at pragmatic works my role includes building new classes for pragmatic works as well as delivering live training events similar to this not quite as huge as this event but similar to this in our live dax boot camps and other boot camps that we do i'm super excited to be here today we have people from all over the world in the chat so thank you for being here and attending this workshop and i hope it's helpful for you all right we're going to jump right in and we're going to talk a little bit about logistics for this this is going to be a three-hour workshop we'll have a 15-minute break around 12 25 eastern time so that's going to be in about an hour and 20 minutes from here and then this session fortunately will be recorded i know that's going to be one of the number one questions that we get this will be recorded and you can find that online at the youtube pragmatic works youtube channel questions good we're gonna have a lot of questions coming in throughout and so in order to make sure we don't disrupt the flow of this workshop too much i'll try to answer as many questions as i can after we take that break and then we'll do a follow-up blog and possibly a youtube video that's focused just on those questions specifically as well if you have a very targeted specific question once again please feel free to send me an email and that brings us to the next thing here which is following along in your class files which i'll talk about on the next slide i've provided you the files you would need to try to follow along today but i kind of would discourage you from doing that only because it's sometimes it's hard to follow along especially with the programming language and i don't want you to get frustrated this is recorded you're going to have all the class files so you can go back through this at your own pace and go back through all of the demos that we provided but if you want to follow along you definitely can i would encourage you to have at least two monitors one to watch what i'm writing and then one so that you can work on yourself and then of course let's talk about the class files so in the files that you downloaded and if you didn't download them yet my co-workers are going to put that in the chat window for you right now there's going to be about five different files that exist in there one is a readme file that just tells you about the different files one is going to have the dax workshop slides in a pdf format so you have that available as well and then there's going to be a code examples file i was going back through the demos before today this morning in fact and i noticed i wanted to change a couple of quick demos around so i did and then i re-uploaded that to the class files the attendee files and so if you have one that says dax workshop code examples that's the first edition so it's not exactly the same as the demos i'm going through today so you'll want to download that again and then we have a data model now that data model is the starting data model so you can follow along if you want or go through these in a review fashion after the class is over so you'll open up that data model and then you'll just follow along with the examples that i'm doing whether that's live or in the recording afterwards and then of course i provided for you a completed example so maybe you just want to open up that completed example jump right in look at the code that we're doing that'll work great as well however if you want to open up either one of these files they were created in the september 2020 version of power bi desktop so you would need that in order to follow along or in order to open up those power bi files to get the most out of this class you do not have to follow along just come along the journey with me and we're going to write dax and have fun all right so let's take a look real quick at the agenda we're going to be taking a look at just dax fundamentals a lot of the people that are in here today are going to be brand new to this language called dax so what do we use it for calculated columns calculated measures what are they and when do we use those we're also going to start kind of building a foundation here for authoring more intermediate and advanced level dac so what is row context and what is filter context those two items together make up something in dax called evaluation context and then we'll do some stuff around time series analysis so we'll take a look at year-to-date cells and prior year calculations as well and then semi-additive measures and then we're going to dive into context transition ever so slightly at the very end if you like what we're going through today and you really enjoy this format i would encourage you to take a look in the description of the video link at our dax bootcamps that we have coming up here in the next couple of months and maybe you sign up for that and you get three days of hands-on experience in a more intimate environment where we can ask questions and work together all right so that's the agenda for today let's jump right in we got a lot of material to cover what is dax well dax stands for data analysis expressions and this is the expression language that we use when we're working in power bi desktop now today i'm going to talk a lot about power bi desktop but many of you are probably not even in power bi desktop maybe you're working with ssas tabular models or powerpivot when i first started working with dax it was about seven eight years ago and it was when i was actually building analysis services cubes in microsoft sql server technologies right it was before power bi desktop was really a thing so you can definitely use everything that we're talking about in this workshop today whether it's in powerpivot for excel or in ssas tabular why do we have dax microsoft has lots of programming languages right well dax was designed specifically to support a larger user base and microsoft did an incredible job with this you look at the hundreds of thousands of excel users across the world that are already familiar with the functions in excel and dax is very similar to that at least when you get started and so this is designed specifically to help those data analysts get up to speed a lot quicker now this is a great tool for us i t professionals as well but it's really an end user tool that's why we have dax it it's just such a better language than those traditional languages as far as being able to learn it and get up to speed very very quickly which i hope to demonstrate in this workshop today so what is dax used for primarily we use dax for calculated columns and calculated measures calculated columns are where we go in there and we add new columns to a table typically we do that so that we can describe our data a little bit better right so it adds analytical value by giving us additional ways to describe our data for example if i'm looking at total sales and you want to see sales by age demographic or by age breakdown then we would go over to our customer table and if we don't have that column already we would create that new column and that would give us a descriptive slicer that we can use in our reports i recently did a video on youtube around the last working day how do we always show our power bi report to show the last working day when we come back to work or we come to work the next day that also is a great example of building a calculated column in data analysis expressions another common use case for building calculated columns is when we want to build a relationship between two tables in power bi or in the tabular model and we do that by maybe having to combine multiple columns together first i'll talk more about that when we get to that demo here in just a little bit we're also going to be using dax to build calculated measures now calculated measures is really where you'll spend the bulk of your time writing dax and this is where we build those dynamic aggregations so things like total sales and product cost and profit and margin and all of those kind of metrics like that we also use calculated measures to build ratios and percentages to build time intelligence calculations year-to-date cells year-over-year cells prior-year cells so if you want to do analysis across time we do that with calculated measures and then there's also this concept of kind of complex relationships in dax and so we handle those also with calculated measures one point on calculated columns that i want to talk about from a best practice perspective it is recommended as a best practice when you're working in power bi that if you can you actually create those columns before you bring them into the power bi desktop application only because it can give you better compression and so one of the things we recommend with that is if you can build it back in your sql server table back in your excel file or in the power query editor build it further up stream before you bring it in you can get better compression from that but if you have a small data model you're building a proof of concept don't worry about it just go ahead and use dax that'll definitely work fine as well all right and then also you can use dax to create calculated tables calculated tables aren't something you'll do quite as often as calculated columns and measures i like to use them for a couple of purposes one maybe i build a calculated table that's an aggregate of an existing table so i get an aggregate table that helps me to improve query performance the other thing that i'll use a calculated table for might be to build a date table so if you don't have one that's a quick and dirty way of kind of getting a date table in your model and also i love using calculated tables just for being able to debug and validate my code unfortunately today in this three hour workshop we won't be jumping into calculated tables because you won't be doing that much with them anyway so let's jump to the next slide and take a look all right we don't have a lot of slides in this workshop i think i have 14 total so don't be too worried about this with navigation functions what they allow us to do is they allow us when you're building a calculated column specifically go over to another table grab a column from that table and add it to the call the table that you're currently on and so navigation functions are very similar to like a vlookup if you come from excel very similar to a join if you come from a sql server background and so when you're using navigation functions there's really two functions that are available there's the related table and then the related function and we'll take a look at both of those in our demo as well the one thing that's kind of tricky about this for those of you that have been working with dax for a little bit and you're used to that automatic kind of filtering that happens through the data model then you build a calculated column you're like wait a minute it's not automatically filtering what's going on we're going to talk about that as well but that's why we need these navigation functions to go over there grab that data and bring it over now keep in mind we're not covering the full breadth of everything you can do in dax here today navigation functions you can use inside of calculated measures as well but that's another topic all right requirements for using navigation functions a relationship must already exist within your data model to use them if a relationship does not exist then you need to go ahead and use it or create that relationship in advance or you would use some other function like the lookup value function keep in mind related and related table will typically perform better from a performance perspective than lookup value so build a relationship and use related if you can all right so we're going to jump right into our power bi model now and we're going to write dax this is the exciting part here and we're going to start off with some pretty basic examples but we're going to go a little bit deep pretty quick as well and that's for the more advanced audience that we have here so let's jump right in to power bi desktop and take a look what i've done here is i'm opening up the power bi desktop file that you saw when you downloaded and you saw in my powerpoint presentation so this is called dax workshop dash data model once again if you want to open it up you do have to have the 2019 2020 september version of power bi desktop to do so so with power bi desktop open the first thing i want to do is we're going to create a new calculated column and so i am over here on the very left side of my screen going to click on the data view and when i go over to the data view i'm going to create a new calculated column on the customer table and this will give us a chance to just review some of the fundamentals of writing dags so i'll right click on the customer table and when i right click right here i'm then going to select new column and that's going to give us an opportunity right here in the formula bar just to start writing dax the first thing that we want to provide here is going to be the name of our column so i'm going to type in here full name equals and then i'm going to do a single quote now one of the great things about dax is that automatic functionality where it auto-completes your code for you or it gives you intellisense and whenever you see intellisense that's a good sign that you're writing your code the right way and so i'm going to go ahead and hit tab on my keyboard to let that auto-complete and then i'll do an open square bracket and then i'll type the first letter of the column that i want which is the first name column and then i will hit tab again to let that auto complete now in that dax workshop code example that i gave you if you're following along you can just copy the code out and paste it right in here without having to try to type it and maybe you miss a comma or a closing parenthesis that's why i provided that file for you so we have the customer first name now we want to add a space and then we want to add the last name so the way that we can combine strings together index is we use the ampersand operator which is just above the seven key on my keyboard and then we're going to add in a blank space here using the double quote so double quote space double quote and then we'll add another ampersand sign and now i want to bring in the last name from my customer table so we're going to bring in the customer and then right there we see last name so i'll hit tab again and that's it that's our very first dax example and as you can see it's pretty straightforward so the great thing about calculated columns is as soon as you kind of complete that expression and hit enter you can see the results right away in your table here and so right away we see our results and we know it's working this is awesome this is a little bit different than calculated measures which we'll talk about when we get there but now we can validate that but now i'm going to ask you the first question of the day when we created that calculated column here customer first name how does it know to take the first name from this row because technically in this customer sells or this customer table we have 18 484 rows how does it know to take that first name from that row uh how come it doesn't choose some other first name and it seems like a very intuitive and obvious answer right of course it does because i'm i'm creating a row on the side it does that in excel but what's happening when you create a calculated column is it creates something called a row context and that row context allows the calculated column to essentially perform operations on that table one row at a time so it goes to the first row of the table it performs that expression and since it's only performing an expression on that row and it only sees that row that's how it knows exactly what first name to use and exactly what last name to use and so at its core row context is actually a really simple concept now maybe you've read a book or you've watched some videos and you've you've gotten a little mixed up on different evaluation context in this workshop today i want to cover the foundation and kind of the fundamentals around that so that's the first thing whenever you create a calculated column a row context is created and it works on one row of the table at a time in programming we call that iterating over a table there's another thing i want to talk about here that's very important whenever a row context exists the filter that is on the row of that table is kind of deactivated this is a good thing sometimes it's a bad thing but we're going to talk about that so for example what do you mean filter that's on that row well when you're working in power bi you create relationships in your data model and that tells power bi how to filter tables right so for example if we come over here and look at our data model real quick let me just simplify this ever so slightly we'll notice that we have a relationship from our date table to our internet sales table on the date value so we've built a relationship where the date table filters internet sells automatically on that order date however when we're working within a row context that relationship is almost non-existent and it's been deactivated in some way which you'll see here in just a moment and we're going to talk about why we need those navigation functions to bring that relationship back to life so let's go back over to our table real quick and back on the customer table we've created a row context because it's a calculated column and that active relationship has been deactivated keep in mind that most people when they think of row context they immediately go to calculated columns but row context also exists when you're working with certain functions in dax as well so the filter function what it does is it works over a table working on one row at a time perform an expression on every row of that table it also if you're working with x functions they do the same thing their first parameter is a table so they also will work on one row of that table at a time and that also performs a row context if you understand and know when a row context exists that's going to help you write and author more advanced tax calculations all right let's jump into our next example here another simple example and we're going to on our customer table we want to be able to analyze our data by age breakdown so in this table we currently have the age column and that's not good enough we want to take their age and we want to bucket that if you will we're going to kind of bucket that into an age bucket so if you're over 55 you're 55 plus if you're over 45 or 45 to 54. so let's create a new calculated column on our customer table once again all the way over here on the very right i will right click on the customer table and i'm going to choose that i want to create a new column so i'll create a new column here and in order to build this age breakdown calculation we are going to use some conditional logic in dax so this will be our first look at that here today we'll create an age breakdown calculation and we're going to say that age breakdown equals by the way whenever i go down to the next line in dax i always use shift enter this kind of auto formats your code and it's really awesome and as you see there i actually commented out my code as well and that commenting of code is ignored by the data modeling engine when it processes that expression so now i'm going to tell it if i'll hit tab and then shift enter down to the next line another really cool thing about dax is that intellisense that you see right there whenever you're using a function in dax it gives you a description of what that function does and it tells you what parameters are expected so the very first parameter here is a logical test and my test is going to be if the age of the customer is greater than 55 then they're 55 plus so we'll type in here if the customer and then age is greater than or equal to 55 then that customer is going to fall into the 55 plus bucket and then we do the next one right so our next condition is if the customer age is greater than or equal to 45 they're 45 to 54. so the thing about this actually i'm going to clean this up and format it a little bit better here and then i'm going to go down so just so it's easier to read and so we're going to say if the customer age is greater than or equal to 45 then the customer is 45 to 54. and then we'll do another condition here right so if that evaluates to false if they're not equal to you know if they're not greater than 45 if they are 45 to 54 if they're not then we're going to do another conditional operation here so then if the customer whoops age is greater than or equal to 35 then they're going to fall into that 35 to 44 bucket right and then if they don't meet any of those criteria we're just going to say everybody else is 18 through 34 so that's our ultimate result if false criteria there and we'll say everybody else is going to be 18 through 34 double quote and then because we have a bunch of nested if statements you probably know this from excel we need one closing parentheses for each one of those which in this case is going to be three now let's take a look at this code real quick i'm going to hit enter so we'll be able to see the validation of that over here on the right now the astute student would probably notice that when you get to the second pass right here that we're not actually adding an upper threshold right we're just saying if the customer age is greater than or equal to 45 they fall within the 45 to 54 bucket but you might know that if somebody were 57 they're greater than or equal to 45 if somebody were 90 they're greater or equal to 45. so is this expression correct well the answer is yes yes it is because the way that dax works is it uses the first pass rule so any of those customers that are 55 or older when they went through this very first condition they were eliminated from the result set so then when we're looking at the next result here or the next expression we only see the remaining customers that were not assigned a value in that first pass so this is a valid expression and now we can go over to the right side and we can see right here the result and we can do a very quick breakdown here so all of these appear to be 35 through 54. this one is an exception that's 45 through 54. and so you can go through and do a quick test but this is our first conditional operation here in data analysis expressions all right so like we talked about before one of the main reasons we create calculated columns is because we want to create new columns on our table to help us to really add analytical value to our data model so we can describe it in different ways and slice our data in a number of different ways let's create another calculated column this time it's going to be on the date table so over on our date table we have another user request another business requirement and this time i want to create a new column on the date table so make sure i right click on that create a new column and i want to create a two digit month and a four digit year so we'll call this our month year and this by the way will look great there's a couple of reasons why you might want this one is because it could help your dax calculations when you need to be much more specific in a kind of authoring a more intermediate or advanced level calculation also this is going to look great on like a column chart when you're looking at data across time because you'll have the two-digit month and the four-digit year together right there so our goal here is to return the two-digit month month month with a dash and then the four-digit year a really easy way to do this in dax is by leveraging a function called the format function once again as soon as we type in that function i hit tab to auto complete the definition of it is it converts a value to text in the specified number format essentially what this does it'll it'll let you take either a date value or a numeric value and format it in a very specific way now there is a date there is a side effect of format which is it will take that value and convert it to a text value so it does do like a data type conversion here as well and so i'm going to do shift enter to go down to the next line and then i'm going to provide the value the value is going to be the date column from my date table and we want to take that date value and we just want to extract from it the two-digit month and the four-digit year now we're going over format very quickly here but as you explore this yourself you're going to find out this is extremely customizable so we're going to do a four digit month we'll add just a regular string dash right there in the middle and then we're going to tell it that we want the four digit year closing parenthesis right there at the end and this gives us exactly what we want for our business requirements if i did the three digit month here right so let me put a little comment if i did the three digit month this would return for january j-a-n if you did the four-digit month it would actually return the full month so you would see something like january right there and so it's a very very flexible solution so we'll go ahead and hit enter and then we'll go all the way over to the very right right here and we'll see we got exactly what we wanted the two-digit month and the four-digit year now as you explore dax and you get more familiar with it and you get really more adept with it you'll find out that you could have written this expression in a couple of different manners and that's all part of becoming better at writing a language right and then you start getting more adept at it you start performance tuning and that's kind of that next level here but this is a great solution for what we wanted in this situation all right we're going to go and jump into another challenge that we want to solve and this is around the data modeling piece we've been very fortunate pragmatic works to partner with microsoft and doing a lot of dashboard in a days um really internationally whether it was live or through virtual events like this one here and a lot of times when you're in a dashboard in a day you get a lot of great questions for people that are brand new at dax and one of the problems that people have a lot of times when they leave dashboard in a day is they're pulling in data from a lot of different data sources how do i connect that data together that's a really challenging thing so i wanted to give you an example in this dax workshop just in case you kind of fall into that category so let's take a look once again at our data model and i'm going to jump over to our temperature table we're just going to look at the data real quick we want to be able to take a look at our total sales our profit our profit margin based on the temperature range so what is our profit margin when it's cold what is our profit margin when it's hot so we need to build a relationship between our temperature table and our internet sales table but in order to do that what column do we build that on fortunately for us in our temperature table we actually already have a key column that exists right here and so this right here is our key column and a key column especially if you're on the dimension side of the relationship and i'll talk about what a dimension is here in just a little bit but if you're on the one side of a relationship that key column needs to be unique so in this table what makes a record unique is the combination of the region and then the month number now we want to build a relationship to internet cells so let's go take a look real quick at our internet sales table so we'll escape out of there take a look at the internet sales table and on our internet sales table we don't actually have the region name and we don't have the month number those don't exist right now today on the table so instead what we're going to do is we're going to have to add those columns to this table so let me show you what we can do right here you'll notice that we have our sales territory key so if i want to know this transaction right here whether it happened in the united states or australia or canada or what region it occurred in or what zip code or what city we take that key over to the sales territory table we find cells territory kia four and we say hey this happened in the northwest region so we do have what we need here to essentially do if you were in excel what would you do you would do a vlookup if i was working in sql server i would just do a join to the sales territory table i'd grab the region and i would bring it over so how do we do this in dax well the way we do this in dax is we're going to add a new column obviously on this table so on the internet cells table all the way over here on the right i'm going to right click on internet cells and then select a new column just like so and this is going to be called our temperature key so i'll go ahead and name that real quick to temperature key and then how do we get that region from our sales territory table well if i start typing in cells territory here you'll notice that it doesn't quite work intellisense says wait a minute i don't know what you're trying to do and this is a good thing if you see this don't keep writing right because it's just not going to work i promise you that it's one of those situations where the intelligence is so good it kind of tells you when you're going down the wrong path even though there's an active relationship between the sales territory table in the internet cells table there is a relationship that exists in our data model we're creating a calculated column and that calculated column means we're working in a row context and as we reviewed before when you're working in a row context the active filter that's on that row is deactivated so dax doesn't see that relationship right now but we can tell it to go ahead and use that active relationship by using one of those navigation functions so i'm going to type in related and then you'll notice immediately i see the customer table there and if i start typing in cells territory the sales territory table pops right up so we're going to do that and then i'll do an open square bracket and then i'm going to grab the region from that table close that up and hit enter you'll notice this is a little bit easier than a join or a vlookup because we don't have to specify what that join is on or what the relationship is because that's already created in the data model related just takes advantage of the relationships that you've already built inside of your data model all right so this is related let's go take a look at the results over here on the right just to see if they look correct and this looks really good so if we click the filter here at the very top right we can get a distinct list right i click the filter right there we can get a distinct list of all the different regions that got returned from the sales territory table this looks good it looks like everything's working exactly the way that we expect it to so with that being done we need one more piece we're not done the other part of this is we need to return the month number from the date table now the you might think that you could just take advantage of the date that's on this table the order date and you could just extract the month from that and in some ways you're correct because right now our active relationship is on order date so that would work but you got to be careful with that because we want everything to be dynamic in dax and if we use the month number from the date table it's using the month number based on the active relationship so if you built this data model and you originally built it with the active relationship on order date you can go ahead and take the month directly from this table and you're totally fine but if you later on change that active relationship over to the ship date table then all of a sudden it's actually taking the wrong month from the wrong date if you hard code it here so the method i'm going to show you is a little bit more dynamic and i would prefer so look at this right now and here's a good example if you look at the first row that month number of year is 12. but if you look at the ship date that month number of year is one because it's different it was ordered in december it shipped out in january and this is part of a larger concept that we won't talk about today and this is role playing tables and so that's more of a dimensional modeling terminology how do we handle that in dax well we won't get to that today but that's a really good topic and we do cover that obviously in other classes so what i want to do is instead of taking the month directly from the order date we are going to tell it that we're going to do an ampersand sign and we're going to combine the region with the month number of year from the date table once again i can't just type in date here that doesn't work so we need to use related so we can take advantage of those active relationships in the data model so we'll type in related and then we'll type in date and then we grab the month number of year from that date table and you'll notice it looks like i'm typing really quickly but i love autocomplete it's going to save me one day from having carpal tunnel i just always hit that tab key and let it finish for me so this right here is going to give us that exact key that we need on this table now this does not solve all of your data modeling problems in dax right this solves one problem where one table was set up in this fact table the mini side of the relationship it just didn't have it didn't have the key that we needed so we went and got the key sometimes you'll have many to many relationships and you have to build a bridge table there's a couple of different ways that you have to handle these situations we don't cover them all today but this is one method this is one method that's very good so now if we go over here to the right we have a temperature key and we can build a relationship for now we're going to skip that step because later on i want to show you what happens when a relationship doesn't exist so a little bit later on in this workshop we're going to come back and we'll build that relationship but this gives us an introduction and it gives us some insight into navigation functions and what those look like all right let's take a look at another way of kind of working with this row context and then using navigation functions to get out of it so we're going to do this one on our cells territory table and this demo here is a little bit of an extra demo that wasn't in those original notes for the class so you want to make sure you download those class files again to make sure that you get that dax ready to go and so on the sales territory table we're going to create a new calculated column now here's the scenario our business users come to us and they say hey mitchell we want to be able to see our total sales our cost our profit we want to be able to see that based on the high volume region or low volume region or medium volume region well we look at our sales territory table we're like well we don't have an attribute or a column in that table that represents that that helps us to describe our data in that way so what we're going to do on this table is we're going to create that column but in order to determine if a volume if a region is high volume or low volume we either need to look at the total sales or total transactions or something like that so we're first going to create a helper column then we'll create that other column so let's focus on total transactions first and i think i call this one total transactions perfect so we're going to create a new column on our sales territory table called total transactions and i am going to just say count rows from internet cells and this is not going to work once again because we're creating a calculated column and because we're working in a row context so let's take a look at the behavior here you'll notice that when we create this new column and we're looking at cells territory key of one which is the northwest region it's returning 60 398 rows but it's returning that same value all the way down for every region this is a clear indication that filtering is not taking place in our data model not the way we might expect it to now we know just after a little bit of time in this dax workshop we know that the reason filtering is not taking place is because this is a calculated column we're working in a row context and row context has disabled this key right here right so we have a relationship in our data model from the sales territory table to the internet sales table on the sales territory key but it's not filtering it actively because a row context kind of turns that off so how do we solve that we are going to use navigation functions either related or related table which one you use depends on what side of the relationship you're on we are currently on the one side of the relationship in power bi the one side of the relationship represents that whatever you built your relationship on right here which was on the sales territory key that key in the one side is 100 unique that's what makes it the one side of the relationship a lot of times dimension tables right i'm going to talk a little bit about that now and i'm going to give you some context here when we're building a data model in power bi the best way to build that data model is kind of like what we call a star schema in dimensional modeling technologies right or that methodology and a star schema is a transaction table that's surrounded by descriptive tables a dimension table is a descriptive table that describes your data and it's a bunch of related columns so for example if you were looking at your transaction table and you wanted to see total sales by product you would go over to your product dimension your product descriptive table and you'd have all of your related attributes for a product in that table the product name the product color the product weight so on and so forth and so when you're building a data model in power bi it's very very recommended that you build it in this format because it's going to optimize storage and performance and it's going to make your data model flexible and when you build it in this way the descriptive attributes those tables there's always going to they should be the one side of the relationship your transaction table is the mini side because every time you sell a product it's going to be recorded in that transaction table so this is the one side of our relationship the transaction table is the mini side so since we want to go and get rows of data from the mini side of our table in our relationship we're actually going to use related table and what related table does is it returns a table that has all of the rows and all of the columns filtered down by the active relationship in the data model so this right here will return all of the associated records for sales territory key of 1 from the internet cells table which means it's not going to be all 60 398 rows it's just going to be the 8 000 rows for that region so now once we have this table we can now do our aggregation on that table expression so we're going to count rows like so hit escape right there to get rid of that intellisense and then let me format this just a little bit here so it's more readable and now that is going to actually give us the exact result that we want so row context is a great thing in dax and it allows a lot of things to happen but if you're new this can be a pretty frustrating situation because all i want to do is go get total sales how do i get that or i would i just want to go get total transactions and we do that by using related and related table now later in the class at the very end we're going to talk about context transition it's quite possibly the most complicated topic in all of dax and so we won't dive deep into it but there's actually an easier way to solve this problem with context transition and we'll touch on that here at the very end all right so over here on the right what do we get we now get the exact results that we want because filtering is happening and it's giving us exactly what we want now this was the helper column what we actually wanted here is we wanted to build a column that helps us describe our data based on low volume medium or high volume region so we're going to now create another column in our data model and it's going to be using another conditional type column but we're going to use a different method i want to i want you to see things from a couple different perspectives here so on our sales territory table we're going to create a new column and this column is going to be called probably region volume let me check my notes there it is so we're going to create a new column here called region volume and then we're going to do some conditional logic this time instead of using if i'm going to use the switch statement if you come from a sql background switch is very similar to like the case statement in sql and so i'm going to type in switch and then we're going to tell it true there's a guy in the community by the name of rob coley he wrote a blog called the diabolical genius of switch true he wrote it many many years ago and it explains why this works the way it does but this is the expression that we're going to use and we're going to say if our total transactions so the column that we created on this table if our total if our sales territory total transactions is greater than or equal to seven thousand we're going to say that that is a high volume region right so we'll say high volume and then we'll do another condition here if our sales territory total transactions is greater than or equal to four thousand then we're going to say that it is a medium volume region which is perfect and then we're going to do one more kind of condition here and we're going to say look if the total transactions is greater than or equal to one then that falls into the low volume category right there and then if none of those criteria are met then we're just going to say everything else is in a and this is another one of those kind of dimensional modeling terms so i apologize because you might not come from that dimensional modeling background in fact most people that are working in power bi don't but what happens a lot of times is from a reporting perspective if you get bad data in and you can't assign it to a certain product or you can't assign it to a certain sales territory we still want to make sure we record that sale and we're able to report it tomorrow right we'll be able to show our boss we did have 10 million in cells but maybe we didn't know exactly what sales territory that was associated with because our data might be a little bit bad so what we would do is in our fact table we just assign that to in a not applicable or unknown and in this table we have that we have a dimension attribute or a member in this record that's in a it's not applicable we don't know so if it's in a you notice there were zero transactions associated with this um if it's blank we're just gonna say look it's not applicable so that's what we're gonna do there we'll hit enter real quick and this is another way of writing a nested if statement i think it's a little bit cleaner easier to manage and easier to maintain so i really like this method right here and then all the way over here on the right we can do a quick kind of validation here and see that it's working exactly the way that we want now when we're building those visualizations in our we're building those reports in power bi we can use this to kind of describe our data we can use this as a slicer we can put it on the x-axis in our report we can put it on our rows and our columns and a matrix this is going to work perfect all right so i think we're almost done with calculated columns we have one more example that i want to talk about here and it's another one that looks at row context and it introduces a new type of function in dax which i'm really excited about these are my favorite functions in dax and they're called x functions so let's go look at the scenario here we're going to go over to finding my place in my notes we're going to go over to our internet sales table actually let's go back to the customer table that's where i want to be all right on our customer table we want to know the last order date of a customer maybe we want to build this kind of analytics here so our marketing team can do something with it and you'll notice that on this table we actually have the first purchase of each customer so we already know when they made their very first purchase that's when we entered them into this customer table but now we want to know their last purchase as well so we're going to create a new calculated column and this is going to be our last purchase date now the way that we get the purchase date is or in order is we have to go over to the order table we find all of the records for that associated customer in the order table and then we get the last order for that customer so how do we do that in a calculated column well let's take a look so on our customer table and i'll escape out of there real quick on our customer table we're going to go ahead and create a new calculated column in dax so i'll right click and then select a new column there and we're going to call this one just something very simple like the customer last purchase date so we'll come all the way up to the top and type that in real quick last purchase date and then we want to go over to the internet cells table and we want to get the max order date from that table so if i'm new to dax and i don't know about row context and i don't know about that side effect where the active filter is disabled the way that i would originally write this is i would just say okay there's a relationship in the data model just go to the order just go to internet sales table and get the max order date for this customer so i would type in here probably something like max and then internet cells and then order date just like that and so i'm going to do that i'll type in max internet sales order date but what gets returned when we do that if no filtering is actually taking place where this table is filtering down internet sales that's going to return the very last order date from the internet sales table for every customer which that's not what we want right so let me show you what i mean there if we go all the way over to the right you'll notice that similar to our total transactions when we were in the sales territory table we're getting that repeated value again the repeated value is always a clear indication that filtering isn't taking place so not every one of our customers made a purchase or their last purchase on july 31st some of them made their last purchase six months ago or three months ago and so we need to figure out what's the problem here how do we solve that well we do know that we're building a calculated column and we know a row context exists and we know the active filter has been disabled so we want to activate that filter and we can do that with navigation functions so what i would do here is we're on the mini side of the relationship we're going to use the function related so i would type in related you guys have the completed example in your notepad so you can just copy that out and when i click on related um actually it's a related table we're on the one side i messed myself up there we're on the one side so what you do when you're on the one side is you go over and you use related table to get all of the associated records from that related table so we're going to return all of the records for customer one all of the columns and all of the rows now this becomes a little bit difficult in dax my favorite functions in all of dax are x functions min x max x sum x average x let's talk a little bit about what they do you'll notice that if i go to the very beginning here and i say all right we have a table now i want to get the maximum from that table the max function this aggregate function only accepts a column name or a scalar value so you cannot pass that related table that we just created you can't pass that into the max function as a as a value as a parameter you can't do that and so this is a situation where we have to use if you don't know what context transition is which we'll talk about at the very end you would have to use an x function in this situation so i would type in max x now because i've been teaching dax for many years i know that x functions can be really confusing so i'm going to take a moment just to explain kind of the breakdown of what an x function does i like to think of x functions as two separate pieces the first piece is the x part and what the x part does is it takes a table and it works on one row of that table at a time and it performs an expression on every row of that table so at the end you get kind of a single column table as a result that gets returned so in this situation we're going to say all right we want the x portion to work on every row of this table that got returned which is of course the related records for whatever customer we're looking at and for every row that you perform for every row that you're working on return this expression and the expression i'm going to return is going to be the internet cells order date all right so this is going to return a list of all of the order dates for each customer then after this part does its work after the x function is looked at every row in that related table and it's returned the order date for every row in the internet sales table for that customer then the max function comes in and says okay get me the maximum date from that list of dates so we're going to return the maximum date from that list of dates right there and we'll hit enter and then if we come all the way over to the right we can see that we're not getting july 31st of 2008 for every customer we're now getting the exact last purchase date of every customer here and so if you want to come up with something like this in dax it's really easy to do but of course the hardest part there is understanding that row context that exists and how to work around that with navigation functions all right this was a little bit of an intermediate level topic here so if you're brand new to dax i apologize for that i wanted to throw this in here and kind of get you familiar with it because it does have some familiarity with excel as far as being able to do that vlookup type functionality go over to another table get the data you need and bring it over and this is how we do that in dax so with that being done we're going to transition over to calculated measures and talk a little bit about that and so we'll cover the fundamentals there and then kind of build on that as well and then if we have any questions that we're monitoring the chat though let me know if there's anything a common theme there other than the fact that i'm going too fast and i need to slow down just a little bit which i'm going to do i'm so excited about dax i have a hard time controlling myself so i'm going to slow down a little bit i've seen a couple messages from somebody telling me to slow down and i will do that all right so let's jump back over to the powerpoint real quick and take a look at calculated measures in dax so with calculated measures we're going to talk about a couple of different things here one calculated measures are phenomenal this is one of the things that i think has made power bi desktop such an incredible tool it was a couple of years ago i was doing an architect type session with a company and they came in and they really didn't want power bi they didn't want it their customers kept asking for power bi but they didn't want power bi and what they did is they essentially took a platform they built a data warehouse on it and they gave their customers pre-built reports so it's a specific industry right and they wanted me to give them all the reasons why they might not want to use power bi as a selling point to their customers and so we go through this full day architectural session i'm trying to talk down power bi and finally i said you know you're having this problem where you're having to go teach your customers sql to try to modify existing reports because they want to make some changes but you can't teach them sql right they it doesn't work you can't do that in a week let me show you a little bit about what power bi can do and so i open up power bi real quick i create two or three measures and i show them how dynamic and how those measures are auto magically filtered and in the next two hours the entire mindset of that meeting shifted and they said okay we need power bi how do we build this into our tool so calculated measures are phenomenal and i think this is one of the reasons why power bi has exploded on the scene it's not just those pretty visuals it's how dynamic these measures are so in this section we're going to talk about filter context what that is what it does we're going to take a look at calculate which is without a doubt the single most important function in the entire dax language you want to learn calculate and you want to learn it quick and then we're going to take a look obviously at time intelligence functions doing that time series analysis and we're also going to review semi-additive measures as well so let's jump right in and take a look so filter context in power bi is simply or in dax rather is simply the active relationships that you built in your data model in the way that they filter down the tables right so if you have a relationship from your date table to internet sales then whenever you're showing your total sales amount and you bring in the year from your date table it automatically filters that down and so you don't have to write a bunch of different expressions to make that work it just happens automatically in power bi and if you're working with tabular it happens automatically there as well and so what constitutes or what makes up that filter context well it's really anything that's kind of in that report page or affecting that report so it could be if you're looking at a table visual or a matrix visual it can be any attributes right that you have on the rows any filters that you have on the columns of that matrix those would filter down that value if you have slicers which are just visual filters in your report those would also filter down that table or that measure rather it would filter down the measure if you have filters in the filters pane they're kind of hidden over there on the side they're also part of that filter context and then also when we're writing dax expressions as you'll see here in just a little bit we can also apply filters within our dax that becomes part of that filter context as well so we're going to talk about that automatic functionality of filter context and how that works but sometimes you don't want that automatic functionality and you want to override it or you want to change it or you want to modify it in some way that's what calculate does and this is why calculate is the single most important function in all of dax calculate allows you to evaluate an expression within a modified filter context so i say i want to see total sales but not for the current filter i want to see total sales for last year so take whatever the current year is and modify it to give me last year total sales or i want to return total sales but not for the country australia that's on the row return it for the united states instead and so calculate allows us to kind of modify or evaluate that expression within a modified filter context and it's really easy to use and we're going to go through probably 15 or 20 examples i think today where we're using calculate over and over and over again and just exploring it in a couple of different ways now we're going to use calculate to build things like ratios and percent of totals to build very specific scenarios where we want to compare values also to do time series analysis we'll use calculate quite a bit the syntax for calculate is pretty straightforward you're going to start with calculate and then you provide an expression that expression is generally going to be some form of an aggregation so return total cells for this modified filter context return total cost for this modified filter context the modified filter context you define in the filter section of calculate so usually calculate is going to be you start with calculate you have an expression which is normally an aggregation a lot of times we're leveraging a measure that we've already created and then just below that we have a filter where we're filtering that down in some way we're changing that filter context and so this is the default value of calculate once you use it a couple times it becomes very automatic and it's a really really cool thing the other thing i want to talk about we'll get to this before the end of the day this will be after we take a break today is we're going to talk about semi-additive measures semi-additive measures is in your data model that's when you have something that's additive across some dimensions remember we talked about dimensions earlier those descriptive tables it's additive across some descriptive tables but not all so most of the time they're additive across your your dimensions but they're not additive across time that's the most common scenario so let me give you a quick example if you're looking at mitchell's bank account on october we're in october so october 1st my bank account might have a hundred dollars you look at it on october 2nd i still have 100 and then when you get to october 8th i still have 100 in my bank account you can't add up my account balance for all eight days of october because if you do it says that i have 800 but i don't have 800 dollars i actually have 100 so that measure of my account balance is not additive across time however if you had a thousand customers in your database you could add that value that account balance you could add it up across all of your customers so it's additive across customers it's additive across other dimensions that you might have but it's not additive across time all right so how do we handle this in power bi we're going to talk about that inventory levels account balances those are both very common scenarios where you're doing semi-additive measures in dax and it's a very common one i see this all the time how do i solve that and then of course we're going to jump into time intelligence index we're going to do calculations like year to date prior year year-over-year sales you can do things like rolling three-month total rolling six-month total running total for all time and the beautiful thing about dax if you've done this in other programming languages is that you can do this not in a couple lines of code you can do this within just a couple of words of code and it's just an unbelievable i've done this in other programming languages i've had to write tons and tons of code in sql server to build out time intelligence and then multiple variations based on how users wanted to filter the data and then in dax i can write three words and i have all of my year-to-date across any way that customers want to filter that data which is incredible if you're doing time intelligence and i'll mention this again when we get into the demos here a little bit later there are a couple of requirements one requirement is that you should have a date table in your data model this is one of those another i told you we we get to work with a lot of people that are kind of brand new to power bi brand new to dax and this is one of the biggest problems i see is that they don't have a date table in their data model so their time intelligence isn't working the way they expected it to and they keep trying to rewrite the calculation but it's not the calculations that's wrong it's that they don't have a date table so you want to make sure you have a date table in your data model and that date table should have really two requirements the first requirement is that you don't have any gaps you don't have any gap so you say well we're not open on the weekends we don't do business during holidays we're not open on the summer so we're going to remove those dates from my date table don't do that you want to make sure your date table has every possible day that's available from january 1st to december 31st you also want to make sure that your date table spans all ranges so if you're bringing in transactional data that starts on let's say june 5th of 2017 then i want my date table to at a minimum go back to january 1st of 2015 and then go to the very end of this data this this current uh year as well right so all the way to december 31st of 2020. you might need more dates though i'm working with a customer right now where we're building out forecasting data across multiple different fact tables and doing some other stuff with that so we need dates that go a little bit further into the future there as well so sometimes you will need more dates but you want to make sure you span the range of all possible dates and you don't have any gaps and that's going to help out significantly when we start doing that time series analysis and data analysis expressions all right there's also a ton of different functions that exist in dax for doing time intelligence obviously we don't cover a lot of those here in a three hour workshop in our dax boot camp and our on-demand training we cover almost all of these we cover a lot of them because they're very powerful and they allow you to do a lot of really cool things within power bi and tabular models of course like i said i'm going to say power bi a ton just because that's what i get to work with most today but things like total ytd that's going to return my year-to-date sales same period last year that gives me my my measure whatever it is for last year first date last date things like parallel period previous year lots of really cool things we can do here and so what we cover today is just a really small kind of scratching of the service surface what you can do with time intelligence when you're working within the dax language all right so that right there just to let you know is the end of our slideshow i wanted to get through the slides very quickly in here kind of cover what we're going to cover and then we just want to write a bunch of dax so for about the next 25 or so minutes we're going to write calculated measures we're going to look at that awesome functionality of filter context and then we're going to go into calculate we'll take a break and when we come back we're going to spend the rest of the day talking about calculate how do we override filter context semi-audited measures time intelligence all that great stuff there so we're going to jump back over to power bi desktop here and back in power bi desktop when i create calculated measures i generally do that from the report view and the reason i do that from the report view is because unlike calculated columns you don't actually see the value of your calculated measure in the in the table right because it's dynamic so there's not a static value it changes as you change the filters within your report now i do want to put all of these measures that we create on our internet sales table and so i learned a really cool trick from somebody in the community matt ellington did a blog i saw it on twitter so i want to give a shout out to matt allington for this right here he went over to the model view and what he does is he'll take all of the columns in his table and actually put them in their own folder and i like this a lot because then my measures then my measures aren't getting kind of mixed up with all of my calculated columns now you can assign measures to their own folder as well but then you have to go to each measure and keep doing it so i like kind of doing the inverse here so i'm going to go over here and grab all of the columns in my internet cells table now in order to do this you have to be on the model view and obviously have a newer version of power bi desktop and so i'm going to grab internet cells here and go all the way down to the bottom grabbing every one of those columns and then in the properties window in the display folder here i'm just going to say hey put all of those columns in a new folder called columns and we'll do that and so they all get grouped up together now when we see our measures as we create them we'll be able to see those grouped together here as well and i saw that we had a question here or a lot of questions that came in about fiscal year to date and doing time intelligence around fiscal calculations we are going to talk about that so stay tuned we will cover that thank you for throwing that out there we will definitely jump on that as well now let's go ahead and create a couple of measures here the first measure we're going to create is going to be very basic very simple it's going to be the sum of sales amount from our internet sales table so on my internet cells table i will right click on internet cells and then i'm going to create a new measure right there then up here at the very top i'm going to call it total sales equals the sum of internet sales and then sales amounts so it's just i'm just summing up a column from my internet cells table and this is extremely powerful because i don't have to add a bunch of filters here for seeing it by 2005 or seeing it by a country because of the active relationships in the data model this is going to kind of satisfy almost all of our reporting requirements which is really incredible so we'll hit enter one of the things i'll always do when i create a calculated measure is i'll immediately jump in there and go ahead and apply some level of formatting so up here at the top we're under the measure tools menu i'm going to go ahead and give this a currency of english united states by clicking right here next to the currency icon and there we go and then we're going to create about three or four more calculated measures and then once we create those measures we'll put those into a table we'll put those into a matrix and start taking a look at filter context so that's the first measure that we want to create you'll notice that because we put all of our columns in a columns folder thank you again to matt allington we can now see our measures kind of separated from those right when we first create them i'm going to go and click on internet cells again right click we're going to create a new measure this measure is going to be our total cost and this is going to be the sum of total product cost so it's another column we're just going to sum that up from the internet sales table so sum internet sales and then total product cost just like so and we'll hit enter again and then we're going to format it again so like i said we're going to be doing just a couple of really basic aggregations here now there is something else i should talk about fundamentally when you're writing dax in the dax community it's generally considered a best practice whenever you reference a column that's in a table that you always put the table name in front of it there's a couple of reasons for it the most obvious is that a column name is not necessarily unique to a a specific table you could have the same column name in multiple tables so in the dax community it's generally accepted as a best practice put the table name in front of it whenever you reference a column from a table so that's what we're doing here i put the column or the table name in front of it and then we're going to go over here and format this one also as english currency united states perfect now we have our total sales total cost i also think i skipped over total transactions which is one that we definitely want as well so we're going to create another one for total transactions there's a few different ways you can build this measure i personally my favorite is count rows so you'll see me use count rows quite a bit so i'll go over to my internet cells table and i'm going to right click and then create another new measure this measure is going to be called our total transactions very similar to the one that we created earlier on our sales territory table and then in this situation we're just going to type out count rows i'll hit tab to kind of auto-complete that functionality there and then what table do i want to count the rows from well i want to count the rows from the internet sales table so i'll type in internet cells here and then close up the parentheses just like so and this will return the total transactions from our internet sales table so let's hit enter there and perfect now we're going to create two more calculated measures then we're going to dive in and start taking a look at filter context the next measure that i want to create is my profit profit is really just going to be total sales minus total cost and so when you're creating measures like this as a best practice i like to reuse those measures over and over and over again when i write other measures so i don't want to type out my total sales as sum of internet sales sales amount when i can just use the total sales measure that we've already created so let's go ahead and create another measure real quick and take a look at what that looks like on my internet cells table we'll create a new measure and this is going to be what do we say our profit measure like so so we'll say profit and that's going to be our total sales minus our total cost and then of course once i hit enter i want to format it now you probably noticed a couple of things here one when i reference a calculated measure in dax i do not reference the table that it is assigned to so calculated measures can be assigned to any table and they work just the same so if you accidentally create a measure on the customer table and you're like oh no that sales measure should have been on the internet sales table what you can do is you can select that measure come up to measure tools and you can just assign it to a new table and so one a very common practice in dax for many years has been that we would create a table called our measure table and we just assign all of our measures to that table i don't personally do that anymore because now power bi has given us the ability to assign our measures to folders within a table so that gives me that organization that i want but you can do that you can assign them to another table and they work just fine and one of the reasons for that is because measures must be unique so i can't have a total sales measure on the internet sales table and on the customer table it won't let you create the same measure twice so you don't have to reference the table name you might have also recognized that when you reference a measure the text here is actually purple and that's another indication just to let you know you're referencing a measure not a column in a table now when that's done we'll go ahead and hit enter and then of course we want to go ahead and format this one as well so as soon as it's done processing there i will click the drop down next to currency and then we're going to choose our english currency united states just like we did for our total sales and our total cost all right we're almost there we want to create one calculated measure and this is going to be our profit margin now profit margin is going to be our profit that we just created divided by our total sales and there's a couple of ways to do this a lot of students that i see here a lot of people writing dax they use the divide operator where they just say profit divide total sales the one drawback with that is that you could get a divide by zero case right you could get a divide by zero occurrence and so i would recommend as a best practice that you you use the divide function instead because the divide function will automatically handle divide by zero occurrences all right so we're going to go over to internet cells i'll right click on that table again and we're going to create another new measure on that table and then up here in the formula bar we'll call this one our profit margin and then once again remember we could divide this a couple of different ways you could have done this which i'm not going to do where you say profit divided by total sales but this does leave you open to those divide by zero cases personally i prefer to use the divide by function so we'll say divide we'll shift enter to go down to the next line one of the things i love about shift enter is you see how it automatically indents the code kind of saying this is the first parameter of divide this is the second parameter and then i'm going to do an open square bracket and then we will bring in the profit measure that we've already created now the denominator here is going to be our total sales so divide profit by total sales i'll do an open square bracket again go down grab total cells and then i will hit tab to kind of auto complete that functionality and then i'm going to do one more thing now there's uh this little known thing in dax which i'm going to start talking about now and kind of take us through this as we progress through the rest of this workshop when you're working in dax a lot of functions have optional parameters how do i know it's an optional parameter you know it's an optional parameter because it's surrounded by square brackets and so on the divide function over here in power bi desktop you'll see that we're dividing the numerator by the denominator but then there is an alternate result that appears right here within square brackets and because it's in square brackets it tells you that that is optional you don't have to provide that parameter but you can now the way that the divide function works is that if it encounters a divide by zero case it's automatically going to return a blank value for me that's perfect that's exactly what i want but you might say well if there's a divide by zero let's just return the profit or if there's a divided by zero let's return zero or let's provide some kind of text message that says divide by zero occurred and that's an option as well the only thing you need to be aware of is if you were to type something in here like divide by zero occurred you're effectively changing the data type of that measure to be a text which i don't want that to be a text because it's a percentage right so you got to be careful with that in this situation i'm going to opt to not use that alternate parameter all right so we'll go ahead and do enter and then i was told to give a shout out here to peter schmidt and chuck norris can divide by zero divide by zero what are we talking about here oh my goodness that's what that says so chuck norris can do everything yes and so another reason we like to do the shout out is because sometimes people don't think we're really live which if you were here at the beginning and the volume wasn't working you know we're definitely live all right so that is our profit margin we'll go ahead and hit enter right there and of course we want to format this one as well and that's going to be a percentage so we'll make that a percentage here real quick and we can now take a look at filter context so we've created about five or six measures here and we want to take a look at what does that look like when we start looking at that inside of a report inside of a visualization and so on this table or inside of this power bi i already have a table kind of formatted the way i want it here and i'm going to go and actually get rid of over here on the right in the visualizations the way that you validate your measures when you're working in power bi and you're building these dax measures is we do that in the visualization pane we start putting measures into visualizations we start slicing them by different attributes and we start seeing what those results look like and we validate that they're working the way we expect them to and so this is a very common way of kind of validating that so what i'm going to do is i'm actually going to remove the year from the row for just a moment here so that we can see the original value of these measures before they start getting filtered so i'll bring in my total cells and then i'm just clicking on the little check box next to each one of these measures here all right so we have here in the value section we have total transactions total sales total cost profit and profit margin and when we go back over here and we look at this table let me expand that out just a little bit more here when we look at this table we start to see that that is the sum or that's all of the rows from our internet sales table this is the sum of the sales amount column from the internet sales table and this right here one thing i like to start working on really early is kind of defining what that represents what does that value represent from a filter context perspective that is 29 million in cells across all products all customers all dates all sales territories and all temperatures because right now there's no filters that exist within this report we don't have any filters on the rows no filters on the columns no slicers no filters in our expression so this is really total sales across all time but once we start adding filters into this report these numbers change dynamically which is incredible so let's do that i am actually going to zoom back out and on this table visual right here we're going to go over to the sales territory table and we are going to add in the sales territory country and i'm going to drop that right here on the rows and you'll notice immediately that all of my values are filtered down and this is such an incredible feature right i worked in sql for many years from a consulting capacity building solutions for customers and if they wanted total sales by country i had to write them an expression that was total sales by country if they wanted total sales by country for 2005 i had to write that expression as well but when you're in power bi we created one very simple calculation and because of the relationships in the data model it's automatically being filtered down which is incredible so we have that right there and then if i go and say you know what i want to take this a step further so i'm going to clean up the table a little bit i'm going to get rid of everything except total sales and i'll do that by going back over to the right here just so we have more kind of landscape to work with we'll get rid of transactions and cost and profit and profit margin and then on the columns i want to add in the year so we're going to add the calendar year into the column of this matrix and i'll go over to my date table we're going to grab the year column from right there and we are going to drop that on the column section and let's take a look at this expression again so now we're looking at total sales but this cell right here that we're looking at in this matrix this represents the total sales for australia for the year 2005. so it's being filtered down from the attribute or the column that's coming from our sales territory table based on that active relationship and it's also being filtered down based on the year that's coming from the date table based on that active relationship so one measure is going to work across all of these different descriptive attributes in our data model the other way that you might slice this data is we might add a slicer across the top remember any filters that are in this report are going to be part of that filter context which is what we're talking about now so we're going to add a slicer right here across the top let me make sure volume is there there we go and i'm going to add a new slicer and for this slicer right here let's see we are going to bring in from our customer table i'm going to bring in the customer breakdown that calculated column that we created earlier so in the customer table let's go ahead and grab that age breakdown and i'm just going to drop it right there in that slicer if i did that too quickly what i did is i grabbed this visual right here in the visualization pane and then from the customer table we grab the age breakdown so this gives us another way just a visual representation of kind of interacting with our data and filtering at the same time and then we're not going to worry about formatting this or changing the slicer at all and i'm going to go in here and say you know what i only want to see total sales for australia for 2005 for the age demographic of 55 plus so we'll click on the little check box right here and that number has now changed it's no longer 1.3 million that filter context of that is you know going to represent what that is the total sales for australia for 2005 by the order date because our active relationship is actually on the order date for age breakdown of 55 plus now we don't have any filters coming from the temperature table so it's for all temperatures we don't have any filters coming from the product table so it's for all products but that's our filter context that's being applied to that cell and this is really really awesome so let me show you something else another way that i like to think about calculated measures when i'm writing them right if we go back and look at total sales and we look at this measure that we created right here earlier you might read that measure as this is the sum of sales amount i like to actually read that as something like this this is the sum of sales amounts within right within the current filter context this is the sum of sales amount within the current filter context meaning that this calculated measure that we developed is going to work within the filter context for whatever filters are being applied through the active relationships in the data model now i keep throwing that out there at you we didn't actually build the data model today but i keep saying active relationships active relationships let me show you what happens when an active relationship doesn't exist so right now our table is being filtered down by 55 plus and so let me hold it to 55 plus you see that the value is 668 000 right here if i go over to the model view and i actually delete don't follow along with this part if you're following along but if i actually get rid of that active relationship that exists between customer and internet cells which is what allows those different columns from the customer table to automatically filter down internet cells if i delete that relationship it's not going to work the filtering won't work anymore so i'm going to right click i'll delete that relationship right there and by deleting that relationship we go back over to the report view and we see it doesn't work it's going to not allow us to filter it accordingly so i think i deleted it it's taken a really long time there well i think we're frozen here so we're almost near a break i think we're going to take a break in about five or so minutes but i'm gonna see if i can unfreeze this because it appears to be frozen hold on let's see if we popped up on another screen here something going on in the background power bi desktop is freezing up that is not what you want to happen in a live demo but we can recover because it's right around break time anyway so what we're going to do is i'm going to fix this while we take a 15-minute break i'm also going to look over some of the questions that you guys had see if there's any of those that i can answer as soon as we get back from break which is a good time to kind of look at those and answer those questions and so i'll see you back here in about 15 minutes thank you everybody and then we'll put a timer right here on the screen and i'll the timer is going to kind of come and go but i'll make sure it's there as we come back to the class [Music] all right three two one hello and welcome back hopefully you enjoyed the first about hour and a half of this we've already recovered from that i spent the break got all of our measures back into this working model just as a reminder if you're enjoying what you're seeing here today we have plenty of dax boot camps that we do that are virtual events three-day events where you can kind of work with the data there's some hands-on labs ask questions and really dive into the technology and we also have a recorded platform as well and there's 20 off of the on-demand learning if you choose to purchase that so take a look in the description of this video if you're interested and i'm sure you'll get an email from us afterwards reminding you about that as well we had a couple of great questions now there have been a thousands of questions it seems like but a couple of great questions that i want to address real quick one of them was how can i get a count of number of transactions essentially for each customer and it's a wonderful question because it takes us right back to what we were talking about right before we went on break which is we can take advantage of filter context we don't have to build any other measures than what we've already built so let me show you how to return total transactions for each customer i'm going to duplicate the current page that we're working in over here in power bi desktop so we can just create a new page that shows the count per customer and you'll notice that if i go over to my visualization and i get rid of let's get rid of everything that's in this table and i'm going to add in total transactions which is that kind of count star or count rows count star and sql right so we bring in total transactions what does total transactions do got to think about that for a minute total transactions counts all the rows in the internet cells table within the filter context that's why i love to always add that sentence to the end when you're not adding a filter yourself so we have 60 398 total transactions but you want to see those broken down by customer well what we do is we go over to our customer table and we grab that full name cost that that full name column that we created earlier we drop it right there and aaron adams has four transactions aaron alexander has one aaron bryant has five so when we're looking at this in power bi here we're able to see those total transactions broken down by customer and it's a beautiful thing right this is the wonderful thing and i could tell by that question that's a person that comes from a sql background and you're used to having to write all these one kind of one-off calculations for every request but in power bi and dax you don't have to you build that one calculation and you allow the filter context and those active relationships to do the filtering for you we had another great question another question that we got was how why should i use calculated columns versus calculated measures should i build the the column in the model or should we build it more dynamic another great question when you're building calculated columns and measures if it's going to be an expression like total sales total transactions total profit profit margin any of those you should build them as a measure the reason you want to build them as a measure is because they're going to be dynamic just like we saw with total transactions here it's always going to work within the filter context if you build this total transactions on your customer table then yeah it'll work anytime you filter it by the individual customer but it won't be dynamic when you filter it by the year or the country or the product so that's another thing that you have to be careful of when i'm building aggregations kind of rule of thumb is i'm always going to do it in a calculated measure because it's dynamic somebody else asked well i don't have a table i don't have a date table how can i build a date table well there's a couple methods right we could do it in the power query editor there's a great blog by devin knight on his uh devonnitesql.com that i always use to create a date table but if we're talking dax and you just want to create it right here in data analysis expressions you can create a calculated table remember at the very beginning of this we talked about with dax you can create calculated columns calculated measures and calculated tables so one thing you can do is you would go up to the modeling ribbon here at the top and then i would click on new table and when you click on new table you're saying hey i want to create a new calculated table in this data model it can be a derivative of an existing table it can be an aggregate of that table it can be filtered down what we're going to do though is we're going to create let's just call it our calendar date table and our calendar date table the way you can create it is you can create it with one of two functions you can either do it with calendar auto or you can do it with just the calendar function i prefer the calendar function because you tell it the start date you tell it the end date and so i would say calendar and then remember when you build that date table it must have all range of dates and it must go back to the first day and the last day right of whatever year you go to so if we were going back to january of 2005 i would say or 2015. i would say 101 2015 and then i would pass in 12 31 2015 here close that up and this will give you a one column date table that just has the date and then you would use calculated columns to create your month your year your quarter so on and so forth in that date table now this isn't necessarily the best method that you can use but it does give you a quick and dirty date table so you can start building out kind of some proof of concepts so let's hit enter real quick and we'll take a look at the results over on the data view just to make sure that's what you were looking for once again what i recommend to most people is that your it department probably has a great date table for your company for your industry that shows working days that shows holidays that shows a lot of really good stuff so i would definitely reach out to the it department and i would ask them specifically hey do you have a date table i can use because it's probably going to be great and you're going to be able to leverage that but if you don't you can absolutely do something like this and if you email me i can send you a reference to that power query blog that really sets you up about seven or eight columns right off the bat so where is the table we created here we go calendar date table and it's going to create for us a one column table that has every date that we're looking for and then we would just build calculated columns on here extract the month from that date extract the year so on and so forth so that's how you do the date now i think there was another question here around what was it around fiscal calendar yes so if it's around fiscal okay fiscal date reason so a lot of companies have fiscal dates fiscal calendars that go from like microsoft goes from july 1st to june 30th so that's really how they measure it right so if you do a year-to-date sales you want to see from july 1st to june 30th you don't want to start over when you hit december 31st that calendar year and so the great thing about time intelligence calculations we haven't gotten there yet but the great thing about them in dax is that there's actually an optional parameter remember we talked about optional parameters just a moment ago with the divide function we're going to take a look at those optional parameters with time intelligence functions as well they're awesome they make it so easy to write your code and for those of you that are out there asking those sql type questions you're going to notice that it's really easy and devin i mean brian is going to be putting in the chat window for you guys that that blog i was referencing for devon knight all right so we've answered some questions we're going to see if we have some more questions we can answer at the very end of this but let's jump back into our discussion here with the dax workshop and we're going to continue talking about calculated measures and filter context which is going to lead us right into our next topic which is going to be calculate all right so right before we went on break looking at my notes here remember i was rebuilding that model we're going to build a new measure here let me build the scenario this is really cool so if we go back over to page one in page one we were looking at our total sales by country and so i'm going to go back over to this visual get rid of the year we've kind of gotten a kind of a feel for filter context right now and how awesome that is but sometimes it works against you right because sometimes you're like okay i want to be able to compare australia to all cells so i can get a percent of total now maybe you've used show value as i'm going to show you the difference between those as well in just a moment and i'm going to show you why you might want to use this method or that method but right now let's look at this if you wanted to get australia's percent of total which would be their 9 million in sales divided by 29 million in cells across all of your countries that's a little bit difficult when you first start with dax because what i want to do is i actually want to get this 29 million that's being represented right here i want to kind of put that on the same row as australia so i want it to show me my total sales right here of that 29 million and i want that to show up all the way down because once i can get that number the rest of that percent of total calculation is really easy it's just a basic arithmetic operation right so what i'm going to do here is we're going to create a new calculated measure that's going to return total sales for all countries and we're going to look at a couple of different ways of building that calculation when you're working in dax so just like before i'm going to go over to my internet cells table here i'll right click on the table and then i'm going to go ahead and select new measure and that's going to create for us a new calculated measure right here in power bi [Music] and this is going to be called and i'll just call this something like total sales and be all explicit all countries right so we're returning total sales for all countries and then we'll hit equals now like i said before calculate is the most powerful function in all of dax and you're going to probably wind up catching yourself using calculate and about 80 percent of all of the the measures that you create because those base measures that we created right before break they just work they work all the time so when you want to kind of create a derivative of that or a variation of that we use calculate to evaluate that expression within a new filter context a modified filter context so in this one i'm going to write a sentence over here in the comments of what i'm trying to achieve this always helps me out i want to return the total sales for all countries right so i'll just keep it in all caps to keep it consistent now i want to return the total cells for all countries so since i'm going to be modifying the filter context i know i'm going to be using calculate so i'll start with that and then i'll do shift enter to go down to the next line now i should have left that intellisense up since it's the first time we're looking at it when you're working in calculate the first parameter is always your expression for me that's always an aggregation that i've already built so it might be my total sales measure my total cost measure my year-to-date measure my profit measure but i'm reusing a measure i've already created so in this situation i'm going to return total cells and now we have to apply the filter this is where our filter kind of takes priority over those other filters that are in the filter context here because we're modifying that filter so we're going to tell it that hey we want to return total sales for all countries so we're going to use a function here called all and what all does is it returns all the rows in a table or all the values in a column and the keyword there is at the very end ignoring any filters that may have been applied so i'm going to use all and then i'm going to tell it to ignore all filters that are coming from the cells territory table and more specifically the sales territory country so we'll close that up with one closing parenthesis and we'll close it with another and so if you read this return total sales for all countries we're removing those filters there is another way to write this that i find some people like a little bit better so we'll show you that method here in just a moment so i'm going to hit enter and when i hit enter that's going to complete the dax formula and then up here at the top i'll click on the currency english united states and then we're going to come over here we're going to grab that measure let's throw it in our table and take a look at what that looks like all right so this is awesome this is what we wanted evaluate total sales within a modified filter context which is ignoring the filter coming from country so now we're seeing 29 million in cells represented all the way down now there's a little bit of weird behavior that just showed up here a little bit of odd behavior we get this record that was previously not showing up under n a before it wasn't there but now it is so the default behavior of a visual in power bi is very similar to like a default behavior of a power pivot or a pivot table in excel the default behavior here is that whenever all of the values for a row are blank the tables in in your tables in your charts and power bi will automatically hide those members of a column so in a is a member of the country column it was being hidden automatically now it's not because total sales all country says okay the filter is in a ignore that filter and return total sales for all countries so how do you work around this right i don't want that in a to show up how can we fix that well we can do this with some really basic really simple conditional logic we're just going to use the if statement so let's modify this measure real quick to kind of kind of satisfy this scenario so if we go back up to the top here we're going to create some conditional logic that says look if total sales is blank within the filter context then also return blank for total sales all countries so we're going to say if and then our logical expression is going to be total cells actually let's there's a couple ways to write this but i'm going to use is blank so if is blank total sells and what this does is it's a boolean expression is blank is going to check total cells to see if the value is blank there's no value and if that evaluates to true then we're going to do the next expression for if so if that's true it is blank we're going to also return blank for this measure that we're creating if it's false meaning that total cells had a value then we're going to perform this other calculation here so let me just select all of this and tab that over we need to add another closing parenthesis here at the end so if total sales is blank return blank for total sales all countries else return total sales for all countries right if there was a value for total sales then let's return total sales for all countries and this is a pretty common pattern that you can use in dax to get rid of those blanks in this example we had one member of the country table in a that was showing up but what if you were looking at a customer table and all of a sudden you had thousands of records that were showing up that would be a really bad visual so this is a way to kind of clean that up very simply in your dax so we're going to go ahead and hit enter here and you're going to see that row goes away immediately all right so i told you a moment ago there's another way we can write this expression some people find all a little bit hard to comprehend i use all all the time because i always have but there's actually a newer function we can use and we can leverage here called remove filters so instead of using all right here you could say calculate total cells and remove all the filters that are coming from this column so what i could have done instead is i could have used a function called remove filters and it clears all the filters from the specified tables or columns so the same exact results that we get with the all function it just might make more sense to you and if it makes more sense to you then go with that right use what is the easiest for you to understand and remember going forward so we're going to say remove filters we're going to go to the end we're going to hit enter we get the same results so i hit enter it's refreshed the results are the same all right now we're going to build a percent of total calculation we're going to build a percent of total calculation because now we have everything we need percent of total is really easy it's divide total sales by total sales all countries and then we're also going to take a look at another example here that's not just the percent of total that we built but it's the built-in percent of grand total i'm going to show you the difference between the two so let's do this real quick we're going to build a percent of total calculation and we'll go over to our internet cells table right click and create a new measure and so we'll call this one percent of total and our percent of total is simply going to be total sales divided by total sales for all countries so we'll use of course the divide function anytime i use divide i'm always going to kind of use that divide function instead of the divide by operator and so we're going to say divide total sales by total sales all countries like so and that's going to give us our percent of total now i'll hit enter here and when i hit enter we'll wait for that to complete working and then i want to set this up as a percentage so i'll click right there on percentage and then we can add that to our table and make sure and validate the results are working correctly so let's go ahead and do that real quick like so and you'll see that when we're looking at australia australia had 9 million in sales total for all countries was 29 million in cells which means that australia is about 30 31 of all cells across all countries if i sort this by percent of total right here at the top we can see that united states was 9.3 million they're around 32 united kingdom is around 11.5 percent and so this is how we can use calculated measures to be able to kind of to develop those ratios and those percentages here the hardest part of this calculation is understanding how to use calculate to ignore the filter that's coming from country so once we return total sales for all countries the next part here that percent of total is really easy because it's just a basic divide by operation now some of you have been working with power bi for a while and not dax and you've figured out other ways of doing things and you figured out wait a minute i've seen this before by simply just doing a percent of grand total on a column by clicking a button so let's take a look at that it's going to look the same at first but then you're going to notice it's actually a little bit different and both are good solutions just depends on what you want to do yourself so over here i'm going to add another measure to this table i'm going to go grab the total sales measure and drop it in the table right here right so i'm going to drop it in the table here now you say i want to know the percent of grand total for total sales across all countries well what we can do is under the value section right here you'll notice that we have total sales if i click the little carrot looking symbol this little drop down symbol for total sales right there we'll see that we can do something called show value as and one of those options is show value as the percent of grand total and this works great if you just need a quick and simple selection here so i'm going to say show value as the percent of grand total and then you notice that immediately total sales has become the percent of grand total and it works and we didn't have to write all those expressions so the net obviously the natural question or the next question should be well why did we build that measure well the measure kind of gives us more flexibility we can we can force it to do very certain things this is not the same calculation what percent of grand total sales does it says show the percentage of grand total for what's inside of this table percent of total remember we ignored all filters coming from country so let me show you what i mean by that we're going to add a slicer here so you can see the difference between these two so over in the visualization pane i'm going to click right here to add a new slicer to my report and then in this report in this slicer here i am actually going to add all of our countries to this slicer so over on our sales territory table i will grab sales territory country we'll drop that right there in the field list and then we have all of our countries now normally i would format this make this look a lot better make it look like a slicer in excel but we're talking about dax so we won't worry too much about that let me go ahead and just move it over to the left or over to the right a little bit here we go and so when i go ahead and grab or start filtering this down by the slicer you're going to see the difference right so i'm going to click on australia canada and france so you notice that our percentages of totals are different this value right here of 66 percent that is saying that australia which is the one that's in the very first row australia is 66 of the countries that are represented in this table that have been filtered down by the slicer however the percent of total that we built a moment ago is ignoring the fact that there's any filters coming from the country anywhere right it removes all filters whether it's coming from a slicer filter pane or the rows or the columns on the table that's why we built that the way we did and so this still gives you that original value saying look australia is 31 total sales across all of your countries in your data model that's what that is returning for us so there's a difference here now i'm not saying one's better than the other they're both great just depends on what your business requirement is you might say look when my users come in power bi and they start filtering it down to certain sales people certain customers certain products i want the grand total to reflect that to only that selection well then percent of grand total is perfect for you i still wouldn't use percent of grand total i'd use another way of writing this calculation using a function called all selected we won't actually cover all selected today unless we have a little bit of time there at the very end of the day maybe we could go back and revisit this but this gives you exactly what you want and so that's the difference between those two and that is our percent of total all right so with that being done we want to take a look at a couple of other examples here and we're going to keep using calculate to evaluate an expression within a modified context so that was our all countries removed we removed some blanks percent of total all right so let me clean up this table just real quick here i'm going to remove all of the filters from our slicer here at the top and i'm going to remove a couple of measures what we want to do in the table now is i want to show you how we can bring in things like total sales for united states total sales for canada and we can see them side by side one another in the same table and so that's what we're going to take a look at right now so let's go ahead and clean up this table i'm going to get rid of percent of grand total here i will also go ahead and get rid of percent of all totals or total sales for all countries and we're going to build two new measures here the first one is going to be total sales for the united states and i also want to duplicate this report page because i want to see this by year and you'll see why so i'm going to duplicate this report page by right clicking on the page there at the very bottom and then doing duplicate so i have this new page i'm going to replace the country here with year so that you can see kind of the analytical value of doing this whoops let's open back back up there we go and so from the date table i'm going to grab the year and drop that right on the rows here and so now i'm looking at my total sales by year all right so we got total sales by year here and i want to be able to see something let me get rid of that slicer whoops i want to be able to see total sales for the united states for 2005. total sales for australia don't be able to see them side by side with one another i am going to go ahead and click on that little save button right there because uh it's already crashed on me once we don't want that to happen again all right and then we're going to go ahead and create a new measure so on the internet cells table we're going to create a new calculated measure and this is where we're going to be much more explicit and we're going to say look evaluate total sales for united states regardless of what filters are there if it the filter is australia override that filter with the united states if the filter is all countries override it with the united states so how do we do that we're going to do total sales equals well let's name this united states and then that equals and we're going to use calculate because we're modifying the filter context we're going to return so calculate total cells right so that's the first part calculate expression you're going to get used to this this is a very common pattern you use all the time calculate this measure within this modified filter context so the filter that i want to add here is going to be where the sales territory country is equal to united states so we're very explicitly saying only return the total sales for the country united states that's what we're doing here in this example and so we'll hit enter and then we're going to take a look at it right next to that original total sales measure that original total sales measure is the sum of sales amount within the filter context respecting all filters this measure here is kind of a modified filter context it's changing the filter context for us so we can get exactly what we want for those very specific use cases so we'll format it real quick and then i'm going to drop that right here in the table and now we get to see it side by side here's our total sales for all countries here's our total sales for united states broken down by year and you could do some type of percentage of you know all countries compared to united states here we can also do another measure right we can do total sales for australia total sales for france and so we're going to do that we're going to create one for canada like i said we're going to walk through quite a few examples then we're going to do some conditional logic in here as well so let's create another example very similar to the one we just did right click on internet cells and we're going to create a new measure and this is going to be called total sales for i don't know let's go with canada right i can spell canada that's a good one i always struggle with australia i always want to add an extra i in there i don't know why so we're going to say calculate total sales and this time instead of calculating total sales for united states the modified filter context is going to be for australia so we're going to say calculate total sales for where the country so sales territory table and the country is equal that did not do what i wanted it to do where the country do that one more time there we go equals and we're going to go with canada all right so we'll close that up and then close up the calculate statement and this is very similar what we did with the united states in fact like i said it's a pattern calculate my expression now i define my filter context we're going to go ahead and give this a currency of english united states add it to the table and then we can see those two side by side right there in our table so let's go ahead and grab canada drop it right there in that table and this is really cool right you're able to see total sales for all countries because that's within the filter context total sales for united states total sales for canada now what happens when we go up to the slicer and i click on france well total sales is within the filter context so that becomes total sales for the country france how do the other two change well let's take a look if i click on france united states didn't change canada didn't change but now this technically represents total sales for france because it's respecting that filter understanding filter context in dax makes your life really easy because a lot of those questions like the one we got right after break seeing the total transactions broken down by customer that pops right up it works because the filter context is doing the work for us and so that's an awesome thing so this is how we use this now i did want to show you some conditional logic kind of within calculate here and i want to show you how do we get maybe total sales if the country is united states or canada and there's a couple of different ways to write that and i want to show you that logic here so let's do that next that'll be the next thing we do so i'm going to build a new calculated measure kind of just working along just we're just writing dax now we're just writing dax getting used to this pattern because once again this is a very common pattern that you'll write a lot of times in dex and the new measure we're going to create is going to be let's call it total sales and it's going to be u.s and canada and this time we're going to say calculate total sales right so return total sales for when the country is united states or canada there's a couple of different ways to write this the first way you could write this is we could just use the double pipe delimiter which is an or condition so i could say where the sales territory country is equal to let's say united states right so that would be our first kind of shot at this and then i could use the double pipe delimiter to say or and we would say or the sales territory sales territory country is equal to canada so we don't want to use and here because that would mean it has to be united states and canada at the same time that's an intersection that wouldn't work but if it's canada or united states return the total sales if it's either one of those countries so that'll be a larger number and so this is a combination of that maybe we're trying to build a north america versus all other groups right this is how you might approach that so this is going to return that for us now the only problem with this method that we're doing is that it's a little bit harder to manage because you have to keep adding rows and subtracting rows whenever you add countries or subtract countries so that's one thing to consider when you're working with this example right here but i'm going to go ahead and format that one as english united states as well and then we'll put that in our table also so that's going to bring in our total sales for u.s and canada and of course if we add these two together right here that is in fact 1.247 so just a really easy and quick way to validate that the code that we did is correct once again it is also ignoring or overriding rather that filter that's coming from the slicer that says france all right so i want to take another look at another way of writing this calculation right here real quick just so you can see there are multiple methods another method you might do is you might use the or function again i'm not a very big fan of the or function because it's similar to what we just did where you have to write it all out but the or function actually only takes in two parameters so if i use the or function here it takes in two parameters both of those have to be logical so expressions that evaluate to true or false the first expression would be if the cell's territory country is equal to united states if that's true and then we separate the two conditions with a comma and then you have the second logical expression the biggest limitation here though is that the or function only takes two parameters so if you have three or four or five different countries you wanted to group into this you'd have to keep nesting those multiple or conditions which i don't very much like right but this would work if we hit you know close up the parentheses here we hit enter over here you see we get the exact same results so it works it works great it's just not my go-to so what is my go to well i come from a sql server background writing code in sql server i like the n clause right and so dax has something very similar to the n clause and it makes the code easier to read and i think easier to manage so that's what we're going to do here when the country is in and then we provide a list of countries that we want it to return so instead of using or we're going to get rid of that and i'm going to delete everything on the bottom here and go all the way back up to the top here so we're just cleaning it up and we're going to say when the sales territory country is equal to nope when the sales territory country is in so we're going to provide a list here we're going to say when the sales territory country is in we're going to do curly bracket so when it's in this list and then we're going to say comma and then canada and then curly bracket and then we'll do one closing parentheses to close out that calculate and so to me this is just a lot easier to manage a lot easier to maintain if i want to add countries comma add the country you want to remove a country you just remove it from that list so this right here will give us the exact same results three different ways the exact same results we'll hit enter again and you'll notice that the results are still the same all right so that was total sales for us and canada now there is other ways of kind of looking at this right one of the things you might have noticed a moment ago when we were looking at the total sales for all countries is that that was working really well let's add that back in just like so and let's add in the percent of total as well so i'm back on that first report page and i want to talk a little bit more about calculate and how you can add more than one filter into a calculate expression so we have a slicer here let's change this slicer to maybe the date instead of country so i'll add the year in here real quick so you can see this one mistake that a lot of people make when they're working with calculate and they're ignoring filters like we did with the all function or they're using remove filters is they think that that applies to all tables in all columns but it doesn't it actually applies only to what you specify so if we add a filter in here on our on our years on the slicer and you come over here and you click on something like let's say 2006 how does this value change does it ignore that year filter or does it respect it well if you remember let's take a look at total sales all countries just to make sure we're all on the same page total sells all countries is simply saying calculate total cells and remove filters from cells territory country we're not saying remove filters from the year from the date table or remove filters from the product table or any other table right we're just ignoring filters from the country table so therefore if i come over here and click on 2005 you'll notice that this changes accordingly so now i'm going to be looking at total sales for all countries within the filter context irrespective of country because we're ignoring that of course and this is exactly what i want in this scenario this is perfect because if i filter it at 2005 i can see that australia was 40 percent of our total country sales across all countries in 2005. but if we go take a look at 2008 australia was actually only 26 percent so they fell from 40 to 26 percent now that might mean that australia started doing worse or it might mean we just really built up a lot of those other markets and they're more competitive but either way it's important to understand that calculate um only you know you can add more it doesn't ignore everything when we do what we did now i do want to show you this though you could be in a situation where you're like okay okay well i want to ignore all filters coming from the date as well well you could add additional filters into calculate so if i hit comma right here and i hit shift enter you'll notice that calculate says okay you can add another filter and then we got a dot dot here that says you can add additional filters after that so i could go in here and i could actually say i want to ignore all of the dates coming from the date table i want to ignore the years or you could even say i want to return total sells all countries for the year 2008 and you could do any of those how would we do that let's take a quick look and hopefully this is review what i might do is something like this where or we might say remove filters just like we did before from the date table that would ignore all filters that are coming from the date table so now it would ignore the filters on the slicer if you wanted to do that you could also do something like date calendar year oh we don't have calendar year we just have a year equals 2008. and so this will always return total sales for all countries now i don't know why you would want to do that in this situation i'm just showing you and introducing you to the concept that you can add multiple filters within calculate if you do want to be more explicit and more specific with the way that you change that filtering which i've definitely done i've done this many many times when i'm writing dax i just don't this is not a great example for that all right so we've taken a pretty pretty long look here at calculate and we're nowhere near done we still have a solid hour ahead of us and i have a lot more calculations i want to show you so the next thing that we're going to take a look at here and i'm way behind on my notes because i kind of know what demos we're going to do is we're going to take a look at two more examples of calculate and then we're going to jump into time series analysis which i'm excited about so if you wanted to see total sales for only the year 2007 that's going to be something very simple it's calculate total sales where the year equals 2007. so we'll go over to our internet cells table right click create a new measure and i'll show you this one and then we'll move on to time series analysis as soon as we get done with this total sales for 2007 right so we're going to return the total cells within a new modified kind of filter context which is only for the year 2007 so we know we're changing the filter context we're going to use calculate the most important function in all of dax and we're going to say return total sales for what was it 2007 right so date year equals 2007 and that right there is going to say look whatever filters coming from the date table it whatever the year is always return it for just the year 2007 and we would of course go ahead and format this very quickly as english currency united states and let's go ahead and drop that in our table yeah we can see it right here right so let's get rid of a couple of things so we can just look at it and you'll notice obviously the difference whoops you'll notice it's there's a very clear difference between total sales which is within the filter context so right now it's for all years because there's no filters coming from the year and total sales for 2007 which is filtering this down specifically to the year for 2007. in fact if i click on 2007 in the slicer right here you'll see that they now are exactly the same because now this measure is also returning total sales for the year 2007. so that's just another example real quick of calculate but we're not done with calculate there's definitely more we want to talk about with that let's jump into now time series analysis how do we get year-to-date cells how do we get fiscal year-to-date cells year-over-year cells all of those are it's probably some of the most powerful things you can do in dax because of how simplistic it is and how simple it is remember there's requirements we talked about in the powerpoint which is you must have a date table make sure your date table has all possible dates and then make sure you don't have any gaps in your date table all right let's jump in and take a look at time series analysis in this example the first thing we're going to build is probably one of the most common and that's going to be our year-to-date sales if it's october 8th and we want to see all of the the cells from january 1st through october 8. so we can kind of get that trend then we can do that very easily here by just returning the year-to-date cells so over on the internet cells table i'm going to right click on internet cells and create a new measure and this measure is going to be our year-to-date sales and our year-to-date sales is going to equal all of the sales to the current date so we're going to use a function index called total ytd and it's a super easy function to use first of all we're going to pass in our expression that expression actually should look familiar because it's the same thing that calculate ask for which is an aggregation so if i wanted to get year-to-date profit i would provide my profit measure if i wanted year-to-date profit margin that would be kind of odd one year-to-date total transactions we would provide our measure for total transactions what i'm going to do here though is i'm going to provide total sales because that's what we're looking at so we'll type in total sales and then we need to now provide another parameter the parameter that we see right there is going to say dates about 90 of the time when you see dates right there it's asking for the date column from your date table all right there are some exceptions of that sometimes we'll nipple manipulate that first but normally that's what it means and that's what it's asking for so i'm going to pass in the date column from my date table here like so i'm going to close out the percent parentheses and that's it we just created an incredible year-to-date sales calculation that works across all of the dimensions in our data model it works by country by year whatever you want to filter it by it will work and we did it with literally technically five words of code this is awesome so we're gonna hit enter and then we're gonna go over here and we're gonna change the currency and we're gonna take a look at what this looks like we're gonna validate it's working and then we're gonna go take a look at the fiscal because i know there was a lot of questions around kind of the fiscal year to date as well so we'll change this over to united states english currency and then let's go over to our table where we already have the year i'll clean that up a little bit get rid of a couple things and i am going to bring in the year-to-date sales calculation right there and let's get rid of the france filter all right in fact i'm going to get rid of united states and so what we see right here at the year level is we get the exact same number for total sales that we get for year-to-date cells and that's because they're both doing the same thing effectively total sales has sum up all of the cells for this filter which is 2005. year-to-day cells says filter everything from the beginning of this until the end of this period which is january 1st to 2005 december 31st however if we really want to see the value here what we will do is we'll bring in the quarter or the month let's add that to our table so we can kind of see this working in action so i'm going to add in the calendar quarter over here and then i'm going to go grab the english month name like so all right and now in my matrix if you're new to power bi or you haven't worked with visuals a lot i'm going to click on the drill down here at the very top right to drill down to the quarter level and this is where we can first start seeing this functionality right so for example in our year-to-date sales right here if you look at the fourth quarter q4 which is all the way to december 31st is going to be everything for this year up until this date so that is actually the cumulative total for everything this year which we started in july so july through december now you'll notice that as soon as we get down here in blue we get to january that number starts over so it doesn't go 3.2 million plus 1.7 which is like you know 4.9 it actually starts over at 1.7 so the first quarter equals the first quarter but then if we get out of all that real quick and we go down to the second quarter or the third quarter right here like q2 you'll see that that is again going to be the cumulative total for the first quarter and then the second quarter and then you keep going down now the great thing about this calculation is that this works at the quarter level the month level and the day level so if you keep filtering this down even further like this right here where we expand down one in the hierarchy you'll see even at the month level it's slowly accumulating and if you go down to the day level it works this is just a very simple very easy calculation that's very dynamic and comes with a lot of capability all right so we have a question here does dax and let's see if i can understand the question does dax have the ability to do this analysis by week it does um but not through a very simple time intelligence calculation so if you're doing this kind of week over week calculation that means that you're probably working in a custom calendar like a 544 or a 445 and so you can still build that out pretty easily in dax but your date table has to be set up a very specific way with certain flag columns and then what we would do is we'd say calculate total cells and then in that modified filter we would probably use something like the filter function to redefine what the previous period is so if i'm at the weak level go back and grab the last period grab the last week so there's not an easy way to do week over week inside of dax unfortunately but it's not very difficult either if you're familiar with calculate and how to evaluate something within a filter context or a modified filter context you can definitely do that usually what you want to do though is you want to build your date table out to have all of those attributes and that makes the dax a lot a lot easier to do so great question there's just not a really easy way to do that one not as easy as this anyway because this is really easy we get spoiled with dax because a lot of stuff we do in dax is really easy to do and so that's a little bit more complicated a little bit more code a little bit more validation but definitely possible with a limited amount of time all right let's take a look at year-to-date sales for the fiscal calendar i know we got a ton of questions on that and this is a fan favorite people always love this so we're going to take and actually let's just create a new calculated measure so we can see these side by side and this is going to be our fiscal year-to-date cells and for this one we're still going to use our total cells right so we'll do uh total ytd like so our expression is going to be our total cells and then we're going to pass in the date just like we did before all right now you're going to notice that we have two optional parameters for this total ytd expression the first one is filter i get a lot of questions about that i don't often give demos but i think i might do one real quick here for you guys the other optional parameter is year in date the year-end date is really what we're focused on right now so the year-end date here would be let's say your fiscal year end ends on june 30th well we would go in here and we would type in my year-end for my company ends on 0-6-3-0 like so and now it doesn't start over when it hits december 31st it keeps going and going until it hits june 30th and then it starts over so if you have a fiscal year that ends every year on march 31st june 30th september 30th you just put it right there and it'll automatically handle that for you once again really awesome capability in power bi and specifically in the dax language so we're going to hit enter here and then we'll format it and then we'll throw that in our chart and we'll look at it side by side with year-to-date cells so don't take my word for it we're going to validate that this is working exactly the way that we want so we'll drop it right there and if we look at this right what fiscal year fiscal year-to-date sales is going to do is it's going to go from july all the way to december just like our original year-to-date calculation so at december they're the same but that original year-to-date starts over once it gets to january because it it ends on december 31st our fiscal year to date though keeps going right so you see that this cumulative total that we're looking at this 3.863 million that is the total for all of the total sales starting from january of this year all the way back through last year right all the way back through july 1st of last year so july 1st of 2005 all the way through june 30th and so to verify that let's go all the way down to june and watch this start over so we have january february and march then we see april may and june and now it starts over because we specified that our year end date is june 30th so when it gets to january it starts over that's how easy it is to do a fiscal year calendar right here for year-to-date sales in power bi all right so that's the first example year-to-date sales i did tell you i was going to throw a little bit extra out there at you that other optional parameter so when we're working in total ytd there were two optional parameters the first one was filter what does that mean well total ytd is actually a derivative of calculate that the microsoft power bi team just made easier for us so technically we could do what we just did with total ytd by saying calculate total sales and then we would define the filter for the beginning of this year through the end of the current filter context and you could add additional filters in there right so you could do other filters total ytd allows us to add those additional filters so let's say you wanted year-to-date sales for only the weekday all right so only the weekday i don't care about weekend sales i work monday through friday i only care about sales during the weekday all right that's a great scenario so let's take a look at that we're going to add another optional parameter here to our year-to-date cell so we'll go back to the original calculation and we're going to add in that filter right here so this is actually going to be our year-to-date sales based on the weekday and i'm going to add in a filter that says where the date and then the day number of week unfortunately we don't have a flag that is just weekday versus weekend but we can use the day number of week here so we're the day number of week and we're going to use in and i'm going to use a curly bracket here and we'll just say 2 3 four five and six so if it's monday tuesday wednesday thursday or friday which is two three four five and six we want to return the total sales if it's one which was sunday or seven which was saturday we don't now i just know that because i know this data you would know that your data in your date table and i did it where i specified it because you could do a not in as well but not in a little bit trickier you got to add not to the outside here so i just kept it super easy for this example so we're now going to return year-to-date cells for weekday sales this is pretty cool so notice that for 2005 it's 3.2 million right that's going to change so we hit enter here and since we just overrode the expression we have it's no longer 3.2 million it's 2.3 and all of these numbers are less remember before this was kind of mirroring this total sales rate here but it's actually 329 000. i want to do an entire youtube series on optional parameters and dax because there's so much capability there and people just kind of ignore them or overwrite them because they don't understand how they work it's a really great i think it's a good series i'm thinking about it all right so that is year-to-date sales now we're going to go take a look at another very common problem index i want to be able to compare this year to last year you're about to see a very common very popular pattern and that is going to be using calculate as well so we're going to build a new measure on the internet sales table and this measure will be prior year sales and what we're doing in this example is once again we're going to use calculate and we're going to say all right take whatever the current filter is whatever that is so july of 2005 and go back to the last year and get july of 2004. if we're at quarter one of 2005 go back to last year and get quarter one of 2004 very common pattern so we're going to say calculate our total sales within this new modified context and the modified filter that we're providing is taking the existing filter and then going back one year so we're going to use same period last year and then you see we have that requirement for the parameter there that says dates so we're going to grab the date column from the date table so i'll do shift enter and let me get rid of that intellisense and we'll say date column from the date table i'll give one and then two closing parentheses now let me say this again this is a pattern dax has a lot of patterns that emerge they're they're they're like you write the same expression over and over you just replace one piece so for example if i wanted prior year profit i just replaced this expression with profit if i wanted prior year profit margin i just replaced that with profit margin very common pattern that we're talking about here so we're going to hit enter i'll format that very quickly as english currency united states and this is going to work it's going to give us exactly what we want so let's get rid of fiscal year to date and year to date so we can just evaluate the expression that we created and when we come over here we get a bunch of blanks like what's going on why are we getting a bunch of blanks well we're getting a bunch of blanks because quite frankly there was no cells for july of 2004 we just started opening our business in july of 2005. so if we want to see prior year sales we have to go to july of 2006. so let's go and take a look at that real quick i'm going to make this a little bit bigger and if we go down to july of 2006 it shows us right here that this is our current year sales this is our total sales within the current filter context and this is our total sales for the prior year right so if we go back and validate this we will see that july of 2005 was 473 000 so this calculation is beautiful and the great thing about it is you notice even at the quarter level it works at the quarter level so let's go look at q3 of 2005 and see if it was 1.4 million we go up q3 1.4 million so once you have your prior year sales you can build a year-over-year calculation it's super easy it's total sales minus prior year sales divided by i think prior year sales if you want to do percentage growth i always get that one wrong maybe it's total sales minus per year divided by total sales from this year i don't know but the point is the hard part is getting the prior yourself which really wasn't that hard we just had to use calculate to know what the filter context was and modify it ever so slightly so that right there is our year-over-year cells let me take a quick look at the note make sure i didn't skip anything here and it looks pretty good it looks pretty good all right so i told you this was a pattern so i'm going to go a little bit off script here i'm going to add another expression here you guys can kind of go back and look at the notes later and write this yourself but what if i wanted prior year year-to-date cells so right now we have a measure called year-to-date cells i want prior year year-to-date cells well you could go about this the really long way without your year to date then you calculate it then you go back but remember when you're using calculate with same period last year this is a pattern that emerges that we can reuse over and over again so this is a lot easier in every dax boot camp that i do i always ask this question i never get this answer because we're always over complicating things right so if i go click on internet cells and i create a new measure here we are going to create a measure that's called prior year year-to-date sales so that way i can see year-to-date sales for this year versus year-to-date sales for last year you might look at it in table you might see it on a chart like a graph where you can see them kind of intersecting each other and crossing over so on october 8th of 2020 so far for the year all of the days up until october 8th am i doing better or am i doing worse than i was last year it's a great trend so we're going to say equals and i'm going to do calculate here and then total sales nope not total sales because we're doing year-to-date cells year-to-date sales same period last year now some of you might be thinking mitchell why aren't you using previous year well previous year is different it's not as dynamic as same period last year it doesn't work at every period level same period last year is derived from data previous period comes from parallel period and parallel period works differently so unfortunately we don't have time to get into that in the demo today but if you were writing this out and you tried previous previous year instead of same period last year and you drill down to the quarter the month and the day you would get different results and that's because of the way that they work they're different both of them have great use cases and i use both of them in fact we're going to use parallel period here in just a few minutes when we get into semi-additive measures so let's finish up this example and uh keep moving along so same period last year it wants the dates which means the date column from my date table i'll give that a couple of closing parentheses hit enter and then we'll format that real quick here as english united states and now we have our year-to-date sales for the prior year of course we would want to be able to look at year-to-date versus prior year year-to-date which we could do so let's go ahead and get rid of prior year cells here from this table and then we are going to add in year-to-date cells and then prior year-to-date cells right there and right there all right just like before with prior year sales we get a bunch of blanks at first because there were no sales for that prior year but as soon as we get into july we start to see okay this year so far we have 3.1 million in sales last year we only had 329 000 pretty good of course we know we have six more months for 2006 that we didn't have prior year but as you go down you see that that total keeps to accumulating and it starts over so we start over in 2007. now let's look at these two because this is going to be pretty good so year-to-date sales starting in 2007 is 342 000. if you look at january from the previous year that cumulative total was actually more so this year we didn't do so good if you look at february our cumulative total is added up to 668 000 but the prior year we had gotten up to 800 and 2 000 so we're about 140 000 behind schedule right now so it's a really good way of analyzing your data and said okay we got to put some controls and processes in place change our marketing strategy we got to up those sales if we want to get back on track for the year and make our goal so prior year very common pattern calculate whatever the measure is same period last year you'll do that over and over again very common pattern index all right so now i want to take a look at semi-additive measures remember we talked about this in the powerpoint right a semi-audited measure is something like an inventory balance something like my account balance which sadly is around 100 please feel free to send an email to devon knight and tell them i need a raise but those are going to be semi-additive measures and you can't add them up across time you add those up across dimensions so you can add them up across some dimensions but not all generally it's across the time dimension where you can't add it because if i had 100 on each day of the month and you're on the last day of the month you can't say i have three thousand dollars uh you don't add that up across all the days so how do we do that how do we build this logic into power bi well we're going to use a few different measures that exist now in this data model that i gave you guys i went into sql i created a product inventory table that kind of simulates what we want to do here and so in the product inventory table i've created a table that has inventory levels let's go take just a really quick look at this to kind of add some context to the example that we're going to be working on i've created a table it's really just for one single product because if i added in all the products this would have became a very large i'm clicking save real fast because i see a white screen here and it's scaring me let's go back over to customer table refresh go back over to product inventory and that's not correct here it's really weird let's look at unit balance so we're getting values there all right so we're not going to be able to validate the table but you see that there are values in here um do i really have blanks i must have blanks and didn't know it when i created it all right well i think we have blanks so that oh we do we have blank so i tricked myself so we do have some blanks bad data we can filter that out later but here's what the data looks like so every day right every working day of the year that's not on the weekend we take an inventory balance we see what is the number of balance what is the number of units that we have in stock so for example on june 30th 2005 we had 113. on july 1st we had 118 but then you see that there's actually a two day gap here where we didn't have we didn't take inventory level over the weekend july 2nd or july 3rd so when you're building out measures around this you have to take this into account this is not as simple as just sum of units balance so we're going to go create real quick here a product inventory measure measure and then i'm going to show you how to handle this scenario in dax and it's actually relatively simple clean this up i'm going to clean up the table just a little bit here i'll get rid of the slicer as well let's go ahead and create a new measure on internet cells this is going to be our product inventory measure that starts us off so this is just going to be a product inventory there we go and our product inventory is going to be our sum of the inventory from our product inventory table so we'll start typing out product inventory there it is and we want to sum up our units balance now i know a minute ago i said you can't do this and now i'm doing it but you're going to see why so the first thing i do is i create our inventory balance that would be a thousand separator this is not a currency so we'll format that as a thousand separator now if i put the product inventory into a table like this one right here we know that the inventory levels are not going to be correct because it's adding it up across days it's adding up across months and that's not correct you always want the last balance so if i drop product inventory in this table you saw when we were looking at the product it was actually only like 113 but now it's saying 2630 because it's taking that inventory balance for each day of july and it's adding it up we know that's wrong so the first way that we try to tackle this within dax is we say return the inventory balance on the last day of the filter context so if you're looking at july return return the inventory balance for july 31st right if you're looking at june return the inventory balance for june 30th and a lot of times that works perfectly except for situations where you have blanks in your data like so if you're the stock market and you don't work on certain days or you're you don't take your inventory balance every that that that throws another kind of wrench in the problem so we're going to tackle both of these problems real quick so the first one is let's go ahead and create our closing balance i'm going to create a new measure here on internet sales and we'll call this something like product inventory closing balance or just closing balance last date and then we're going to say all right i want the closing balance for the last day so that means we're modifying the filter context so we're going to say calculate and then closing what product inventory so return product inventory for the last day not for all the days just for the last day of the filter context so we're going to use a function called last date and what last date does is it just returns the last non-blank date um seems odd i've never read that before it actually just returns the last date from your date table so we're going to return the last date from our date table i'll show you the last non-blank function here in just a moment and we'll hit enter and then we're going to actually format that as currency english united states of course oh no we'll do 1000 separators sorry about that because it's not a it's not a number and now if we look at that side by side with product inventory you'll see the numbers are drastically different so this is the last day if you look at august was august 31st that would be 117. for september our inventory balance on the last day of that month would have been 117 and then for october it would have been 116. so what last date does is it looks at the date table it grabs the very last day from whatever the filter context is so right here we're filtering our date table down to august so it looks at all the days of august and it gets the last day of august and then it returns the product inventory from that last day so that's filter context now you notice for july we got a blank value what's going on well in july it's returning the product inventory for july 31st but we didn't take inventory balance on july 31st it was a holiday it was a weekend same thing in december december 31st right it's new year's we didn't take an inventory balance everybody was at home with their families so how do we handle that situation when you have blanks well we can use another function here called last non-blank and so with last non-blank it's a little bit a little extra step but we say return the last date that had a value in other words return the last date within the filter context that had an inventory balance so let's go take a look at what that looks like real quick in our example so we're going to create a another new measure so we can see these kind of side by side a little bit of comparison here and on the internet cells table we're going to create a new measure here this will be called closing balance for let's see we'll call it non-blank and now we're going to say calculate again so we're going to return total cells within a modified filter context calculate oh no not total sales product inventory there we go and that is going to be for the last non-blank and our last non-blank the way that last non-blank works is it returns the last value from a column for which the expression is not blank so it takes two parameters the first one is the column that you want to return the last value from the second expression is okay how do you determine if it's not blank right is it by sales is it by transactions is it by customer what does it buy so the first value we'll pass in here is actually going to be the date column from our date table and then our expression is going to be product inventory right so if the date in our date table has an associated product inventory from the product inventory table it's not blank but if we look at the product inventory table and there's no date that corresponds to that then it is blank and we're going to ignore that so in this example here we're going to return product inventory for the last non-blank date in our date table right so very similar last date but we're not grabbing the last date we're grabbing the last non-blank date so if we hit enter now this is going to within the filter context find the last date that's not blank so let's take a look at that we're going to add it to our table real quick like so and the difference here is that when we're looking at for example july you see that july now gets a valid value whereas before it was blank same thing is true for december and any other dates where there might have been a blank value so right here what this expression does closing balance date it grabs the last date from the date table so it looks at july it grabs july 31st and it returns the inventory balance for july 31st which there wasn't one because we didn't take one what last non-blank does which is this expression right here is it looks at july and all of the dates for july because we passed in the date column from the date table and it checks to see what was the last date in july that actually had a value so it looks at july 31st and it says nope you didn't have an inventory balance so we're going to kind of skip that then it looks at july 30th and it says okay there was an inventory balance for july 30th so then it returns our inventory balance for july 30th and so this is a way of kind of handling semi-additive measures when you have blanks in your data if you don't have blanks it's really really easy to handle semi-additive measures because you could just write that original expression where we return the closing balance for the last day but if you do have blanks it requires a little bit more work and that's what we did here we just used last non-blank now if you wanted to return the first balance of the month right from the first day you would just replace last date with first date and you could return um for the first non-blank you would return last non-blank with first nonline so you'd rewrite that expression and you'd get the first day instead of the last right so that's how that would work if you were trying to return that for semi-additive measures now there's a lot more you can do with this opening balance month closing balance month opening balance year closing balance year that will kind of give you some of the same functionality in fact i'm looking at my notes i think we have one more semi-additive measure we're going to do here and let me make sure we have a good amount of time to do that and we do so we're going to calculate our inventory balance that's not let's do let's do opening balance for the month now if you want to do opening balance for the month technically your opening balance for this month is the closing balance for last month right so if you want to bring back your opening balance for this month it's the closing balance from the prior month so it's not first date from the current month that wouldn't work you're that's just the balance from the first day of the month you have to actually go back to the prior month and bring that in there's a couple of ways to write this in dax the easiest way is a function called opening balance month so i'm going to show you that first then i'm going to show you the alternative method that handles blanks so let's do opening balance month real quick and i think with about 20 minutes to go we have some time to do both of these do one more example and take a couple of questions here at the end so it's going to work out perfect we're going to do opening balance month and opening balance month is going to once again actually i always want to start with uh calculate here but we don't need to so i'm going to type in opening balance month we'll go over here and say we want to return the opening balance month for our product inventory so what was the product inventory at the beginning of this month whatever it ended for last month right so we're going to do that and then you notice here it says you have to pass in your dates column from your dates table and this right here is a beautiful expression once again if you have data and you have a situation like this where you have to return balances for whatever the balances might be and it's a semi-additive measure if you do not have blanks in your data this works and you do not have to worry about the next example i show you although the next example still works for other scenarios too so it's a good example and so we're going to return the opening balance for the month what opening balance month does is it automatically goes back to the prior month it gets all of the days for the prior month and then it grabs the last day of the prior month and then it returns the product inventory for the last day of the prior month and that's what becomes your opening balance for this month so that's all it does it goes back to the prior month gets all the days grabs the last day and then it gets the product inventory for that last day now you might have noticed it's doing the same thing that last date kind of did it just grabs the last day so if you have a blank it's going to return blank let's take a look we're going to go ahead and drop opening balance month right here in our table and i need to clean up so we can see everything right here and then you'll see it's working exactly the way that we kind of want it to in fact let's get rid of total cell so we can see it a little bit better all right so this right here closing balance last non-blank was a closing balance of july of june that closing balance of june is the opening balance for july perfect that works great but then we get to august and we're like wait a minute we want to return the opening balance for august and says we have no balance on august 1st well that's not correct we know we had a balance back in july why would we have no balance the problem is that opening balance month opening balance quarter closing balance month those functions they do not automatically handle blank values so therefore it's doing this it's doing the same thing that this last date function did it's looking at july it's getting july 31st from july and since july 31st had no associated inventory balance product inventory it's returning blank so how do we fix that well it's a little bit more code and if you really want to dive deeper into this we go deep into this stuff in our dax boot camp so that's definitely the place to learn this and get a kind of deep dive into it a little bit a lot slower more hands-on experience but i do want to show it to you i want to give you this tool in your toolbox so you can go back and try this out yourself so let's build another calculated measure here on our internet cells table and this one is going to be our opening balance month non-blank right and so we have two more measures we're going to build this one and then one more and then let me zoom in too all right come on non-blank there we go i can tell we're reaching the end here i'm wearing down all right opening balance month non-blank and for this one what we need to do is we're going to manually have to go back and get the list of dates from the prior month so the function that does that is parallel period parallel period will go back to the period you specify and get all of the dates from that period all right so if we use parallel period right here and then i pass in the date column for my date table what this is going to do is it's going to look at our date table and it's going to take the current filter that we're at and it's going to go back to the prior month so we're going to tell it minus one month and so we're telling it look at the date table go back one and then you specify your interval which is month so you could use the same thing for opening balance for the quarter you would just go back to the prior quarter opening balance for the year you would just go back to the prior year this is extremely flexible so parallel period returns all of the days from the prior month now i could say all right now that you've gotten all the days from the prior month grab the last date this is kind of what opening balance month was doing it's grabbing the last date there like so but we don't want to do that if we grab the last date we know sometimes it's going to be blank so what do we do well we already know we use last non-blank so we're modifying the dates with parallel period then we're going to use last non-blank and say from that list of dates from the parallel period gras so that's going to be let's say we were looking right here right we're trying to solve this problem for august so parallel period if you're looking right here at the cell the filter context is august parallel period goes back to july and it gets all of the dates for july last non-blank then says okay you've provided me a table that has all the dates for july last non-blank will go get the last date for july kind of like what closing balance month is doing right here so we're going to say last non-blank and that takes two parameters right the first parameter is your list of dates or your column rather and then an expression to determine if it's blank or not and ours will of course be product inventory and so we're very close to actually being done with this measure that's the hard part the easy part is we now want to calculate so this is our filter portion so this is going to be the filter part of calculate and so up here we're going to say all right calculate or return our product inventory for this modified filter if you were to put this in a sentence in fact what you would say is return product inventory for the last non-blank of the prior month and that's exactly what this calculation right here is doing it's giving us exactly what we want so let's take a quick look at this we'll add another closing parenthesis here hit enter and so unless i mess something up here sometimes i'll miss a parenthesis i'll put in the wrong place we should be good to go so let's grab that we'll drag that over real quick and we're going to take a look at this all right so you'll notice that right here we get a blank value and that's okay because we didn't have any cells back in may that never happened but every day after that we have an opening balance that is the result of the prior month so let me zoom in on that and take a closer look here we go all right so our new calculated measure that we created i know there's a lot here if you're brand new to dax today we just dove into the deep end i understand that uh it is a very elegant language it's a very simple language but there's obviously a lot more you could do this measure right here what it's doing is it's going back to the prior month getting the last date from that month that was not blank and it's returning that value and so if we're looking at july in our table right here we're trying to get the last non-blank from the previous month which is 113. it's perfect if we're looking at august we're getting the last non-blank from july and so that gives us our opening balance for this month really cool really great solution here there's a lot more to learn around parallel period versus date ad same period last year versus previous period there's a lot more in dax than what we cover in this workshop but this gets us very far now i'm going to broach ever so slightly the most complicated topic in my opinion in all of dax and that is a topic called context transition we talked about two things around evaluation content context in this class the first thing was we talked about row context row context is when you have an operation like a calculated column like a filter function or one of those x functions my favorite functions in dax that iterates over a table meaning it works on one row at a table at a time and when you work on one row of a table at a time you are performing an expression on each row of that table so that expression only sees that row we that makes sense that that's very logical but there's a side effect the side effect is that row filter which is the active relationship in the data model we talked about the fact that it was deactivated right and we worked around probably three or four examples where we used navigation functions related and related table to say hey use the relationship that exists well context transition is when the row filter gets added to the filter context and that can happen a couple of different ways it can happen automatically or it can just happen because you force it to happen and this is without using related and related table that's the interesting thing here so let's take a look at a quick example here in my notes we actually go over and look at the customer table which is perfect so on the customer table here we earlier in this class we created a calculation that was pretty involved uh we created a calculation using maxx related table and there was a lot going on here that we had to explain and for most students that are brand new to dax you're not going to understand this the first time you see it you kind of got to work through it a couple of times we could have written this with calculated context by forcing calculated context to occur we could have written this a lot easier let me remind you of a couple things here right we're going to create two calculated columns on this table real quick so you can see something that's really interesting we're going to create the first calculated column on the customer table it's customer customer table and it's going to return the maximum date and you remember what this does right we're going to call it max order date and if i type in here max internet cells and then order date what that returns from our internet sales table is it returns the maximum date for all records because there's no filtering going on so it sees the entire table and it grabs the very last date that's because we're in a row context so that's the first column we're going to create now i want to show you context transition and how this can make your life a lot easier and sometimes unfortunately in more advanced calculations when you're authoring more advanced calculations it can make it a little bit more difficult as well to really highlight this i do have to create a measure real quick and the measure is going to be this code in fact just so you know this is not a trick it's not slight of hand i'm going to copy that code right there i'm going to copy that code and then on the customer table i'm just going to create a measure real quick and the measure is going to be called max date measure and then i'm going to paste in that code that i copied a moment ago it's the exact same code as the column all right now the problem is whenever a calculated measure is called it gets wrapped it gets wrapped in a calculate statement and what calculate does is we've talked about how it allows you to evaluate an expression within a modified filter context it also does something else that can be really confusing it actually takes row filters that have been kind of deactivated like we talked about and it adds them to the filter context and if they're part of the filter context you're going to get completely different behavior than what we've seen so far so far and so technically this calculated measure that i just saw is wrapped it wrapped in a calculate which will force context transition to occur the simple definition that i use for context transition is it's when a row filter gets added to the filter context i like that better it's a little simpler so watch this we've got a calculated column that we created here that returns the maximum order date from our internet sales table we're going to create one more column on this table right click new column and this column is going to be called max date using measure equals and we're going to instead of type max order date from the internet sales table we're actually just going to return our measure that we created called max date measure which is if you remember it's that exact code it's the same code that we used in our calculate column but implicitly behind the scenes the dax language wraps that in a calculate because it's a measure so we're going to hit enter and we're going to go over here to the right and you're going to notice the results are different and this is this is uh surprising right this is surprising so this is context transition the filter that was on the row that was previously deactivated because we were working in a calculated column that calculated measure is wrapped in a calculate and calculate says hey you know what i'm going to take that row filter i'm going to add it to the filter context so now that row is filtering down the internet sales table no problem in this situation this is awesome right because we don't have to write that really complicated expression like we wrote over here where we said max x related table internet cells it's a little bit more confusing we can just say return the max internet sales order date but it's inside of a measure they say well mitchell i'm not quite sure that's correct can you show me another example here i can if you look at mac's order date right here just to be very very clear it's returning the very last date from the internet cells table over and over and over again right there right well what we're going to do is we're going to wrap that expression inside of a calculate so watch this if i come over here and i just wrap that in calculate and i go to the very end that is going to force that date key the date that the relationship is on to automatically filter down internet cells so the row filter gets added to the filter context and it just works like magic once again sometimes when context transition is introduced it's a great thing it makes your code a lot easier to write sometimes it's really really bad the other example takes it takes a little bit of time to work up to so it might have taken the whole couple of hours we had together here today uh but that's something we cover in our other classes and in that dax boot camp now if i hit enter right here and we go all the way over here to the right let me see here all the way over to the right the measure that we just modified was this one right here it no longer shows 731 all the way down instead it shows us the exact examples and that's because the row filter is added to the filter context and it's now able to filter down the internet cells it's now active all right that brings us to all of our demos i don't know how we did it but we got through everything that was in your notes remember that if you downloaded these class labs you know two days ago i would highly encourage you to download them again because the examples that i went through were ever so slightly modified i'm going to throw a couple of things out there remember that if you attended this and registered for this event today you'll get 20 off of our on-demand learning courses and also i would encourage you if you really like this take a look at our dax boot camps that are down in the description of this youtube video so in the description of this youtube video we have the links to those dax boot camps that are coming up this is a pretty big event so i would not expect those seats to stay open very long but that's i'm not a sales person so do what you want to do uh survey link yes the great thing about the survey link is it tells us what you want us to do in the future that's in the description as well so let us know what are events that you're interested in because we're going to start doing these more and more where we do these three hour sessions and we're just giving back to the community and we want to hear from you what are you interested in and then i'm going to help back for a few minutes and i'm going to see if brian over here has any questions for me that i can answer just to kind of wrap this class up as always thank you guys for joining us check me out on youtube at just mitchell sql mitchell pearson check out the youtube channel i'll be dropping a new dax video every week and i enjoy doing that all right brian do we have any questions for answering a lot of questions in the background there if you know peter also from the nerd boot camp i do so peter smith thank you thank you we have some quite a few people when you're in an event like this there's a lot of attendees appreciate everybody who is helping out with answering those questions and thank you peter for jumping in there and helping out as well uh but no common question most of them answered already wow no common information let me throw i saw some people asking for my contact information if matt is still on here he can throw that in the chat window but i will actually share my powerpoint as well and like i said before mitchell pearson won on linkedin please feel free to connect with me i talk with a lot of people on there all the time connect with me on twitter email me if you want young pearson at pragmatic works and like always i just want to tell everybody thank you for joining us today i hope it was helpful i hope it was informative i got yelled at a couple times for going too quick but it's recorded so you can definitely go back slow me down a little bit and uh and enjoy that so if we have no questions brian i think we are done sounds good i'm going to stop the stream now thank you so much guys thank you everybody

Channel: Pragmatic Works

Views: 378,782

Keywords: dax, power bi, intro to power bi, intro to dax, dax calculations, dax concepts, dax formulas, new to dax, new to power bi

Id: QJw4HkagVWc

Length: 174min 14sec (10454 seconds)

Published: Thu Oct 08 2020