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.
Sales Amount (Delivered) NC =
VAR __Dates = SELECTCOLUMNS('Calendar',"DateKey",[DateKey])
VAR __Table = FILTER(ALLSELECTED('Sales'), [DeliveredDateKey] IN __Dates)
VAR __Result = SUMX(__Table, [SalesAmount])
RETURN
__Result
In my testing the measures return within 50 milliseconds of one another.
- AlexisOlson3 years ago
Super User
Nice!
Now try it without the slicer filtering and see how the performance compares. 🙂
- Greg_Deckler3 years ago
Community Champion
AlexisOlson This one doesn't use CALCULATE either (LOL!) and is actually 3 times faster than the original measure from a DAX Query perspective.
NC3 = VAR __Table = CALCULATETABLE('Sales', USERELATIONSHIP ( Sales[DeliveredDateKey], 'Calendar'[DateKey] )) VAR __Result = SUMX(__Table, [SalesAmount]) RETURN __Result- tamerj13 years ago
Community Champion
🤣🤣🤣