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...
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
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 
    Result

 

Solution 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 
    Result

 

Cumulative 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 
    Result

 

tamerj1 I tried and your code and it returns incorrect subtotals.

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

AntrikshSharma Really impressive! If there is a restriction that you can't change the data model, then this slight tweak works just as well:

Cumulative Sales Amount (Delivered) AS rev = 
VAR SalesByDelivery = 
    SUMMARIZE ( 
        ALLSELECTED ( Sales ),
        [DeliveredDateKey],
        "@TotalSales", SUM ( [SalesAmount] ),
        "@YearMonth", YEAR ( [DeliveredDateKey] ) * 100 + MONTH ( [DeliveredDateKey] ) 
    )
VAR Result = 
    SUMX ( 
        FILTER ( 
            SalesByDelivery, 
            [@YearMonth] <= MAX ( 'Calendar'[Year] ) * 100 + MAX( 'Calendar'[MonthOfYear])
        ),
        [@TotalSales]
    )
RETURN
    Result