CODEWITHSUNDEEP
dax
user3528314
user3528314

Reputation: 45

Why is this SUMX function in DAX is working differently than expected?

I am a beginner in DAX. I have a measure called "Actuals" and a table called "Accounts" with a column named "sign" - which is 1 for incoming cash flow and -1 for outgoing. Using these a new measure is defined as below:

Actuals with sign = SUMX("Accounts", [Actuals]*"Accounts[sign]")

From my understanding, this should iterate over rows of Accounts and multiply the value in sign column with the measure called "actuals". To me this seems equivalent to taking the sum of sign column and multiplying with "actuals" measure. But the result shows otherwise. Is my understanding incorrect ? how exactly is the calculation being done here ?

Upvotes: 0

Views: 768

Answers (1)

AntrikshSharma
AntrikshSharma

Reputation: 661

Context Transition is a process which transforms all the columns of the currently iterated row into an equivalent filter context, which in turn filters the model. We think that only explicitly mentioning CALCULATE can initiate Context Transition but the truth is every measure has a hidden CALCULATE around it:

So your code

Actuals with sign =
SUMX (
    'Accounts',
    [Actuals] * 'Accounts[sign]'
)

Is internally transformed into :

Actuals with sign =
SUMX (
    'Accounts',
    CALCULATE ( [Actuals] ) * 'Accounts[sign]'
)

The problem is if you don’t have at least 1 column with unique values then the result returned after context transition is inflated as when it is time to filter the model duplicated rows are transformed into filter context 1 by 1 and then they filter the rows that have been iterated earlier. Because internally there is not concept of Previous or Next, the engine only knows that it has to filter the model.

So if you have 2 duplicated rows, row 1 and 5 then at row 1 Context Transition will filter row 1 and row 5 and at row 5 Context Transition will filter row 5 and row 1 and then SUMX will SUM the values which will inflate the numbers.

Here is a small representation of how it happens: enter image description here

Upvotes: 3

Related Questions