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 - Aggregators

So far I have covered filter contexts, row contexts, and the differences between calculated columns and measures. I next want to focus on aggregation functions, which are the building blocks for a huge amount of more complex processing.

There are many aggregation functions, but the most commonly used are:

  • SUM
  • AVERAGE
  • MIN
  • MAX

These operators allow you to aggregate values for an individual column and they will take into account the filter context under which they operate, as the dataset they work over will be the one defined by this context.

They do, however, ignore the row context. To show you what I mean, lets create a calculated column in the people table:

Showing calculated column in model using SUM. All rows show 14.

We can see that the same value has been added to each row of the table. This is because the aggregate operators are ignoring the row context under which they are operating (which relates to the current row of iteration), and there is no filter context applied in the raw data view so all of the data is summed.

The aggregation operators are used extensively with measures, to get e.g. the total children, the average number of children, etc.

X-Aggregators

The previous operators can only work on a simple column. You can't, for example, do:

Error returned when attempting at alter a column in SUM.

For this you need to iterate over each row, do the evaluation, and then SUM the results.

You could do this by using a calculated column, but that would cause the report to use more memory every time you wanted to do a computed aggregation… Luckily there is another option:

Achieving the same but using SUMX.

The first argument here is the table over which to operate and the second is the expression which you want to SUM. This will still take into account the filter context under which the aggregator is applied as this context will define the dataset over which the aggregator is operating and therefore the rows over which to iterate. This produces a doubled SUM of the number of children:

Showing 28 total "doubled children".

Notice here that I have used the word iterate. These operators use row contexts in the same way as a calculated column to iterate over each individual row, execute the formula for that row, and then finally SUM the results.

It is important to highlight that these X-aggregators still don't take into account the row context when doing the aggregation:

Using SUMX in calculated column returns 14 for every row.

I think it's best to think of the X-aggregators in two steps (which both happen under whatever filter context is currently applied):

  1. Create a temporary calculated column (which uses the row context to evaluate the expression for each row)
  2. Use the SUM operator on the temporary calculated column

Using these X-aggregators, we can build up quite complex processing, especially when combined with table functions, which we will see in the 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.