Calculated Columns

Let's talk about some of the unique characteristics of a calculated column. First is the fact that they're calculated. So, we need to create a formula or expression to determine their value. That calculation is computed at time of data refresh. That means that every time you refresh your data, all of your calculated columns have to be refreshed as well. Once they have been recalculated though, they're stored with the reset of the data for use. From that point, you can treat them just like any other column in your table. That's a part of what makes them so intuitive. Finally they're filtered by something called a row context.

The row context is a unique filter that says, For each row, we are going to limit this calculation to just this row. A calculated column isn't aware of the other rows in the table. Calculated Columns are pretty simple, they look at the other values in the same row to produce a calculated value.

• An expression producing a column

• Computed at the time of refresh

• Stored with the table

• Limited by a row context

Let's say we want to create a calculated column called Gross Sales.

Gross Sales = [Price] * [Quantity] * (1 - [Discount])

Measures

We have learned how to add some basic business logic using calculated column. But we aren't taking the advantage of the raw power of a columnar database. Measures are less intuitive but more powerful, when trying to encapsulate business logic. Measures allows us to manipulate filters that have been applied by the user. They allow us to look at all of the data, one column at a time, instead of being limited by a single row. So let's dig in What Measures are, and How they work?

A Measure is an expression summarizing all of the data in some sort of way. It aggregates all of the data into a single value. This value is computed at runtime, that is to say it's computed dynamically, on demand. Unlike other analytical systems, the values that come from Measures in DAX are not pre-computed. And as a result, the final value of that computation is stored for only as long as it's needed, and then it's discarded. The trade-off is that you're using more CPU, but less RAM. Finally, a Measure is limited by something called a filter context.

A filter context is all of the filters that have been applied by the user. A Measure looks at the data all at once, minus what's been filtered out.

• An expression summarizing the data

• Computed at runtime

• Stored temporarily

• Limited by a filter context

Measures are the core feature of DAX, they're the key feature of DAX. The power of DAX language lies in the ability to manipulate and use those Measures to concisely show what the data means.

Here, we have an extremely simple Measure. We want to produce the total of all sales. To do that, we need to pick an aggregate function of some sort, to combine all the data together. In this case, we'll use SUM.

Total Gross Sales = SUM( Sales[Gross Sales] )

Measures aren't extend to table horizontally. Instead, they summarize it vertically. In this case, we're going to pool the entire column of Gross Sales and squish it together, producing a single value. This gives you an idea of how Measures summarize the data vertically. Measures, even though they're associated with a table, they're very loosely associated with the table. They're not like calculated columns, which are just part of the table like any other column. In fact, something you can do that's more of advanced technique, is you can set up a dummy table, with no data, and fill it with Measures. And it's still work, because the Measures can see all of the data at once.