Forum Discussion
CALCULATE Challenge - Round 1
- 2 years ago
I think this should qualify, though it isn't super clean.
VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Year], Dates[Date] ) VAR _DateRange_ = WINDOW ( 1, ABS, 0, REL, _AllDates_, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _Result - 2 years ago
thank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...
WINDOW isn't required. A plain filter works fine too and has been around since the beginning.
VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date] ) VAR _CurrDate = MAX ( Dates[Date] ) VAR _CurrYear = YEAR ( _CurrDate ) VAR _DateRange_ = FILTER ( _ALLDates_, YEAR ( Dates[Date] ) = _CurrYear && Dates[Date] <= _CurrDate ) VAR _Result = CALCULATE ( [Internet Sales], _DateRange_ ) RETURN _ResultHowever, there are still 15 other date intelligence measures in the file that are part of the challenge.
I'm not really interested in doing all 15 other ones as I don't think they'll reveal much that this YTD example doesn't already. If there is one that you think is meaningfully different, let me know.
- 2 years ago
Greg_Deckler, PW is an easy case where the TI solution or something like the following is just as fast.
CALCULATE ( [Internet Sales], TREATAS ( VALUES ( Dates[Prior Week Date] ), Dates[Date] ) )
The other interesting thing about this approach is that you are pretty much destroying the star schema or at least not leveraging it in the slightest.
I want to come back to this point as it is really important. SUMMARIZE uses the data model, whereas SUMMARIZECOLUMNS does not. And all the window functions explicitly ignore the data model for a myopic view on a pre-sorted virtual table. Similar for the Visual Calculations.
So the questions becomes more - do you want the data model to do the work, albeit maybe a bit slower, or do you push it aside and hand craft a high performance/ high maintenance alternative?
I agree. All window functions ignore the data model. But I didn't get the point of SUMMARIZECOLUMNS. Would you please elaborate on that?
- lbendlin2 years ago
Super User
tamerj1 Here's a very nice write-up All the secrets of SUMMARIZE - SQLBI
- tamerj12 years ago
Community Champion
Thank you lbendlin
That is one of my most favorite articles which I keep referring to every other while. However, my question was about SUMMARIZECOLUMNS and how it ignores the data model. I would really appreciate sharing some resources on this regard as it is really difficult to find detailed and insightful materials about some DAX functions which I admitt many of which I still don't fully understand.
- lbendlin2 years ago
Super User
here's another one Introducing SUMMARIZECOLUMNS - SQLBI