Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexisOlson
Super User
Super User

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.

 

AlexisOlson_0-1679503045350.png

 

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

 

 

Please use the attached file.

Report preview:

AlexisOlson_1-1679503139856.png

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

2 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png

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

 

View solution in original post

AntrikshSharma
Community Champion
Community Champion

@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

 

AntrikshSharma_0-1679580762224.png

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

AntrikshSharma_1-1679581031494.png

View solution in original post

26 REPLIES 26

@Greg_Deckler 

Yes I actually placed the solution of the first problem in a separate reply right before the the solution of the 2nd problem. 

@tamerj1 Somehow I missed that, the way these forums do threading is mildly infuriating at times.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@AlexisOlson It's a good more or less single use case where CALCULATE is beneficial but really just because USERELATIONSHIP was coded to work with CALCULATE. USERELATIONSHIP could have been coded to support FILTER as well. It's not really CALCULATE, it's the additional functions that were coded to work with CALCULATE. But, there are plenty of examples of the reverse. Try writing a version of these with only using CALCULATE and no X aggregator that A. Actually works and B. Performs significantly faster.

  1. Write a running total measure that uses a single table data model only using only CALCULATE and no X aggregator
  2. Write a working measure total for a semi-additive measure using only CALCULATE and no X aggregator. Measure Totals, The Final Word - Microsoft Power BI Community
  3. Write a version of Open Tickets measure using only CALCULATE and no X aggregator
  4. Write a Deseasonalized Correlation Coefficient measure using only CALCULATE and no X aggregator
  5. Write a Net Work Days measure using only CALCULATE and no X aggregator
  6. Write a Simple Linear Regression measure formula using only CALULATE and n X aggregator
  7. Write Cthulhu using only CALCULATE and no X aggregator
  8. Write an MTBF measure using CALCULATE and no X aggregator
  9. Write a While loop measure using CALCULATE
  10. Write a For loop measure using CALCULATE
  11. Write a Days of Supply measure using only CALCULATE and no X aggregator
  12. Write Hour Breakdown measure using only CALCULATE and no X aggregator
  13. Write Lookup Value measure using only CALCULATE and no X aggregator
  14. Write Overworked measure using only CALCULATE and no X aggregator

I could go on, I only got through 3 pages of 11 of the Quick Measure Gallery.

 

The point here is the the No CALCULATE approach is a far better and more flexible approach to writing DAX that allows you to solve real-world problems, problems that CALCULATE could never hope to touch. Yes, there are times when CALCULATE is a good idea for one reason or another. But, this fixation the DAX community has on CALCULATE is unhealthy. It makes DAX harder to learn and breeds this crazy culture where people want to use it everywhere when it is simply making their lives harder and is absolutely unnecessary the vast majority of the time when the simple approach of:

  1. Create some VAR's
  2. Create a Table VAR
  3. X Aggregator

Will solve the VAST majority of problems in DAX without ever needing CALCULATE. Simple. No reason to worry about the internal workings of CALCULATE or context transition or pretty much any of the stuff that people find "hard" about DAX.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler X aggregators are absolutely essential. I have no interest in comparing No CALCULATE vs No X aggregator since I'd rather not handicap myself at all.

@Greg_Deckler 
Totally agree 

@Greg_Deckler 

🤣🤣🤣

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.