Why does this formula work? Filter(all(...

[Anonymous]

Hi

The following formula allows me to sum YTD sales to a week selected on my date slicer:

YTD Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Number]<=max('Date'[Week Number]) && 'Date'[Week Year]= max('Date'[Week Year])))

So when I select week 5 on the slicer, it will sum up weeks 1 to 5. However I thought the 'FILTER(ALL('date')' part of the formula would remove my slicer filter because it removes all table filters does it now? I would therefore expect it to bring back ytd to the max week number.

What am I misunderstanding?

[CNENFRNL]

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] )".

[Anonymous]

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?

[CNENFRNL]

@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.

http://mdxdax.blogspot.com/2011/03/

[Anonymous]

@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!

[CNENFRNL]

@Anonymous It seems to me that you didn't wrap your head around the order of calculation here. For FILTER func,

1. it evaluates in the first place ALL('Date'), the full table of 'Date'
2. 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.

[Anonymous]

@CNENFRNL 

Thanks for the swift response. Sorry if i'm being dense but surely this means that by clearing the filters from my table it provides the full list of weeks, not just those filtered in the slicer? 

1. it evaluates in the first place ALL('Date'), the full table of 'Date' - so it removes the slicer filter which filtered to week 5
2. 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.  and therefore it should bring back week 1 to 52 as there are 52 weeks in the table

?

[PaulDBrown]

@Anonymous 

The logic follows the order you have stated. What seems to be confusing you is the MAX function used in the second expression. MAX will return the "max" value in the filter context. So if the filter context is one date/week/year, it returns THAT value. Think of it as acting like SELECTEDVALUE. (you can in fact use SELECTEDVALUE instead of MAX).

[Anonymous]

@PaulDBrown @CNENFRNL 

Thank you both for your patience. Day 3 of Power BI and my head is melting. So my misunderstanding is where filter context applies.

So max returns 5 because the filter context is set by the slicer which is set to week 5. Can i ask why the filter context is not 52 at this point because ALL is executed first thereby removing the slicer filter context? Why is the filter context still 5?

[PaulDBrown]

@Anonymous 

Think of it this way:

By using ALL, you remove the filters on the table/column specified. By adding the filter expression, you are now specifying or adding a new filter on the dates table determined by the expressions therein. A bit like "firstly remove the filter on the dates table and now apply this filter instead". 
Again, the MAX function is acting like SELECTEDVALUE. So it "looks" at the slicer (which you have referenced in the filter expression), sees week 5, and establishes that value as the reference for the new filter expression.

Something I tend to do to see what's going on is set up a table visual with the filtered column and the measure. You can then see what the  measure returns for each filter context ("row", though you should never think of it as an Excel type row if you are using the column in a visual; the concept of "row" in the Excel sense is ONLY applicable in the context of a Data/Dimension table in the model) of the table.

[Anonymous]

@PaulDBrown Ok so we remove the filter on the week column (and all table columns because it references the table in its entirety) which allows for the exposure of all weeks in the data table. But because we're using the max function, it refers to the slicer for the filter reference.

I guess then why is the slicer being used as a filter reference and not the max week in the table (52) without the filter? Why does the slicer come back into play? If we wanted to remove the role of the slicer, what would you do differently?

Thank you for this recommendation - it'llhelp me break the problem down moving forward:

Something I tend to do to see what's going on is set up a table visual with the filtered column and the measure. You can then see what the  measure returns for each filter context ("row") of the table.

[PaulDBrown]

@Anonymous 

The slicer comes back into play because the filter expression refers to the column (Date [Week Number]) used in the slicer

The easiest way to ignore a slicer is to remove the visual interaction under Format in the ribbon, "Edit interactions". (But this will affect all the measures in the visual)

You can of course ignore a slicer selection by simply using ALL by itself in the filter expression: if you reference a Table, it removes all filters from that table; if you reference a column, it removes any filter applied to that column

[Anonymous]

@PaulDBrown  you must frequently hit your head off a table in frustration dealing with learners like me!

"The slicer comes back into play because the filter expression refers to the column (Date [Week Number]) used in the slicer" - so our ALL function never removes the role of the slicer, it unfilters the data table but the slicer is still 'alive' so when filter expressions are used it filters with the slicer again? 

[PaulDBrown]

@Anonymous 

Correct. The ALL function removes a specified filter on a table/column (it doesn't "erase" a filter)

The filtered column ("Slicer") comes into play again because the filter expression explicitly refers to that column. In this case, the MAX function is returning the selected value in the filtered column ("Slicer").

[Anonymous]

@PaulDBrown 

Sorry Paul, this is the bit that still doesnt add up:

The filtered column ("Slicer") comes into play again because the filter expression explicitly refers to that column - this column shouldnt have a filter now though because 'ALL' removed it. This specific issue is what's causing the confusion for me. 

Sorry if you've already answered this in a different way previously.

[PaulDBrown · Accepted solution]

@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.

[Anonymous]

Thank you @PaulDBrown  and @CNENFRNL  , truly appreciated your help!

[CNENFRNL]

@Anonymous 

1. it evaluates in the first place ALL('Date'), the full table of 'Date' - so it removes the slicer filter which filtered to week 5 - Yes, that's right
2. 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.  and therefore it should bring back week 1 to 52 as there are 52 weeks in the table - max('Date'[Week Number]) returns the week number in the slicer, i.e. week 5, coz it's subject to filter context

[Anonymous]

*Bump* - cant find the solution to this anywhere