Understanding CONTEXT for DAX - Using FILTER() vs not using it in CALCULATE - different results

[tljthree]

I'm trying to better understand the order/manner of determining filter context, and came across a conundrum that I couldn't explain.

The 2 measures below produce significantly different results.

(A) CALCULATE(SUM([SalesAmount]), LASTDATE(DimDate[FullDateAlternateKey]) )

(B) CALCULATE(SUM([SalesAmount]),FILTER(DimDate,LASTDATE(DimDate[FullDateAlternateKey])))

...

When used in a PivotTable with a context of Year=2001:

(A) aggregates sales from the last day of the year 2001
(B) aggregates sales from all days of the year 2001

...

Can someone please help me understand the development of the context for (A) and (B), so I can better get how PowerPivot works?

Thanks for anything you can do to clear my fog.

Tom

 

[powerpivotpro]

Hi Tom. When LASTDATE is used as a "raw" argument to CALCULATE (example A), it is allowed to do its special magic, which is that it navigates the Date field to the last date.

But the second argument to FILTER is a boolean true/false expression that is evaluated for every row in the table argument (the first argument to FILTER).

Since LASTDATE returns a date (and not 0 or FALSE), it is considered to be TRUE for each row in DimDate, so all rows are used.

I explain FILTER in some detail in my book, but here's a post that covers some of this as well (scroll down to How Does FILTER Work?)

The Greatest Formula in the World, Part 3 « PowerPivotPro

 

[tljthree]

Super! Thank you! By the way, I purchased your book on Kindle, and a colleague has purchased the physical book.

Because I'm a newbie, not everything sinks in well the first time I read it.

Thanks for clearing my fog.

 

[powerpivotpro]

Great! I understand completely btw, the "not sink in well first time" thing. I tend to grind at things, slowly, rather than quickly/sharply cutting to the heart of things. I think being "dumb" in that way helped me to write a more approachable PowerPivot book, actually, so it's not a bad thing really.

 

[RoryA]

I tend to grind at things, slowly, rather than quickly/sharply cutting to the heart of things.

Ditto - hence am really enjoying your book! (even if the fonts look a little weird to me on the Kindle)