Skip to content
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!

 

Learning DAX and Power BI – CALCULATE

Learning DAX and Power BI – CALCULATE

Carmel Eve

This is the final blog in a series about DAX and Power BI. This post focuses on the CALCULATE function, which is a unique function in DAX. The CALCULATE function has the ability to alter filter contexts, and therefore can be used to enable extremely powerful and complex processing. This post covers some of the most common scenarios for using CALCULATE, and some of the gotchas in the way in which these different features interact!
Learning DAX and Power BI - Filter Contexts

Learning DAX and Power BI - Filter Contexts

Carmel Eve

Here is the first in a series of blog posts around understanding DAX and Power BI. This post focuses on filter contexts. which are a central concept which is vital for being able to write effective and powerful DAX!In this series Carmel walks through the main ideas and syntax surrounding the DAX language, and provides examples of using these over a dataset. DAX is an extremely powerful language. Using these techniques it is possible to build up complex reports which provide the insight you really need!
Learning DAX and Power BI – Row Contexts

Learning DAX and Power BI – Row Contexts

Carmel Eve

Here is the second blog in a series around learning DAX and Power BI. This post focuses on row contexts, which are used when iterating over the rows of a table when, for example, evaluating a calculated column. Row contexts along with filter contexts underpin the basis of the DAX language. Once you understand this underlying theory it is purely a case of learning the syntax for the different operations which are built on top of it.

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 - was released in April 2021. 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.

Carmel worked at endjin from 2016 to 2021.