Forum Discussion
Community ChampionCALCULATE Challenge - Round 1
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 _Resultthank 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.
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] ) )
Super UserThe 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. You effectively create a new table that includes all rows in the fact table summarized by Date and then create a filter clause over that. So this kind of says to me that the mantra of the star schema coupled with CALCULATE as being the silver bullet for all things DAX is effectively nonsense because you basically have to blow-up the star schema to get CALCULATE to function at any reasonable performance level.Finally, I don't think it's intuitive that the SUMMARIZEd table includes all dates in it considering the filter context within a row within the table is to a specific date. That seems weird to me although I imagine it is because using SUMMARIZE against ALL of the fact table and then by Date brings all of those dates back into context somehow versus the individual date at the visual row.
The SUMMARIZE works on the expanded fact table, which leverages the star schema. I find using SUMMARIZE this way useful in many situations.
This situation where you have a visual with thousands of rows does indeed seem to be a pathological case for basic CALCULATE patterns. There are other situations where those same patterns will outperform the No CALCULATE approach in the post (see the Contoso example I uploaded, for example). I'd expect Tamer's WINDOW solution to work well broadly.
Super User
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?
- tamerj1
I agree. All window functions ignore the data model. But I didn't get the point of SUMMARIZECOLUMNS. Would you please elaborate on that?
- lbendlin
tamerj1 Here's a very nice write-up All the secrets of SUMMARIZE - SQLBI
- tamerj1
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.
