Forum Discussion

Super 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 inactive relationship. It must also not break if a simple filter from any of the dimension tables is applied.

Sales Amount (Delivered) = 
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    USERELATIONSHIP ( Sales[DeliveredDateKey], 'Calendar'[DateKey] )
)

Please use the attached file.

Report preview:

As expected, we can see that sales during periods without a discount promotion have products delivered in promotion months.

Contoso Sales Sample.pbix25 MB

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

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.

26 Replies

AntrikshSharma

Community 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

Community 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
Community Champion
3 years ago
tamerj1 Slick, way better than my revision in my video I made last night.

Greg_Deckler

Community 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

tamerj1
Community Champion
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] ) )
- User5231
  Helper II
  2 years ago
  Greg_Deckler
  What's an easy no DAX to replace A Amount here. Basically, return the Amount of A in context to the visual regardless of category. So you can easily then calculate percent of A on any category, etc. I can't find a good way that isn't convoluted...
  - User5231
    Helper II
    2 years ago
    Greg_Deckler I found Round 2 of these challenges and realized that addresses what is essentially what I am looking at here.
- LuisMacedo
  Regular Visitor
  3 years ago
  Sir, would you mind telling me how you can get sales for sameperiodlastyear without using calculate, knowing that the date table doesn't have date column, only year and quarters?I tried using filter() and max(), but it does not bring the correct values .
  - Greg_Deckler
    Community Champion
    3 years ago
    LuisMacedo Get your current year, maybe VAR __Year = MAX('Date'[Year]) then VAR __PY = __Year - 1, then filter ALL of your fact table where Year column = __PY? Hard to say exactly without knowing your data.
Greg_Deckler
Community Champion
3 years ago
AlexisOlson

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.
- AlexisOlson
  Super User
  3 years ago
  Nice!
  
  Now try it without the slicer filtering and see how the performance compares. 🙂
  - Greg_Deckler
    Community Champion
    3 years ago
    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
AlexisOlson
Super User
3 years ago
I'm awarding the win to tamerj1 for the original problem. It's about 20ms in testing on my machine instead of 10ms for mine but that's still super fast.

I'm awarding the win to AntrikshSharma for the cumulative challenge since that solution works for subtotals as well.

Forum Discussion

No CACULATE challenge

26 Replies

Related content

Caculate Semester

getting challenges with data science

Matrix Challenges

Filtering Data challenge

Facing challenge to calculate previous year QTD