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.
Solved! Go to 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]:
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 🙂
Proud to be a Datanaut!
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.
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]:
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 🙂
Proud to be a Datanaut!
@OwenAuger, that's a damned good and excellently illustrated explanation of the issue and/or subject.
best regards
Oxenskiold.
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
Hi,
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 ?
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 !
Hi again Tristan!
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 |
[Measure1 V3] = VAR FilterMonth = <any expression> RETURN CALCULATE ( SUM ( Sales[Quantity] ), ALLEXCEPT ( Calendar, Calendar[Year] ), Calendar[Month] = FilterMonth )
Proud to be a Datanaut!
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
We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.
Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!
User | Count |
---|---|
164 | |
82 | |
76 | |
41 | |
40 |