Filter and Allexcept

[Datatouille]

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[Quantity] , FILTER(ALLEXCEPT(Calendar;Calendrar[Year]) , Calendar[Month] = 3))

and

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

return the same result ? - even when the user selects a year with a slicer (from Calendar table of course).

For example when the user selects 2014 with Calendar Slicer, I would expect [Measure1] to compute the sum of quantity for 2014 and month number 3, whereas it computes the sum of quantity for month number 3 of all the years in the model...

It seems like ALLEXCEPT works as an ALL when it is used as a table argument in a FILTER. Anyone can explain ?

Thanks in advance 🙂

I am using Power Pivot in Excel 2016.

[EAfang]

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

[OwenAuger · Accepted solution]

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 🙂

[Datatouille]

Hi,

@OwenAuger

Thanks for this wonderful explanation.

Sorry for not getting back to you earlier, I hadn't checked this post for a while.

I think I get your point but I still have two questions:

1. Do you know why this ALL behaviour exists in that particular context ?

2. I am applying my filter on Year and not directly on my Date column. But apparently same behaviour, even if it is indirect right ?

@OwenAuger @EAfang

I made my initial question simple because in reality my "3" is dynamic - which is why I had to use a Filter.

Btw, do you know why Microsoft made it impossible to use "advanced" or "dynamic" filters in a raw calculate ?

Many thanks for your contribution and your very detailed examples.

Tristan

PS : I had correctly put the brackets in my measure, sorry for the spelling mistake in the initial post @EAfang !

[OwenAuger]

@Datatouille

Hi again Tristan!

1. I believe the ALL( Calendar ) behaviour is just there for convenience, and to make the time intelligence functions easier to implement. I guess it's often the case that if you provide a filter argument containing the Calendar[Date] column, you want to clear any filters on other columns of the Calendar table at the same time. This ALL behaviour helps the time intelligence functions, since those functions just need to return a column of Date values, and any other Calendar filters are automatically cleared.
2. Actually your filter argument for the CALCULATE function
   

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

   contains all columns of the Calendar table except Year, with Month = 3, for all years. In other words it contains the month of March in every year, looking something like this:
   

   Date	Month
   1/03/2014	3
   ⋮	⋮
   31/03/2014	3
   1/03/2015	3
   ⋮	⋮
   31/03/2015	3
   1/03/2016	3
   ⋮	⋮
   31/03/2016	3

   
   The important thing is that this table contains the Date column, so the DAX engine automatically clears all filters on Calendar within CALCULATE. Unfortunately, the effect of this is that your filter on the Year column is lost.
   I would say it's a bit dangerous to use
   FILTER( ALLEXCEPT ( Calendar, Calendar[Col1]), Calendar[Col2] = X )
   as an argument for CALCULATE since it probably won't have the desired effect, and instead use two arguments
   ALLEXCEPT ( Calendar, Calendar[Col1] ), Calendar[Col2] = X
3. If your month value is dynamic, you could instead use something like this to save the value as a constant:
   

   [Measure1 V3] =
   VAR FilterMonth = <any expression>
   RETURN
       CALCULATE (
           SUM ( Sales[Quantity] ),
           ALLEXCEPT ( Calendar, Calendar[Year] ),
           Calendar[Month] = FilterMonth
       )

4. When you say Microsoft made it impossible to use "advanced" or "dynamic" filters in a raw calculate, are you talking about the restriction that Boolean expressions used as arguments for CALCULATE must reference a single column, but can't reference a measure, use CALCULATE or return/scan a table?
   
   I guess it was just a design choice to provide a shorthand just for the "simple" case of overwriting filters on a single column using constant value(s), which is presumably quite common.
   
   Also, allowing more complex expressions within the Boolean expression would probably lead to unintended results.
   Since an argument within CALCULATE like Table[Col] = X is translated into FILTER ( ALL ( Table[Col] ), Table[Col] = X ), if X is a measure or contains CALCULATE then it could have different values for each value of Table[Col], whereas the intention might have been for X to take the constant value from the outer filter context.
   
   However, you can use VAR/RETURN to save a constant value to use within a Boolean expression, as in point 3 above 🙂

[Datatouille]

Awesome,

Thanks a lot Owen. I hadn't checked this post for a while...

And I have improved a lot ever since 🙂  Your explanation is really clear.

Congrats & thanks again

Tristan

[Oxenskiold]

@OwenAuger, that's a damned good and excellently illustrated explanation of the issue and/or subject.

best regards

Oxenskiold.

[BhaveshPatel]

You should use ALLSELECTED for the Year Slicer to filter your table correctly.

For the difference between ALL and ALLSELECTED, please refer to the very well explained blog at here.