CODEWITHSUNDEEP
powerbidax
Maile Cupo
Maile Cupo

Reputation: 748

Understanding the DAX CALCULATE function

I am working on a model in Power BI that has two datasets:

Set_1 (just a list of each group name)

Group:
1
2
3

and Set_2, a bunch of values per group in a different dataset:

Group:    Value:
1         10
1         20
1         -7
2         100
2         -25
3         45
3         15
1         3

The tables are related by group. I want to create a measure on Set_1 that shows the sum of the values by group in Set_2. I can do so with the following DAX formula:

GroupSum = CALCULATE(SUMX(Set_2, Set_2[Value]))

looks like this

Group:    GroupSum:
1         26
2         75
3         60

But I don't understand why the CALCULATE function, which doesn't take any filter contexts as parameters works the way it does in this instance. Without the CALCULATE function,

GroupSum = SUMX(Set_2, Set_2[Value])

looks like this:

Group:    GroupSum:
1         161
2         161
3         161

Which makes sense. I just need help understanding how the Calculate function works, specifically when it isn't passed any filter parameters.

EDIT: The answer lies in the concept of "context transition" as pointed out below.

Upvotes: 1

Views: 1343

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Using the CALCULATE function makes the DAX perform a context transition.

CALCULATE transforms all existing row contexts into an equivalent filter context before applying its filter arguments to the original filter context.

For more detail on this, check out the site I quoted above:
Understanding Context Transition.

In your example, the value in the Group column of each row acts as a filter when you use CALCULATE as if you had written something like CALCULATE(SUM(Set_2[Value]), Set_2[Group] = 1). Even though it doesn't have an explicit filter, the row context acts as a filter.

Upvotes: 1

Related Questions