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] ) )
There seems to be something pathological going on that's making the query planner do weird things with simpler measures. I'd really like to figure out what as I think that knowledge may be broadly applicable in measure optimization. It should not be doing this (some sort of cross-join?).
I got the above when using this code:
VAR _CurrYear = SELECTEDVALUE ( Dates[Year] )
VAR _CurrDate = SELECTEDVALUE ( Dates[Date] )
VAR _Result =
CALCULATE (
[Internet Sales],
Dates[Year] = _CurrYear,
Dates[Date] <= _CurrDate
)
RETURN
_Result
Using WINDOW is better but it still has steps with 1.4 million records.
CALCULATE (
[Internet Sales],
WINDOW (
1, ABS,
0, REL,
ORDERBY ( Dates[Year], ASC, Dates[Date], ASC ),
PARTITIONBY ( Dates[Year] )
)
)
Can any DAX masters like marcorusso or jeffrey_wang shed some light?
- Greg_Deckler2 years ago
Community Champion
- AlexisOlson2 years ago
Super User
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- tamerj12 years ago
Community Champion
What the formula engine is trying to do is to create the following table:
Date Date YTD Sum of SalesAmount Then it does the aggregation over Date.
With help of some mathmatics, given that the Date table is 7,670 rows the formula engine would produce a crossjoin table of around (1/2 )* (7,670) * (7,670) = 29,414,450 rows. This is simply the area of a triangle.
In opttimized versions, the corossjoin is happening to only the dates that do exist in the fact tabe (1,124) rows. That would result in a table of around (1/2 )* (1,124 * (1,124) = 6,316,680 rows.
This is why the cardinality of the Dates[Date] and FactInternetSales[Order Date] do matter alot.
Usually I use the Window function following a NoCALCULATE approach by pre-calculating values then pushing all other calculation to the formula engine. For example the following produces the simplist and fastest query plan along with the minimum number of storage engine queries among all other solutions.Internet Sales (YTD) Window = VAR AllDateSales = SUMMARIZE( ALL ( FactInternetSales ), Dates[Year], Dates[Date], "@Amount", SUM ( FactInternetSales[SalesAmount] ) ) VAR Result = SUMX ( WINDOW ( 0, ABS, 0, REL, AllDateSales, ORDERBY ( Dates[Date] ), PARTITIONBY ( Dates[Year] ) ), [@Amount] ) RETURN ResultI would however say that your WINDOW-CALCULATE solution matches the performance of other NoCALCULATE solutions so I vote for it as an acceptable solution.
- tamerj12 years ago
Community Champion
AlexisOlson
Exactly.
What the formula engine is trying to do is to create the following table:Date Date YTD Sum of SalesAmount Then it does the aggregation over Date.
With help of some mathmatics, given that the Date table is 7,670 rows the formula engine would produce a crossjoin table of around (1/2 )* (7,670) * (7,670) = 29,414,450 rows. This is simply the area of a triangle.
In opttimized versions, the corossjoin is happening to only the dates that do exist in the fact tabe (1,124) rows. That would result in a table of around (1/2 )* (1,124 * (1,124) = 6,316,680 rows.
This is why the cardinality of the Dates[Date] and FactInternetSales[Order Date] do matter alot.