Forum Discussion

Datatouille's avatar
Datatouille
Solution Sage
9 years ago
Solved

Filter and Allexcept

Hello everyone,   I have  classic calendar and sales tables in my model (1 to many related with the shared column "Date"). Do you have any ideas why :    [Measure1] = CALCULATE( Sum(Sales[Quanti...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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 :)

     

EAfang's avatar
EAfang
Frequent Visitor
9 years ago

Just some observations

 

Your two measures are:

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

 

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

 

Problem 1: The closing bracket to the SUM(). 

CALCULATE( Sum(Sales[Quantity] , ....)

is NOT same as 

CALCULATE( Sum(Sales[Quantity] ), ....)

Since the format to Calculate is CALCULATE(expr, filter1, filter2,....)

Former is equivalent to CALCULATE(expr). That is 

Sum(Sales[Quantity] , FILTER(ALLEXCEPT(Calendar;Calendrar[Year]) , Calendar[Month] = 3)) is an expression due to the position of the closing brackets.

 

Problem2: As already noted above, there is no need to wrap ALLEXCEPT(Calendar,Calendrar[Year]) in a FILTER() function. Simply use it as the Filters of your CALCULATE. That is...

[Measure1] = CALCULATE( Sum(Sales[Quantity]), ALLEXCEPT(Calendar,Calendrar[Year]), Calendar[Month] = 3)

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

 

Don't forget to indicate vote and indicate answers.

Cheers