Forum Discussion

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago
Solved

No CACULATE challenge

For those haters of CALCULATE like Greg_Deckler, I challenge you to create a measure without CALCULATE that performs anywhere close to the same speed as this very simple measure that switches to an i...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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 ðŸ˜‰

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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.