Why does the DAX formula in my calculated column use propagation to filter in one instance and not in another?

Question

Suppose I have a couple of tables:

fTransactions

 Index   ProdID  RepID  Revenue
   1        1       1      10
   2        1       1      20
   3        2       2      30
   4        2       2      10

dSalesReps

RepID   RepName   CC1   CCC2   
1       joe       40    70
2       sue       30    70
3       bob             70

CC1 contains a calculated column with:
CALCULATE(SUM(fTransactions[Revenue]))

It's my understanding that it's taking the row context and changing to filter context to filter the fTransaction table down to the RepID and summing. Makes sense per an sqlbi article on the subject:

"because the filter context containing the current product is automatically propagated to sales due to the relationship between the two tables"

CC2 contains a calculated column with:
SUMX(fTransactions, CALCULATE(SUM(fTransactions[Revenue]))

However, this one puts the same value in all the columns and doesn't seem to propagate the RepID like the other example. The same sqlbi article mentions that a filter is made on the entire fTransactions row. My question is why does it do that here and not the other example, and what happened to the propagation of RepID?

"CALCULATE places a filter on all the columns of the table to identify a single row, not on its row number"

RADO RADO · Accepted Answer · 2020-03-15T23:39:17

A calculated column is created in a loop: power pivot goes row by row and calculates the results. CALCULATE converts each row into a filter context (context transition).

In the second formula, however, you have 2 loops, not one: First, it loops dSalesReps table (because that's where you are creating the column); Second, it loops fTransactions table, because you are using SUMX function, which is an iterator.

CALCULATE function is used only in the second loop, forcing context transition for each row in fTransactions table. But there is no CALCULATE that can force context transition for the rows in the dSalesReps. Hence, there is no filtering by Sale Reps.

Fixing the problem is easy: just wrap the second formula in CALCULATE. Better yet, drop the second CALCULATE - it's not necessary and makes the formula slow:

CCC2 =
CALCULATE(
    SUMX(fTransactions, SUM(fTransactions[Revenue]))
)

This formula is essentially identical to the first one (the first formula in the background translates to the second one, SUM function is just a syntax sugar for SUMX).

Why does the DAX formula in my calculated column use propagation to filter in one instance and not in another?

2 Answers