3.8 Create basic DAX expressions - Video Tutorials & Practice Problems

<v ->Okay. So in this sub lesson, we are going to define</v> and discuss three types of DAX expressions those being calculated columns, measures and calculated tables. And we'll go ahead and demo the creation of columns, measures and a calculative tables as well. So let's just talk briefly about what a calculated column is. So a calculated column by definition is calculated on a row by row basis. When we go ahead and create a calculated column which we'll do in our demos, you'll see that it creates a brand new physical column in the table in which we're creating the column. It does take up space in the data model such as something you need to be aware of and it's value is calculated at data load time. Typically a calculated column resides in a dimension table to be used as labels on a report and or a slicer. And as you may notice as we go through calculated columns, they can often be pushed into the power query engine. At the very bottom of the screen there, you're gonna see a calculated column called line total sales which is the unit price from the sales table multiply by the order quantity from the sales table. Now just a reminder, inside our data model we have a column called line total sales PQ. Now that is equivalent to the last bullet point there where we can push that logic into the power engine. So we purposely did that back in Power Query so we can show you that the same can be done back there as can be done out in the calculator column. Now, in more advanced uses of data modeling it's actually preferable to push that logic back into the power query engine but for now we're gonna focus on calculated columns and show you how to go ahead and create those. Okay, so just a little bit about measures. So a measure is an aggregate of a column. It does not take up space in the data model and its value is calculated at runtime. So what do those two bullet points there mean? So when you initially create a measure and hit enter you're gonna notice that nothing happens cause you're just storing a query. That query will have a value when you go to use it at runtime on report and it's able to take advantage of context which we're gonna talk about in a subsequent sub lesson to go ahead and calculate its value. Typically, measures reside in fact tables although technically they belong to the data model itself. Which means a few things, measure names have to be unique across the entire database. So down below, we have a measure called total sales which is just the sum of the line total sales. And we can only have one measure across our entire data model that is called total sales. And we could actually move that total sales measure to any one of the tables in our model that we want and the calculation of the value will not be affected. And this is referred to as the home table. Okay, so now let's talk about calculated tables. Most of the time you create tables by importing data into your model from an external data source. But calculate tables let you add new tables based on data you've already loaded into the model. So instead of querying and loading values into your new table's columns from a data source, you create a Data Analysis Expression formula to define the table's values. So down below, we have an example where we're creating a brand new table called sales. And what we're gonna do here is UNION the United States sales table and other countries sales table together. So what we're gonna do in our demo, is we're going to go ahead and bring those two queries from the power creator and deliver them the data model and show you how we can actually create the sales table at the DAX level using a calculated table by UNIONING those two tables together. Okay, so let's go ahead and move ourselves into our power BI desktop file that's gonna support the sub lessons examples. Okay, so we've got our lesson three, sub lesson eight file right here. So I'm just gonna double click on it and get that desktop file open. We'll just go ahead and wait a moment. Okay, so we have our desktop file open. What we're gonna do here now is go to the data view. So this is where we're going to go ahead and write our DAX expressions. Now we can write DAX expressions on the report view but we're just gonna do them down in the data view at this point in time so we can watch our calculated columns get creative. So what we're gonna do here is I'm just gonna open up the fields pane here first, and we're gonna go into our sales table right here and we can already see that we have the line total sales from power query which we did back in the parkway editor. Now what we're gonna do here is go in add a new calculated column called line tool sales. So what we're gonna do here is, hover overtop of the sales table here, and we're gonna right click and say new column. When we say new column we're gonna see the formula bar at the top here appear and I'm just gonna make this a little bit larger so we can see what's happening here. And here's where we can then go ahead and build our Calculated column expression. So the first thing that we're gonna have to do is get rid of this column equals. Now, the reason that power BI puts this column equals in place here, is just a reminder to you that when you right click you're actually asking it to create a calculated column. We're gonna go ahead and get rid of that and define our line total sales like so. So what we're gonna do is type in line total sales and we're gonna say it is equal to sales and we'll go ahead and choose the order quantity and we're gonna multiply it by sales unit price. So you'll notice here before I actually go and finish this off, the IntelliSense has kicked in and this is very handy. It helps us actually type our expressions out. And it also signals to you what are valid constructs while we're actually go ahead and typing this. So one thing that I've learned over the years is if the IntelliSense is not letting you choose something you think should be chosen, that means that you're likely doing something wrong. And IntelliSense is actually pretty smart here. So in this case, I'm just gonna go ahead and say sales unit price and there is my calculated column expression. So I have line total sales equals order quantity times unit price, both from the sales table. The sales table is the current table cause that's what we're currently working on. So I'm gonna go ahead and hit enter there and we'll notice momentarily here that a brand new column is gonna get created here called line total sales and we're gonna see that on a row by row basis, the calculation was made to go from order quantity times unit price to give this value here and then it moved down to the next row. Now, it's just pure coincidence the way the data is ordered in this table at 14 97 is repeating itself over and over again. If we take a look here in this dropdown, we're gonna see that there's many more values that were calculated we just happen to be seeing the same bell repeating at the very top of the table. And so what we're gonna do here in our next lesson we're gonna talk about context and the way this calculated column was calculated was by going through each one of the rows row by row and performing the math and then moving down to the next row. And it went through those 60,398 rows there in this table instantaneously. So it was able to go through and do that. So we're gonna talk about this when we refer to the row context in the next sub lesson. Okay, so next what we're gonna do, is create a measure. So once again, on the sales table I'm gonna right click and say new measure. Go like that. And we're gonna see here in the formula bar, that momentarily is gonna say measure equals. Once again, power BI signaling to you that you're actually asking it to create a measure. It's gonna be your job to go ahead and get rid of this measure like so. So we're gonna go ahead and do that and then we're gonna go ahead and type our syntax in. In this case, we're gonna type a measure and create one called total sales, like so and we're gonna make it equal to the sum similar to the Excel function of the sales, line total sales and we can choose the line total sales we created as a calculated column or the one that we did back in power query. I'm gonna go ahead and use the one that we created just momentarily as a calculated column. Once again, go ahead and close that bracket off, this time we're gonna hit enter and we're gonna notice something different happens. When we actually went and created that, we can see down below here in our fields paint that a new measure was created in the actual table itself. So I just zoom in here. We can see that it has a little calculator next to it it's called total sales, that was created. But if we go back out here and actually take a look in the table, nothing was created and a lot of times when people are getting used to DAX and they go ahead and create a measure they're expecting to see something over here on the table. But that's not the case. Cause keep in mind, all we've done so far is we created a stored calculation and this value will be calculated when we actually go to use it on a report. So let's just quickly show you what that would look like. So I'm just gonna go to the report view really quick and what we're gonna do is just build our first very basic report. I'm gonna take this total sales out of my sales table and I'm gonna drag it to simply drop it onto the canvas. So we're gonna wait a moment here and we're gonna see that a visualization was created. And it is just a bar chart or sorry, a clustered column chart. So what I'm gonna do here next, is actually just go ahead and I'm gonna turn this into a table matrix. I'm gonna go ahead and do that. And we can see we have a total sales value here. So I'm just gonna go ahead and make that a little bigger so we can see it cause we're gonna use this example a few more times over the next little bit as we actually walk through the rest of our DAX expressions and talk about the context. So I'm just gonna go ahead and make that a little bigger. So what I've done here is, I've made my header bigger, my value is bigger and the total if I had one larger as well. The way that I did that was, I made sure my visualization was highlighted. We're gonna do lots of this in the next lesson but I wanted to show you how we do this now. So I went in here and I went to the format right here. I went down to the column headers card, like so and I chose the text size of 18 to make our column headers larger. I went to the same thing in values and made that 18 as well so that we can read the values in a little bit easier and I did the same thing on totals as well should we actually go ahead and start displaying totals as we start putting more onto this visualization. So right now this measure is being calculated within this visualization. It shows $161 million and change there. So one of the really nice things about measures here is I'm actually going to go ahead and add a little bit more context to this. So I'm gonna go into my Order Date Hierarchy and I'm gonna grab the Order Date Year here and go back over to my field's bucket and we'll explain all these things once again in the visualization section for now I'm just using these visuals to explain measures. And I'm gonna take my Order Date Year and drop it into this visual right here. Okay, so let's try that again. I'm gonna drag my Order Date Year and put it above total sales there and there we go. So now I'm starting to see these values come out. So I'm just gonna reposition this visual, make it larger cause we're gonna use this one quite a bit the next little bit. And we can see that now our total sales measure on a intersection and section basis has been recalculated to say I wanna see the line total sales for 2017. I wanna see the line total sales for 2018, 2019, 2020, 2021 and then the grand total here. So this is one of the beauties of the measures is that they are calculated runtime and they have an incredible amount of flexibility and allow you to do all kinds of different things with them based on the context that you're throwing their way. And in particular that context is what is referred to as the filter context, which we're gonna introduce to you in the next sub lesson. Okay, so the last thing we're gonna do here is we're gonna create a calculated table. So the way I'm gonna do it in this data model here is I'm just gonna go ahead and close these tables up here. I didn't actually save my work as it stands right now. I'm gonna work my way back into the power critics. I actually wanna show you bringing these tables through from power creator and actually union them together using the DAX expression to create a brand new table. So once again, I'm gonna go back into the power creator by clicking transform data. And here I promised you we'd never go back in the power creator. And now what I'm gonna do is I'm actually gonna ask that we deliver United States sales and other countries sales over into the data mall itself. So how do we do that? We just right click on both those I guess we should do one at a time. Right click on it and say enable load. So let's enable both of these to be moved over into the data model, click on that, click on that. And what I'll do now is go to the top here and click close and apply. So I'm gonna take those two tables that were just staging tables a moment ago and push them over into the actual data model itself so I can show you creating these as a calculated table, over in the data model itself. So let's just go ahead and let those two tables come through. And we can see now that we've got other countries sales and United States sales. So I'm gonna go ahead and create a table here. And now what we're gonna do is, go to the top here where it says table tools and we're gonna click on this command over here in the calculations and call it new table. So what we're gonna get here now is a expression that says table just signaling once again that we're creating a table here. What I'm gonna do is I'm gonna call this sales DAX just to signify that this expression is creating a table called sales but it's being created from DAX. And the reason I'm doing that is we already have a table in the data model called sales and keeping in mind we can't have two tables in the data model with the exact same name. So what I'm gonna do here is, I'm gonna use the UNION clause and then I'm gonna go ahead and choose the United States sales, once again taking advantage of IntelliSense here and I want to go ahead and concatenate that with other country sales. So as soon as I start typing commands in there we'll see that the tables start coming up. I can go ahead and hit enter, like so and we'll see momentarily we're gonna get a brand new calculated table here called sales DAX that is equivalent to the sales table except we did it using the DAX expression. Now, it's kind of hard to see here so I'm just gonna zoom in, and we're gonna see that this is actually a slightly different colored matrix here. It's kinda hard to see on the screen once you get it to set up on yours it has a blue in there and that's actually indicating that this is a calculated table. All right. So I'll go ahead and zoom myself back out. Right, so just to recap what we did in the sub lesson here. We created a calculated column. We created a measure and then we went through and created a calculated table. So an example of each one of the three DAX expressions. Okay, so in our next sub lesson, we're gonna go in and talk about context so we can understand how the different context affect our DAX expressions.