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
    3 years ago

    Hi AlexisOlson  & Greg_Deckler 
    This one uses neither CALCULATE nor CALCULATETABLE and performs faster than the original one

    Sales 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] )
    )

     

  • AntrikshSharma's avatar
    AntrikshSharma
    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.

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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    AntrikshSharma 
    Yes you are right I've over looked that but this is an easy fix. The outer SUMX should have been a MAXX

    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
    MAXX ( 
        SummaryDates,
        VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] <= [@MaxDate] ) 
        RETURN
            SUMX ( FilteredSales, [@SalesAnount] )
    )
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      tamerj1 Slick, way better than my revision in my video I made last night.

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Greg_Deckler 

        Wow!

        Thank you for mentioning me in your video ☺️

        My name is Tamer Juma by the way and we are connected in LinkedIn actually you replied to my comments there couple of times. 

        Note: I fixed the wrong total by simply replacing the outer SUMX with MAXX

  • 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