Stop Repeating CALCULATE: Iterate Over Categories in DAX

tl;dr: Ever needed to run the same logic for a few specific categories, but didn’t know about iterators? So you went with the obvious choice – write CALCULATE() five times, once for each category. Yeah, been there. But there’s a less dreadful way – using SUMX, AVERAGEX, or whatever X-factor you’re into. In this article, I’ll show you how to use them to clean up your DAX without losing your mind.

CALCULATE fixes everything, right?

When you start writing DAX, discovering CALCULATE is like discovering fire in the ancient days – a total game changer. But it’s easy to get carried away and start solving everything with CALCULATE instead of looking for better options. And if we wanna stretch the fire analogy... yeah, you can absolutely burn down your semantic model with CALCULATE overkill.

Forbidden syntax

So, when exactly is enough... enough?

Imagine this: you have a calculation you need to run on the category level, let's say department efficiency. If you’re new to DAX (or you’ve been living in Excel for a bit too long), your first instinct might be to write a separate calculation for each department. Then you sum them all up and divide by the number of departments. Boom. Done.

I mean… it technically works. But how scalable is that?

And if you think I’m being mean, nah. I’m just describing what I did when I started with DAX. Kinda. Maybe. Who knows.

CALCULATE(
    [efficency],
    dim_department[departmentName] = "Finance"
)

CALCULATE(
    [efficency],
    dim_department[departmentName] = "IT"
)

CALCULATE(
    [efficency],
    dim_department[departmentName] = "Legal"
)

( [efficency Finance] + [efficency IT] + [efficency Legal] ) / 3 

So this would be your setup, perhaps, if you're feeling cheeky. You might even argue: hey, just use DISTINCTCOUNT/COUNTROWS instead of hardcoding 3, throw in a DIVIDE() instead of /, and it’s all perfectly fine.

Well… it runs, sure.  And maybe you do want each department's result shown in its own Card visual, and that's legit. But if you're trying to aggregate this logic into a proper total, nah. This is fundamentally wrong. DAX gives you exact tools to handle this kind of thing, and this is not it.

X-factor

If you want to go over departments – or “iterate” – and calculate something for each, then aggregate the results somehow… well, as the name suggests, you wanna use iterators.

These functions do exactly what you'd expect: go row by row, evaluate an expression, and then do something with the outcome. Add it up, average it, whatever. I bet you've even heard of them. Do SUMX or AVERAGEX ring a bell?

And you’ve probably even used iterators already – maybe that classic tutorial example:

SUMX('Table', [Quantity] * [UnitPrice])

Row by row, sum it up. Makes total sense when you're looping through actual table rows.

But here’s the thing – it doesn’t have to be a fact table. If you’ve got a proper dimension set up, or even spin up a quick virtual one, it works the same. Departments are rows too. You’re still looping, just not through transactions, but through categories.

Table vs Table Expression

Even though IntelliSense (and the official docs) tell you that SUMX, AVERAGEX, etc. require a table and an expression, what it really means is: an expression that returns a table — aka a table expression.

And that’s where the fun begins. You’re no longer stuck with physical tables – you can start creating virtual ones. (Big disclaimer though: don’t go overboard here. Abuse this power, and you might end up doing more damage than stacking CALCULATE()s everywhere.)

One of the simplest ways to introduce virtual tables is the VALUES() function. It accepts a table or a column, and if you give it a column, it returns the distinct values of that column.

Technically, it gives you a one-column table with the name of that column.

Just keep one thing in mind: if you're building virtual tables to iterate over, keep them as small as possible. But don't worry, if you're using VALUES(), you're most likely still in the safe, useful zone.

The Good Stuff

Okay, let’s put it together. We want to take the three departments, calculate efficiency for each, and then average the values. First, you need the efficiency measure — or you could write the logic directly inside the AVERAGEX function. But I usually have the measure ready anyway, since I reuse it in other calculations, including cases where I don’t need it broken down by department.

The second part is the table we're iterating over. Since our dim_department only includes those three departments, we can go with:

avg efficiency per department = 
AVERAGEX(
    VALUES( dim_department[departmentKey] ),
    [efficiency]
)

It's pretty simple, right? We're just iterating over departmentKey and calculating the [efficiency] measure for each one. The result gives us exactly what we want: clean, aggregated efficiency across departments.

Efficiency vs Aggregated efficiency

You can see that if we just use the regular [efficiency] measure, it doesn’t calculate it for each department and then average; it just runs once, without any department context. Looks fine inside a department breakdown, but totally wrong when you need an actual average across departments outside the context, a.k.a. in the total.

The per-department version, though? That one works no matter the context. Inside a visual, it gives you the same numbers. But outside, it still properly averages the individual values that came from the filtered department rows.

Filter iterated items

Iteration does not somehow differ from other techniques, and even here you can filter with CALCULATE. Hey, it's even highly recommended to filter the iterated items so your processing time is lower.

Given I told you we wanna filter the table that we iterate over, which in our case is represented by VALUES(), your immediate thought (and that would be mine as well) is to filter the table directly when we define the table in the first parameter of X factor (that's not official btw) functions such as SUMX or AVERAGEX.

So you could end up doing something like this:

avg efficiency per department filtered = 
AVERAGEX(
    CALCULATETABLE(
        VALUES( dim_department[departmentKey] ),
        dim_department[departmentName] IN {"Finance", "IT", "Legal"}
    ),
    [efficiency]
)

Or, using a neat trick, since {} is a table constructor (in this case, you'll get a one-column table, with the column named Value and three rows containing the values), you could do this:

avg efficiency per department filtered treatas = 
AVERAGEX(
    TREATAS(
        {"Finance", "IT", "Legal"},
        dim_department[departmentName]
    ),
    [efficiency]
)

But we can do something different, which is 1) more natural and 2) quicker to process. Because CALCULATE first takes care of anything in the filter parameters and then does the calculation, we can limit the items we iterate over with a known technique using CALCULATE:

CALCULATE (
    AVERAGEX (
        VALUES ( dim_department[departmentKey] ),
        [efficiency]
    ),
    KEEPFILTERS ( dim_department[departmentName]
        IN {
            "Finance",
            "IT",
            "Legal"
        }
	)
)

We just wrap our standard measure with CALCULATE, so we can apply the filter and move the department filtering there. Notice we also use KEEPFILTERS, so we don’t overwrite the existing filter context for each visual row in a table visual, for example. From my testing, the difference between the CALCULATETABLE version and this version isn’t huge on its own, but the CALCULATE with KEEPFILTERS version starts performing better when used in visuals. And that’s what actually matters.

Nested iterators

Naturally, as you move along, you may run into cases where you feel like you need to use an iterator inside another iterator. This is valid, but it is also something you generally want to avoid. Depending on how your model is built, you might end up

triggering thousands and thousands of nested iterations, which may not be optimal.

As I said earlier, the golden rule is to keep the number of iterations low. Ideally, you don’t want to iterate over the full physical tables. It usually makes way more sense to iterate over a single-column table using VALUES(). That’s usually the go-to move.

If you need more than one column in your iteration, VALUES() won’t cut it. That’s where SUMMARIZE() comes in. You can list the columns you want and build your own custom table to iterate over. Just keep in mind, SUMMARIZE() only works well when the columns come from related tables.

As you’ll see later, if you want to add new "columns" to your virtual tables, don’t put the expression inside SUMMARIZE. Even though it technically works, always go with ADDCOLUMNS instead. It’s generally better for performance, see more here @SQLBI

Now, let’s talk about a common trap. Imagine you want to calculate efficiency for each department first, and then take the average of those department-level results for a specific day.

You might think you can iterate over two dimensions directly, like dim_department and calendar. Let’s say you want to use SUMMARIZE to get distinct values from both tables in one go. Unfortunately, no. These two don’t talk to each other. In a typical star schema, dimensions aren’t connected.

You need a matchmaker, a bridge table, and in this setup, that means the fact table.

Just to be clear, I don’t mean a proper bridge table like the ones used in many-to-many relationships. I’m just calling it a bridge because it acts like glue, helping disconnected dimensions work together for iteration.

So, if your model allows it and the measure you’re working with is simple enough, the cleanest way forward is to iterate over the fact table. Since both dimensions are already linked to it, you can pull their keys and get valid combinations like this:

efficiency iterative =
var _tbl =
    ADDCOLUMNS(
        SUMMARIZE(
            'fact',
            'calendar'[date],
            'dim_department'[departmentKey]
        ),
        "@eff", [efficiency]
    )
var result =
    AVERAGEX(
        _tbl,
        [@eff]
    )
return
    result

This works when the measure is simple and doesn't use other fact tables. Technically, variations of this (such as CROSSJOIN, VALUES as filter, etc.) will work with more complex measures either way, as long as you're inside the filter context of the mentioned dimensions. But the issue occurs when you want to accurately calculate the average with no visual filter context, such as Totals in a matrix or a Card visual. Then you'll see it's not adding up as it should.

The most straightforward approach and fix for more complex measures is double iteration (or even more, depending on how many dimensions you're dealing with). I wouldn’t call it pretty, but it gets the job done. You just wrap one X-factor function inside another, like this:

efficency iterative = 
AVERAGEX(
    VALUES(dim_date[date]),
    AVERAGEX(
        VALUES(dim_department[departmentKey]),
        [efficiency])
    )

It’ll take all available date values in your current context, and for each of those dates, it’ll loop through all available departments, calculate the average per department, and then average those results into a single value. Then it moves on to the next date. Later on, if you use different columns from the same dimension tables, like Week, Department Name, or Department Category, basically anything with the same or different granularity, it should still calculate accurately.

Conclusion

If you’ve been solving category-level logic with repeated CALCULATEs, it’s time to level up. Iterators give you cleaner and more flexible measures. Together with learning about table expressions and functions related to this topic, such as VALUES and SUMMARIZE, you can avoid repeated CALCULATEs for good.