Why does Quantity LY not work?

[michellepace]

Hello.

I recently posted a question Must I explicitly use "filter( )" when using CALCULATE?  , the answer was yes. Below are two DAX expressions. The first returns a correct result, the second does not. Question: Could someone please explain why the second DAX function does not work as expected? I thought I was, in fact, applying best practices by explicitly stating FILTER( ). 

Total Quantity LY (right) =   CALCULATE ([Total Quantity], SAMEPERIODLASTYEAR ( Dates[Date] ))

Total Quantity LY (wrong) = CALCULATE ([Total Quantity], FILTER (Dates, SAMEPERIODLASTYEAR ( Dates[Date] ) ))

Regards,
Michelle

[daxer-almighty]

The second argument to FILTER must always be a logical condition. Bear in mind that in DAX sometimes an implicit conversion of expressions takes place but you should not rely on this if you want to keep your sanity and not make other developers curse you. In your second formula you're using SAMEPERIODLASTEAR but it's a table function, not a logical function. However, I guess DAX turns this into TRUE every time the function returns a non-empty table. Hence you're get something undesired. Please always pay attention to parameters in functions and know the type of data expressions return. Beware of automatic conversions unless you know precisely they are happening and for a good reason. As for filtering under CALCULATE... All arguments of CALCULATE starting from the second on must be tables. And the always are, even though you sometimes see syntactic sugar like T[Col] = "value". This in fact is a table under the hood. When it comes to filtering, there's a golden rule in DAX: Never filter a table when you can filter a column. This is to do with how DAX works and performance.

[michellepace]

@Greg_Deckler  and @daxer-almighty , thank you both. I'll go through the link as well as an explanation. I'll go through both in detail in the morning. I've been struggling all day. The more I have people such as yourself replying to my questions the more I realise how much I have to learn. Thank you. I should have jumped on this boat 10 years ago! 🙂

[daxer-almighty · Accepted solution]

OK. Here's the full and ultimate explanation of what's going on. It's enough to check what this

FILTER (
    Dates,
    SAMEPERIODLASTYEAR ( Dates[Date] ) 
)

does. FILTER is an iterator, so it takes what's currently visible in Dates and goes through this row by row. What does SAMEPERIODLASTYER do? Let's see what dax.guide/sameperiodlastyear/ says:

Remarks

The dates argument in SAMEPERIODLASTYEAR can be any of the following:

• A reference to a date/time column. Only in this case a context transition applies because the <Dates> column reference is replaced by
  • CALCULATETABLE ( DISTINCT ( <Dates> ) )
• A table expression that returns a single column of date/time values.
• A Boolean expression that defines a single-column table of date/time values.

In other words, what happens under FILTER is this:

FILTER (
    Dates,
    SAMEPERIODLASTYEAR (
        // Here is where context transition
        // happens...
        CALCULATETABLE ( DISTINCT ( Dates[Date] ) 
    )
)

So what is it that SAMEPERIODLASTYEAR sees? Well, it sees the date that belongs to the row that is currently being iterated. Takes this value and moves it back 1 year. Now is the fun part. If it so happens that the shifted date is not BLANK, the row will be included, if it is BLANK (because you've fallen off the edge of the calendar), the row will be filtered out. So, what you get as a result depends on which chunk of dates you see but the semantics of the code is this: From the currently visible dates return only those that do not hit BLANK when shifted back one year.

This is the full and ultimate explanation of what you see.

[michellepace]

Hi @daxer-almighty Thank you, that is a super explanation. I get it now 🙂

[Greg_Deckler]

@michellepace - I am not sure who gave you the advice that you must always explicitely use FILTER in a CALCULATE but that is bad advice. You do not and there is actually guidance that you should avoid doing so.

https://docs.microsoft.com/en-us/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument

But then again, I tend to avoid using CALCULATE.

[CNENFRNL]

I notice you authored formulae this way in both of your posts,

FILTER( tbl, filtering_exp )

 I infer that you assume the whole intact tbl is filtered by filtering_exp (correct me if my guess is wrong); as matter of fact, it's not the case. It's crystal clear that evaluation context matters anytime, anywhere in DAX. No exception to FILTER() as well; tbl has already gone through initial context filtering before entering FILTER.

Jeffrey wang, one of founding fathers of VertiPaq engine, revealed some details of the way filter contexts work,

http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html 

Although wrote in 2011, almost a decade ago, it functions as a most accurate compasses for nagivating thru DAX!

[AntrikshSharma]

@michellepace SAMPERIODLASTYEAR is a table function and you are using it in the second argument of FILTER where boolean conditions are evaluated to return the list of values from the first argument of FILTER that satisfy the criteria specified in the second argument.

Both FILTER and SAMPERIODLASTYEAR are doing their job and both are getting evaluated in the filter context, how to verify that SPLY is evaluated in the filter context? SAMPERIODLASTYEAR ( Dates[Date] ) is equivalent to saying SAMPERIODLASTYEAR ( VALUES ( Dates[Date] ) )

But the presence of SPLY has no impact on the FILTER ( Dates ) part, and you are getting the result similar to the current year.

Let’s say filter context is 2020. refer to the comments in the below image:

But when you use just SAMPERIODLASTYEAR, it takes the current dates and shifts back one year and gets the dates of 2019 and applies it to the Filter context and hence you get the correct result.