Forum Discussion
No CACULATE challenge
- 3 years ago
Hi AlexisOlson & Greg_Deckler
This one uses neither CALCULATE nor CALCULATETABLE and performs faster than the original oneSales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) RETURN SUMX ( VALUES ( 'Calendar'[DateKey] ), VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] = 'Calendar'[DateKey] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) ) - 3 years ago
AlexisOlson Good challenge! I am a Pro CALCULATE but recommend users not to stick with a single side.
Here are my solutions:Solution 1 using INNERJOIN, performs in 40ms without any filter
Delivered Amount AS = VAR Dates = SELECTCOLUMNS ( VALUES ( 'Calendar'[DateKey] ), "DeliveredDateKey", 'Calendar'[DateKey] & "" ) VAR SalesByDelivery = SELECTCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Sales ), Sales[DeliveredDateKey], "Sum", SUM ( Sales[SalesAmount] ) ), "DeliveredDateKey", [DeliveredDateKey] & "", "Sales", [Sum] ) VAR Result = SUMX ( NATURALINNERJOIN ( Dates, SalesByDelivery ), [Sales] ) RETURN ResultSolution 2 using CONTAINSROW or the IN performs in 23 ms
Delivered Amount AS 2 = VAR SalesByDeliveryDate = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "TotalSales", SUM ( Sales[SalesAmount] ) ) VAR CurrentYearRows = FILTER ( SalesByDeliveryDate, CONTAINSROW ( VALUES ( 'Calendar'[DateKey] ), Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( CurrentYearRows, [TotalSales] ) RETURN ResultCumulative Total - I added a YearMonth column in the Dates table, since we are not showing date level I decided to reduce granularity, takes about 50ms.
CT DeliveredAmount AS = VAR SalesByDelivery = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "@TotalSales", SUM ( Sales[SalesAmount] ), "@YearMonth", YEAR ( Sales[DeliveredDateKey] ) * 100 + MONTH ( Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( FILTER ( SalesByDelivery, [@YearMonth] <= MAX ( 'Calendar'[YearMonth] ) ), [@TotalSales] ) RETURN Resulttamerj1 I tried and your code and it returns incorrect subtotals.
AlexisOlson
Here you go
Cumulative Sales Amount (Delivered) 2 =
VAR SelectedSales = ALLSELECTED ( Sales )
VAR SummarySales =
SUMMARIZE (
SelectedSales,
Sales[DeliveredDateKey],
"@SalesAnount", SUM ( Sales[SalesAmount] )
)
VAR SummaryDates =
SUMMARIZE (
'Calendar',
'Calendar'[Year],
'Calendar'[MonthName],
"@MaxDate", MAX ( 'Calendar'[DateKey] )
)
RETURN
SUMX (
SummaryDates,
VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] <= [@MaxDate] )
RETURN
SUMX ( FilteredSales, [@SalesAnount] )
)
This is faster than yours. I guess Greg_Deckler has a very good point 😉
tamerj1 Wow! Super impressive! That is lightning fast!! I ran 10-15 tests and your version has DAX query timing that is on average 2-3 times faster than CALCULATE ( ~75 milliseconds versus ~200 milliseconds. Overall time was consistently 100 milliseconds faster. Anonymous
The technique also works for the original problem and performs almost identically to the CALCULATE version.
NC4 =
VAR __SelectedSales = ALLSELECTED( 'Sales' )
VAR __SummarySales =
SUMMARIZE(
__SelectedSales,
[DeliveredDateKey],
"__SalesAmount", SUM( [SalesAmount] )
)
VAR __Dates = DISTINCT('Calendar'[DateKey])
VAR __Result =
SUMX( FILTER( __SummarySales, [DeliveredDateKey] IN __Dates), [__SalesAmount])
RETURN
Once again, CALCULATE goes down in defeat.
- tamerj13 years ago
Community Champion
Yes I actually placed the solution of the first problem in a separate reply right before the the solution of the 2nd problem.
- Greg_Deckler3 years ago
Community Champion
tamerj1 Somehow I missed that, the way these forums do threading is mildly infuriating at times.