Forum Discussion

michellepace's avatar
michellepace
Icon for Resolver III rankResolver III
6 years ago
Solved

Why does Quantity LY not work?

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 ...
CALCULATE
Filter
  • daxer-almighty's avatar
    daxer-almighty
    6 years ago

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

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
6 years ago

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!