Forum Discussion
Why does this formula work? Filter(all(...
Anonymous
Yes, I know it is confusing...It takes a while to get your head around it (I've been at this for over 3 years and I learn stuff every day!).
We are back to a previous stage in the explanation. The whole FILTER expression contains 2 parts:
1) ALL removes the filters from the date table
2) THEN the second filter expression kicks in. What it's doing is seeing an unfiltered dates table (since ALL has removed the filters), and then it returns the values established in the second expression over the whole dates table. Since the second filter expression checks the week number column, which does have a value "selected", it applies the new filter based on that selected value.
Your second expression refers to the value selected in the week number column and a year. So it applies the filters accordingly over the whole date table.
Hi, Anonymous
When getting formatted via daxformatter.com, the measure is way much easier to analyse.
YTD Week =
CALCULATE (
SUM ( 'order'[Qty] ),
FILTER (
ALL ( 'Date' ),
'Date'[Week Number] <= MAX ( 'Date'[Week Number] )
&& 'Date'[Week Year] = MAX ( 'Date'[Week Year] )
)
)To some extent, you're right about ALL('Date'); it does return a whole 'Date' table by removing any filters; but then it's filtered by conditions "'Date'[Week Number] <= MAX ( 'Date'[Week Number] ) && 'Date'[Week Year] = MAX ( 'Date'[Week Year] )".
But should this not then return all weeks in the table...all 52 weeks and not just to week 5 that the slicer filters to?
*Bump* - cant find the solution to this anywhere
Anonymous you think MAX ( 'Date'[Week Number] ) returns the 52th week of the year? What dissapoints you is that it's NOT the case.
As MAX ( 'Date'[Week Number] ) corresponses to
MAXX( 'Date', 'Date'[Week Number] )you get the week number you've chosen. It's all about filter context in DAX. Sounds a bit off the topic from your issue but it's the very conerstone of any DAX calcuation. Here's a classic article on such a subject; one can never read it too many times.
CNENFRNL Thank you for the article, i will read it later today.
I'm actually happy with the original solution, i just didn't understand why it worked. i.e. why the all('date table') didn't remove the slicer filter eventhough the slicer filter uses the week column in the date table! I still dont!
Anonymous It seems to me that you didn't wrap your head around the order of calculation here. For FILTER func,
- it evaluates in the first place ALL('Date'), the full table of 'Date'
- secondly all those filtering criteria, i.e. 'Date'[Week Number]<=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year]) is applied to full 'Date' table.