Introduction to DAX in Power BI: Context, Calculated Columns, and Measures

Learn the DAX basics of context, calculated columns, and measures

This tutorial covers the basics of the DAX language. Matt will teach you about DAX syntax and structure, row and filter context, and how row and filter contexts are applied within calculated columns and measures in Power BI Desktop.

Hi, this is Matt with Playfair+. DAX is the fundamental piece of Power BI information that you are going to be using to create additional views, create a next-level analysis in Power BI Desktop. So today’s tutorial, we’re going to be walking through some fundamental DAX information. We’re talking calculated columns, we’re talking measures.

We’re talking some of the syntax. So this is a starter pack for DAX. So I hope you come to this if you’re beginning or if you’re looking for a refresher on DAX in Power BI. Let’s dive in. DAX is how we take our data structure beyond what it was originally created as. So if you’re creating customized calculations in Tableau, DAX is the language that you’re going to use to do that in Power BI.

We’re going to cover what context means for DAX. We’re going to cover columns and measures, they’re broken out into two distinct ways that DAX handles information. And we’re going to go over syntax. We’re going to talk about the different functions that are available in DAX. And we’re going to cover some common errors.

The two biggest common errors that you’re going to see when you’re writing DAX is syntax. Syntax is great because Power BI really does a good job of walking you through what is wrong. If you’ve written wrong syntax, a lot of times it’s a misplaced comma, an extra parentheses, maybe you have an open function that you haven’t put enough values into, enough arguments into. And so, Power BI is really good at explaining what you’ve done and where the error is that you need to fix it.

So maybe even better than Tableau does about pointing out what the errors are. The other error that we’re going to see is maybe not using an iterator function, then we’re aggregating at the wrong level, or if you’re trying to build a custom column when you should be building a measure and vice versa.

So we’ll talk about when and why we would use a measure versus a column. So, but first let’s talk about context. So filter context and row context equals evaluation context. And I would definitely write this down. And then you’re going to come back to it in a couple days and you’re going to say, why did I write this down?

I don’t understand what this is at all. But really what we’re saying is every DAX expression is evaluated inside the context of having both a filter context and a row context. So row context is what is affecting that current row. If you’re looking at a matrix and you’re looking at, you know the Category of Bikes and the Sum of Sales you are looking at specifically the return value for that row.

Now if you’re looking at the filter context, there’s a lot of other things that can be stacked on top of that. You might have a country filter applied, you might have a year filter applied. All of those things are filter context and they combine into the evaluation context.

So DAX takes into account both of those features and then returns what it calls evaluation context. So just think about it as these are the different ways that my data is being sliced. These are the different things that are affecting what is being displayed on my screen.

So as we move through and we talk about calculated columns and we talk about measures, calculated columns, intrinsically, they have that row-level context because they have generated a unique value for each row, always. They are stored in the memory in that when you create a new calculated column, it is added to your data model. It increases the size of your data model.

So if you have a already large data set and you create a hundred new calculated columns and you say, my data model is running really slow. One of the reasons is because you’ve created a huge addition onto that data model. So maybe there are some ways that you could create some measures that are only called when they’re used that could save you some performance and some size. And again, the columns are computed at refresh time.

So if you have what we’ve done earlier with the profit column that we created, if we change some of the underlying costs or sales figures for those rows, and then we refresh our data, our calculated profit column will refresh at that compute time, and only at the compute time. So if we make a bunch of changes and then don’t refresh, and we wonder why our profit numbers are not looking like we expect them, it’s because we’ve never refreshed the data.

And then measures are, they use that filter context. So measures do not have an intrinsic row-level value. They’re not stored in memory. They’re only calculated when that measure is used on screen. They’re really good for aggregation. So I certainly like to think of measures as what you would use when you don’t need to like append a new value.

So if you’re thinking this is something that I can just call and use, that’s a measure. If you’re thinking I need to actually have this in the data, I need to have a new custom column that groups my sales figures into buckets into small, medium, and large, I would do that as a calculated column because those are not going to change unless you refresh the data and change the underlying data.

So, alright, let’s talk filter context. So we’re talking measures here. If we have, let’s say a table with two columns, we’ve got Product Category, we’ve got Sum of Sales Amount, you’re always going to have that filter and row context. So we’ve filtered to Category and we are showing the row-level context on each of these rows.

So that Accessories row at the top is showing, yes, the filter, but it’s also, we’re only looking, the Sum of Sales Amount has the row-level context on each row. So, the filter context, it’s the set of filters that’s applied to the data prior to the formula being evaluated. And that filter context stacks on top of the row context.

So, when you run your DAX, it is evaluated at the row level and then the filter level is applied to it. And another way to think about that is when we have our dimension tables and we have our filter context, that context flows from the root table. So if we have a Customer and we’ve applied a City filter to it, that starts at that Customer table, and it flows into the Sales table.

So it is applied all the way, not just on the measure itself, but in the same direction as the connection, the relationship between the Customer table and the Sales table. So, that filter is applied first and then the context is evaluated.

So, alright, let’s get into another way that we can look at a measure, and this is an example of a Total Sales measure. Now sales as a measure wouldn’t have an intrinsic row-level context. But we can generate that by using specific functions like SUMX(). So to create this table that has Total Sales and not listing the exact same number for each of the sales, we would need to use SUMX(). So if we tried to create this Total Sales and just said, Sum(Sales[Unit Price]) times SUM(Sales[Order Quantity]), it’s going to return one value every time because it’s only looking at the actual sum of all of the pieces at Unit Price and Order Quantity. But if we use SUMX(), we’re able to iterate across, and that way we can see our Category and Subcategory.

Formula: Total Sales =

SUMX(Sales, Sales[Unit Price] * Sales[Order Quantity])

So Clothing and Bib-Shorts would be split out appropriately and we can see the context that we’re applying. So let’s go ahead and keep moving on and we’ll look at what happens when we have a calculated column. So again, a Total Sales Column has that intrinsic, that row-level context. So yeah, I like to think of it hand-in-hand.

So calculated columns, row context, those are simpatico, they go hand in hand. Every single row of a column is going to be included. Everything that we’re going to create in a calculated column has that row-level context. So, we can pull these from calculated columns, measures, related tables, everything works because there is a value in that table.

So, the Total Sales Column, it’s looking at the corresponding rows for Order Quantity, Unit Price, and that determines the total sales for each specific row. So you can see in this example, we’ve got, a one Order Quantity for many, but if we were aggregating, if we had a two Order Quantity for that top Unit Price, the 714 would double.

We’d be looking at 1429. So. Okay, let’s talk about the actual syntax of our DAX. And as we walk through, we’re going to show that when you’re writing DAX, you’ll start with the column or measure name that you want to create. So we’re calling this Profit. We’re simply writing Profit =.

So that’s the starting point. A lot of times when people write DAX, they like to hit, the title of their new calculated column, or their measure, equals, and then shift+return, that will bump the line down and it makes it very clear that the Profit is the name that you’re using. Everything else that comes from after that is the argument.

So, you need to have an operator in your DAX. This is, you know, obviously subtraction, so we’re showing Profit as Sales Amount minus Total Product Cost. And we’re referencing the table directly. So in a measure, a measure does not have to live within any specific table. It can just be on its own.

And because it lives on its own, it needs to reference the table that it’s calling from specifically. It needs to be, there’s no like intrinsic, oh, we’re in the Sales table. Let’s just pull from the Sales table. It needs to be an explicit reference of, we are calling the Sales table and we’re taking the Sales Amount minus from the Sales table, Total Product Cost.

So that’s the two columns that we’re pulling this argument from. Because of that, um, there’s a unique way to do this, called just creating a new table called Measures Table. I definitely recommend doing that. And we can jump back over to Power BI real quick and I’ll just show what we do for that.

You would go in and you would just go to your Table view, and this is the follow along. So we have our measures table created. There’s nothing here. It doesn’t matter because all we need to do is create a new table. We’re going to call this Measures Table, if I can type, 2, and then it’s creating a new table on our Data pane.

And then if we want to stash all of our measures, we can just put them in here and that it’s a really easy way to make sure you know where all of your measures are. They’re always going to be referencing other tables, and that’s fine. They can just live here.

So you’re never creating, say, let’s say you get into creating 10, 20, 50 measures for, a workbook that you’re working on. It’s, it’s the main data set at your job. If you did that in seven separate tables, I think it’d be really hard to keep track of all of them. However, keeping them all in a single measures table, you know where to search, when you’re looking for, did I create a, a Tariff Dynamic Cost measure? I know where it is. Did I create a Product Cost Grouping? I know where that is. It lives in the Measures Table.

So I definitely recommend going into the Measures Table. And creating your own table for that. So again, you just go Table view, create a new table, and you don’t have to put anything in it.

That’s it. That just has to live there. Thanks for watching. I hope you have a better understanding of the DAX programming language and Power BI and how it can be useful in your everyday reports. Thanks for watching.