Skip to content
Carmel Eve By Carmel Eve Software Engineer I
Learning DAX and Power BI – Calculated Columns and Measures

So, in my previous 2 blogs I went over the theory of filter contexts and row contexts, now it's time for some examples!

Calculated columns

I have already touched on how calculated columns work: They iterate over all of the rows of the table, using the row context to evaluate the formula for the current row.

We saw in our previous example that we could define a new column which indicates whether or not a person has children by going to the table view and clicking "new column".

Showing calculated column in model.

Here we are using an IF statement. The first argument is the condition to be evaluated, the second argument is the value if that condition is TRUE, and the third is the value if it is FALSE.

This expression is re-evaluated for each row every time the data is refreshed, and the values are stored in the model. Therefore each calculated column will mean that the model takes up more disk space. This is fine in our small example, but this can be important to consider as datasets get larger.

Measures

The output of a calculated column is, well, a column. If you instead want to aggregate a column to produce a value, you need to use a measure.

We have also seen an example of this already, where we calculated the total number of children. If we click "new measure" and enter the following:

Total children measure being calculated.

We have seen that we can display the total number of children as follows:

Displaying number of children displayed in a table.

Displaying 14 total children.

And have seen that measures operate under any filter context which has been applied:

Table filtered to only those in London.

Showing 4 total children.

These values are evaluated every time you query the data (unlike calculated columns which are calculated on data refresh). The formula for evaluating the measure is stored on the model, but the value itself is calculated every time. This means that measures do not cause the model to take up much extra memory, but instead consume CPU.

In general, it is best practice to use measures where possible as DAX is optimized for these operations and in general reports will be memory limited rather than CPU.

So, in summary:

We have calculated columns which are evaluated row by row for a table. The values for each row are added to the model and cause a report to consume more memory.

We have measures which are used to calculate a single valued aggregation over a column (or multiple columns). The values are not added to the model and using these cause a report to consume more CPU.

There are many different aggregation functions, and I will go into this in more detail in my next blog.

Carmel Eve

Software Engineer I

Carmel Eve

Carmel is a software engineer, LinkedIn Learning instructor and STEM ambassador.

Over the past four years she has been focused on delivering cloud-first solutions to a variety of problems. These have ranged from highly-performant serverless architectures, to web applications, to reporting and insight pipelines and data analytics engines.

In her time at endjin, she has written many blog posts covering a huge range of topics, including deconstructing Rx operators and mental well-being and managing remote working.

Carmel's first LinkedIn Learning course on how to prepare for the Az-204 exam - developing solutions for Microsoft Azure - is due for release next month. And over the last couple of years she has also spoken at NDC, APISpecs and SQLBits. These talks covered a range of topics, from reactive big-data processing to secure Azure architectures.

She is also passionate about diversity and inclusivity in tech. She is a STEM ambassador in her local community and is taking part in a local mentorship scheme. Through this work she hopes to be a part of positive change in the industry.

Carmel won "Apprentice Engineer of the Year" at the Computing Rising Star Awards 2019.