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

Sorry, Daryl, but NO.

SUMMARIZE is the source of most obscure numbers. I wrote about it several times, the last one was here: All the secrets of SUMMARIZE - SQLBI. The suggestion is the same since the beginning of DAX: do NOT use SUMMARIZE to compute measures, unless you love spending nights debugging your code.

I am still investigating on the topic, and it could take a long while. It seems to be a really weird scenario where auto-exists creates issues, as it might be a bug somewhere in the engine.

The original formula is just perfect: an iteration, context transition, an aggregation. There are multiple workarounds, even though they are what they are: workarounds. The formula, by itself, should already work.

For example, adding KEEPFILTERS around VALUES reduces the problems of auto-exists and makes the formula work:

test recovery =
SUMX(
    KEEPFILTERS ( VALUES('Test Table'[MLP] ) ),
    CALCULATE(MAX('Test Table'[Recovery]))
)

Another simple solution requires using a variable:

Using Addcolumns =
VAR A =
    ADDCOLUMNS (
        VALUES ( 'Test Table'[MLP] ),
        "@Result", CALCULATE ( MAX ( 'Test Table'[Recovery] ) )
    )
RETURN
    SUMX ( A, [@Result] )

Nonetheless, despite working, these are workarounds. I still don't know exactly the reason for that behavior, therefore I cannot suggest one solution against the other.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com