Filter and Allexcept

Hi Datatouille

This page contains an explanation of what you are observing:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

To roughly quote from that page:

"An ALL(Calendar) statement is automatically applied when you apply a filter over a column of Date type that is the primary key in a relationship."

In your case, both of these tables used as SetFilter arguments for CALCULATE contain the column Calendar[Date]:

• FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month = 3 )
• FILTER ( ALL ( Calendar ), Calendar[Month = 3 )

So the DAX engine will automatically remove all filters on Calendar within the CALCULATE function when these are used as SetFilter arguments, by automatically adding ALL ( Calendar ) as a SetFilter argument.

In effect it will translate:

[Measure1] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 )
)

to

[Measure1] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER ( ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = 3 ),
    ALL ( Calendar )
)

and

[Measure2] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER ( ALL ( Calendar ), Calendar[Month] = 3 )
)

to

[Measure2] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER ( ALL ( Calendar ), Calendar[Month] = 3 ),
    ALL ( Calendar )
)

In the case of Measure1, the intended effect of ALLEXCEPT was lost because ALL ( Calendar ) was automatically applied on top.

In the case of Measure2, it didn't make any difference because you were already filtering ALL ( Calendar ) anyway.

For the effect you were looking for with Measure1 (i.e. retaining any existing filters on Calendar[Year] but setting Month = 3) you could use a measure like:

[Measure1 V2] =
CALCULATE (
    SUM ( Sales[Quantity] ),
    ALLEXCEPT ( Calendar, Calendar[Year] ),
    Calendar[Month] = 3
)

Using ALL or ALLEXCEPT as a top-level argument for CALCULATE (rather than within FILTER) invokes the 'remove filters' behaviour of these functions, but doesn't add the corresponding table to the filter context. This avoids triggering the automatic ALL ( Calendar ) being added.

Some discussion of this here as well:

http://mdxdax.blogspot.co.nz/2011/03/logic-behind-magic-of-dax-cross-table.html

Regards,

Owen :)