Skip to content
  1. We help small teams achieve big things.
  2. Our Technical Fellow just became a Microsoft MVP!
  3. We run the Azure Weekly newsletter.
  4. We just won 2 awards.
  5. We just published Programming C# 8.0 book.
  6. We run the Power BI Weekly newsletter.
  7. Our NDC London 2020 talk is now available online!
  8. We are school STEM ambassadors.
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 has recently graduated from our apprenticeship scheme.

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. She has been involved in every aspect of the solutions built, from deployment, to data structures, to analysis, querying and UI, as well as non-functional concerns such as security and performance.

Throughout her apprenticeship, she has written many blogs, covering a huge range of topics. She has also given multiple talks focused on serverless architectures. The talks highlighted the benefits of a serverless approach, and delved into how to optimise the solutions in terms of performance and cost.

She is also passionate about diversity and inclusivity in tech. Last year, she became 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.