Unknown

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 bootcamps and other bootcamps 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.

Alright, 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 1225 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 going to 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 sometimes it's hard to follow along, especially with a 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 coworkers 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 Read me file that just tells you about the different files. One is going to have the DACs 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 DACs 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. Alright, 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 dax. 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 sales 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. Alright, 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 Power Pivot. 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 Power Pivot 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, IT professionals as well, but it's really an end user tool and that's why we have dax. It's 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 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 aggregation, 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 sales, year over year sales, prior year sales. So if you want to do analysis across time, we

do that with calculated measures. And then there's also this concept 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 upstream 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. Alright, 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'd be

able to calculate a 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 getting a date table in your model. And also I love using calculated tables 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. Alright, 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 table that you're currently on. And so navigation functions are very similar to 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 amatic kind of filtering that happens through the data model, then you build a calculated column and 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, alright? 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 on my PowerPoint presentation. So this is called DAX Workshop data model. Once again, if you want to open it up, you do have to have the 20 19 20 20 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 dax. 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 intelligence. And whenever you see intelligence, 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 parentheses. 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 sales or this customer table, we have 18,484 rows. How does it know to take that first name from that row? 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 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 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 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. So for example, if we come over here and look at our data model real quick, lemme 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 sales automatically on that order date. However,

when we're working within a row context, that relationship is almost non-existent and it's been in 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 and 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 an author

more advanced D calculations. Alright, 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 want to kind of bucket that into an age bucket. So if you're over 55, you're 55 plus if you're over 45, you're 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. In order to build this age breakdown calculation, we are going to use some conditional logic in dax. This will be our first look at that here today we'll create an age breakdown calculation. 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 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. 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 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. So if that evaluates default, if they're not equal to, 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. 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 a 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. Alright, 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 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 let you take either a date value or a numeric value and format it in a very specific way. Now 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 parentheses 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, so lemme put a little comment. If I did the three digit month, this would return for January JAN. If you did the four digit month, it would actually return the full month. So you would see something like January right there. It's a very, very flexible solution. We'll go ahead and hit enter and then we'll go all

the way over to the very 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. That's all part of becoming better at writing a language 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. Alright, 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 the days 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 from people that are brand new at dax. 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. I wanted to give you an example in this DAX workshop just in case you kind of fall into that category. Let's take a look once again at our data model and I'm going to jump over to our temperature table and 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 sales. 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 sales 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 sales table all the way

over here on the right, I'm going to right click on internet sales 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 sales territory here, you'll notice that it doesn't quite work and eSense 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 in 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 and the internet sales 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 sales 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 relationship that you've already built inside of your data model. Alright, 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. If we click the filter here at the very top right, we can get a distinct list. 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 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. You'll notice it looks like I'm typing really quickly, but I love auto complete. 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. 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. This solves one problem where one table was set up and this fact table, the mini side of the relationship, it didn't have the key that we needed so we went and got the key. Sometimes you'll have many, 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. Alright, let's take a look at another way 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 sales 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 and 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 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 sales 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 sales 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 in

a row context and row context has disabled this key right here. 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 a hundred percent unique. That's what makes it the one side of the relationship. A lot of times dimension tables. And 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 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 on 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, they should be the one side of the relationship, your transaction tables, 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 in 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 one from the internet sales 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 intelligence. 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 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 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. Alright, 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 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 the case statement and 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 our sales territory total transactions is greater than or equal to 7,000, we're going to say that that is a high volume region. 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 4,000, 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. We want to be able to show our boss, we did have 10 million in sales, 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 na, not applicable or unknown. And in this table we have that we have a dimension attribute or a member in this record that's na, it's not applicable, we don't know. So if it's na, you notice there were zero transactions associated with this. If it's blank, we're just going to say, look, it's not applicable. So that's what we're going to 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 and 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. Alright, 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 function. 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. Alright, 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 sales 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 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 sales 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. So lemme 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, that 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, actually it's related table, we're on the one side 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, some 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, alright, 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 scaler value. So you cannot pass that related table that we just created. You can't pass that into the max function 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 sales order date. 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, 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 and Dax, it's really easy to do. But of course the hardest part there is understanding that row context that exist and how to work around that with navigation functions. Alright, 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. Then if we have any questions that we're monitoring the chat, they'll 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. 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. Alright, 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 prebuilt reports. So it was a specific industry and they wanted me to give 'em 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'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 'em sql, right?

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 automagically 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 and 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 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. 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 cells, 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 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 sales 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, and 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 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 a hundred dollars. 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, 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. Alright, 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 and dax, 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 leaders. 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 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 gaps. 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, 2017, then I want my date table to at a minimum go back to January 1st, 2015 and then go to the very end of this data this current year as well. So all the way to December 31st, 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 bootcamp, in 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 gets me 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 surface surface, what you can do with time intelligence when you're working within the DAX language. Alright, so that right there just to let you know is the end of our slideshow. I wanted to get through the slides very quickly and here kind of cover what we're going to cover and then we just want to write a bunch

of decks. 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 added 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 table 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 Ellington 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 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 sales 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 sales 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 sales table, I will right click on internet sales and then I'm going to create a new measure right there. And 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 I'm just summing up a column from my internet sales 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 and 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 measures, 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 Ellington. 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 sales 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 some 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 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 Rose. So you'll see me use Count Rose quite a bit. So I'll go over to my internet sales 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 sales table, we'll create a new measure And this is going to be, what do we say, our profit measure. 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 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 'em 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've 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 dropdown 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. Alright, 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 selves. And there's a couple of ways to do this. A lot of students that I see or 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 use the divide function instead because the divide function will automatically handle divide by zero occurrences. Alright, 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 dinner 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 sales, and then I'll hit tab to kind of auto complete that functionality. And then I'm going to do one more thing. Now there's 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? 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 divide 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. So you got to be careful with that. In this situation, I'm going to opt to not use that alternate parameter. So we'll go ahead and do enter. And then I was told to give a shout out here to Peter Smit and Chuck Norris can divide by zero, divide by zero. What are we talking about here? Chuck Norris can divide by zero. 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, we're definitely live. Alright, 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 pain. We start putting measures individualizations, 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 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 sales and then I'm just clicking on the little checkbox next to each one of these measures here. Alright, 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 when 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 sales 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 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 sale 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. Lemme 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 grabbed 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 checkbox right here, and that number has now changed. It's no longer 1.3 million. That filter context of that is 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, 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 amount 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 and 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 sales, which is what allows those different columns from the customer table to automatically filter down internet sales, 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 break. I think we're going to take a break in about five or so minutes, but I'm going to 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 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 the timer's going to come

and go, but I'll make sure it's there as we come back to the class. This treats been healthy and I'm go back full screen with you. All right, 3, 2, 1. 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 bootcamps 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 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 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 rose, 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 sales 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 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 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 why should I use calculated columns versus calculated measures? Should I build 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 'em 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. That's another thing that you have to be careful of when I'm building aggregations. 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 devin knight sql.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 of whatever year you go to. So if we were going back to January of 2005 or 2015, I would say one one 2015, and then I would pass in 1231 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 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 date reasons, 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. 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, Brian is going to be putting in the chat window for you guys. That blog I was referencing, Devin Knight. Alright, 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. Alright? 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 gotten a kind of 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 sales 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 cells 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. 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 sales 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. And this is going to be called, and I'll just call

this something like Total Sales and be all explicit all countries. 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 in about 80% of all of 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. So I'll just keep it in all caps to keep it consistent. Now I want to return the total sales 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 intelligence 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 sales 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. I'm going to use all. And then I'm going to tell it to ignore all filters that are coming

from the sales territory table and more specifically the sales territory country. So we'll close that up with one closing parentheses 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. Alright, so this is awesome. This is what we wanted. Evaluate total cells 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 NA before it wasn't there, but now it is. So the default behavior of a visual in Power BI is very similar to 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 your tables in your charts and Power BI will automatically hide those members of a column. So NA is a member of the country column, it was being hidden automatically. Now it's not because total sales, all countries says, okay, the filter is

na, 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 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 there's a couple ways to write this, but I'm going to use is blank. So if is blank total cells. And what this does is it's a bullion 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 parentheses here at the end. So if total sales is blank, return blank for total sales, all countries else return total sales for all countries. 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. And 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 decks. So we're going to go ahead and hit enter here and you're going to see that row goes away immediately. Alright, 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 the time, 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. Alright, 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. And 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 sales table right click and create a new measure. And so we'll call this 1% of total, and our percent of total is

simply going to be total sales divided by total sales for all countries. So what we'll use, of course the divide function, anytime I use divide, I'm always going to 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. 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 that the results are working correctly. So let's go ahead and do that real quick. And you'll see that when we're looking at Australia, Australia had 9 million in cells 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%. And so this

is how we can use calculated measures to be able 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've 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. 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 dropdown 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 obviously the natural question or the next question should be, well, why did we build that measure? Well, the measure gives us more flexibility. 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 dac so we won't worry too much about that. Lemme 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%, 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. 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 salespeople, 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. Alright, 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 remove some blanks, percent of total. Alright, 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 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. Alright, so we got total sales by year here and I want to be

able to see something. Lemme 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. I'm going to 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 it's already crashed on me once. We don't want that to happen again. And then we're going to go ahead and create a new measure. So on the internet sales 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 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 cells 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 the 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 cells 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 all countries compared to United States here. We can also do another measure. We could 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 sales 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. 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. Alright, 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, 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 Dax. 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 US and

Canada. And this time we're going to say calculate total sales. 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. So that would be our first kind of shot at this. And then I could use the double pipe, the limiter 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. 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 US 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. Alright, 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 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 ore 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 ore conditions, which I don't very much like, right? But this would work if we hit 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 and SQL server. I like the end clause, right? And so DAX has something very similar to the in 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 and 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. Alright, so that was total sales for US and Canada. Now there is other ways of looking at this, right? One of the things you might've 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 and all columns,

but it actually applies only to what you specify. So if we add a filter in here 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 sales all countries is simply saying calculate total sales and remove filters from sales 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. We are 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 to 2005, I can see that Australia was 40% of our total country cells

across all countries in 2005. But if we go take a look at 2008, Australia was actually only 26%. So they fail from 40 to 26%. 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 only you can add. 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 in to 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.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 sales 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 country. 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. Alright, so we've taken a 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, 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 sales 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 Cells For 2007. 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. So date year equals 2007. And that right there is going to say, look, whatever filter's coming from the date table, 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. 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

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 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 sales? How do we get fiscal year to date sales, year over year sales, all of those are, it's probably some of the most powerful things you can do in D 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. Alright, 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 cells from January 1st through October 8th, so we can kind of get that trend, then we can do that very easily here by just returning the year to date sales. So over on the internet sales table, I'm going to right click on internet sales 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 patch 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'd 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, alright? There are some exceptions to that. Sometimes we'll 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. So I'm going to close out the 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 going to hit enter and then we're going to

go over here and we're going to change the currency and we're going to take a look at what this looks like. We're going to validate it's working and then we're going to go take a look at the fiscal. I know there was a lot of questions around 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. Alright? 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 sales. And that's because they're both doing the same thing effectively. Total sales has sum up all of the sales for this filter, which is 2005 year to date sales 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. So alright, and now in my matrix, if you're new to Power bi, you haven't worked with visuals a lot, I'm going to click on the drill down here at the very

top to drill down to the quarter level. And this is where we can first start seeing this functionality. 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 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. Alright, 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, 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 5 4 4 or a 4 4 5. 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 sales 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 week 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 easier to do. So great question. There's just not a really easy

way to do that one. Not as easy as this. Anyway, 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. Alright, 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 sales. And for this one we're still going to use our total cells. So we'll do a total YTD. 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. Alright, 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 end 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. 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 sales. 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 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 ends on December 31st. Our fiscal year to date though keeps going. 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, 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 in 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. Alright, 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. 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. Alright, so only the weekday. I don't care about weekend sales, I work Monday through Friday. I only care about sales during the weekday. Alright, 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 sales. 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 wear 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 wear 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, 4, 5, and six. So if it's Monday, Tuesday, Wednesday, Thursday or Friday, which is 2, 3, 4, 5, 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 is 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 sales for weekday sales. This is pretty cool. So notice that for 2005 it's 3.2 million 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 'em or override they don't understand how they work. It's really, I think it's a good series, I'm thinking about it. Alright, so that is year to date sales. Now we're going to go take a look at another very common problem in dax. 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 cells. And what we're doing in this example is once again we're going to use calculate and we're going to say, alright, take whatever the current filter is, whatever that is. So July of 2005 and go back to 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 dinner and lemme get rid of that inte sense and we'll say date column from the date table. I'll give one and then two closing parentheses. Now lemme say this again, this is a pattern. Dax has a lot of patterns that

emerge. 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 sales 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. So if we go back and validate this, we will see we 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 prior year divided by total sales from this year, I don't know. But the point is the hard part is getting the prior year cells, 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 sales. Lemme take a quick look at the note, make sure I didn't skip anything here. And it looks pretty good. It looks pretty good. Alright, 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, look at the notes later and write this yourself. But what if I wanted prior year, year to date sales? So right now we have a measure called year to date sales. I

want prior year, year to date sales. Well you could go about this the really long way out 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 bootcamp that I do, I always ask this question, I never get this answer because we're always overcomplicating things. So if I go click on internet sales 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 'em kind of intersecting each other and crossing over. So on October 8th, 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 sales, 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 ad. 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 year instead of the 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 'em 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 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 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 sales here from this table. And then we are going to add in year to date sales and then prior

year to date sales right there and right there. Alright, 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 802,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 in, 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 in dax. Alright, so now I want to take a look at semi additive measures. Remember we talked about this in the PowerPoint, right? A semi additive measure is something like an inventory balance, something like my account balance, which sadly is around a

hundred dollars. Please feel free to send an email to Devin Knight and tell him 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 'em up across some dimensions, but not all. Generally it's across the time dimension where you can't add it. Because if I add a hundred dollars on each day of the month and you're on the last day of the month, you can't say I have $3,000. 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 SQLI 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've became a very large, I'm clicking save real fast, 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. Alright, so we're not going to be able to validate the table, but you see that there are values in here. Do I really have blanks? I must have blanks

and didn't know it when I created it. Alright, well I think we have blanks so that, oh, we do, we have blanks. 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, every working day of the year, that's not on the weekend, we take an inventory balance, we see 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, 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 unit's balance. So we're going to go create real quick here, a product inventory 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 sales. 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 unit's 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 2,630 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, as we say, return the inventory balance on the last day of the filter context. So if you're looking at July 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. So if you're the stock market and you don't work on certain days or you don't take your inventory balance every, 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, alright, I want the closing balance for the last date. So that

means we're modifying the filter context. So we're going to say calculate. Late. 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 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. 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 thousand separators. Sorry about that because 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've been 117. And then for October it would've 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. It's new. We didn't take an inventory balance. Everybody was at home with their families. How do we handle that situation when you have blanks? Well, we can use another function here called last non blank. So with last non blank, it's a little bit 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 another new measure. So we can see these kind of side by side a little bit of comparison here. And on the internet sales 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, 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 to 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 is it by? 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. 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 and our date table. So very similar to 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. 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've been a blank value. So right here what this expression does, closing balance date, it grabbed 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. 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, that requires a little bit more work. And that's 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 for the first non blank, you would return last non blank with first non blank. So you'd ReadWrite 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'll 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 are going to Calculate our inventory balance. 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. 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. 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 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. 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've 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 that 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 sales so we can see it a little bit better. Alright, So this right here, closing balance. Last non blank was a closing balance 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 it 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 bootcamps, 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 sales table. And this one is going to be our opening balance month non blank. And so we have two more measures. We're going to build this one and then one more and then lemme zoom in too. Alright, come on.

Non blank. There we go. I can tell we're reaching the end here. I'm wearing down. Alright, 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 that is parallel period. Parallel period, we'll go back to the period you specify and get all of the dates from that period. Alright, so if we use parallel period right here and then I pass in the date column from 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, 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. 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. We'll 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 parentheses here, hit enter. And so unless I mess something up here, sometimes I'll miss a parentheses, 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. Alright, 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 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 a hundred 13th. 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 ads, same period last year versus previous period. There's a lot more inact 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 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. That makes sense. 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 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 happened 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. We created a calculation using max X 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 sales

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, 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 slide 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. 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 could 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. Technically, this calculated measure that

I just saw is 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 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. And 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 max order date right here, just to be very, very clear, it's returning the very last date from the internet sales 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 the 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, it takes a little bit of time to work up to, so it might've taken the whole couple of hours we had together

here today, but that's something we cover in our other classes and in that DAX bootcamp. Now, if I hit enter right here and we go all the way over here to the right, lemme see here all the way over to the right, the measure that we just modified was this one right here. It no longer shows 7 31 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. Alright, 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 two days ago, I would highly encourage you to download 'em 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 bootcamps 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 bootcamps that are coming up. This is a pretty big event, so I would not expect those seats to stay open very long, but I'm not a salesperson. So do what you want to do. 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 hold 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 sequel, Mitchell Pearson, check out the

YouTube channel. I'll be dropping a new DAX video every week and I enjoy doing that. Alright, Brian, do we have any questions? Well, huge shout out to Peter for answering a lot of questions in the background there. I think Peter also from our bootcamp. I do. So Peter Schmit, 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. But no common

question. We think you got 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. Elm Pearson at Pragmatic Works. And 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 enjoy that. So if we have no questions, Brian, I think we are done. Sounds good. I'll go ahead and stop the screen now. Thank you so much guys. Thank you everybody.