SUMX producing wrong result when filtered in both row context and filter context

Hi jaap_olsthoorn ,  

I am interested to know what suggestions you received that fixed the problem.

Looking at the DAX, I was wondering why you needed to include the CALCULATE within the SUMX expression.  I tried to remove it, but found that it broke the Sub-Total.

The Sub-Total without CALCULATE does not work because the VALUES function creates a table with 2 rows - both with the max value of 230 - hence the SUM = 460.

When you add the CALCULATE it appears to change the filter context, the VALUES contains 2 rows with max value of 120 and 230, so the SUM is correct.

Unfortunately, including CALCULATE breaks the filter context when start to include addition fitler without include the additional filter in the visual ROW Context.  Consider the following examples.

Versus

It appears in the execution plan the the MAX Function is considering the Owner and Filter columns only without applying MLP filter, so it sums 120 and 230 as VALUES includes both MLP. 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"1"}, 'Test Table'[Filter])

  VAR __DS0FilterTable2 = 
    TREATAS({"Babymarkt"}, 'Test Table'[Owner])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Test Table'[Owner], 'Test Table'[MLP]), "IsGrandTotalRowTotal"),
      __DS0FilterTable,
      __DS0FilterTable2,
      "test_recovery", 'Test Table'[test recovery]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Test Table'[Owner], 1, 'Test Table'[MLP], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Test Table'[Owner], 'Test Table'[MLP]

The following show how the CALCULATE is removing the MLP filter context for the Rows.

DEFINE
    MEASURE 'Test Table'[test recovery] =
        SUMX (
            VALUES ( 'Test Table'[MLP] ),
            CALCULATE ( MAX ( 'Test Table'[Recovery] ) )
        )
    MEASURE 'Test Table'[test count] =
        SUMX (
            VALUES ( 'Test Table'[MLP] ),
            CALCULATE ( COUNTA ( 'Test Table'[MLP] ) )
        )
    MEASURE 'Test Table'[test no calculate] =
        SUMX ( 
        	VALUES ( 'Test Table'[MLP] ),
        	MAX ( 'Test Table'[Recovery] ) 
        )
    MEASURE 'Test Table'[test count no calculate] =
        SUMX ( 
        	VALUES ( 'Test Table'[MLP] ), 
        	COUNTA ( 'Test Table'[MLP] ) 
        )

EVALUATE
SUMMARIZECOLUMNS (
    'Test Table'[Owner],
    'Test Table'[MLP],
    TREATAS ( { "1" }, 'Test Table'[Filter] ),
    TREATAS ( { "Babymarkt" }, 'Test Table'[Owner] ),
    "test_recovery", 'Test Table'[test recovery],
    "test_count", 'Test Table'[test count],
    "test_no calculate", 'Test Table'[test no calculate],
    "test_count_no calculate", 'Test Table'[test count no calculate]
)

EVALUATE
SUMMARIZECOLUMNS (
    'Test Table'[Owner],
    'Test Table'[MLP],
    'Test Table'[Filter],
    TREATAS ( { "1" }, 'Test Table'[Filter] ),
    TREATAS ( { "Babymarkt" }, 'Test Table'[Owner] ),
    "test_recovery", 'Test Table'[test recovery],
    "test_count", 'Test Table'[test count],
    "test_no calculate", 'Test Table'[test no calculate],
    "test_count_no calculate", 'Test Table'[test count no calculate]
)