Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago
Solved

CALCULATE Challenge - Round 1

For those haters of No CALCULATE like AlexisOlson, ðŸ˜…, I challenge you to create measures with an explicit CALCULATE that for the prescribed scenarios perform anywhere close to the same speed as the ...
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    I think this should qualify, though it isn't super clean.

     

    VAR _AllDates_ =
    SUMMARIZE (
    	ALL ( FactInternetSales ),
    	Dates[Year],
    	Dates[Date]
    )
VAR _DateRange_ =
    WINDOW (
        1, ABS,
        0, REL,
        _AllDates_,
        ORDERBY ( Dates[Date] ),
        PARTITIONBY ( Dates[Year] )
    )
VAR _Result =  CALCULATE ( [Internet Sales], _DateRange_ )
RETURN
    _Result
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago
    thank God it's such an obvious and easy solution that even a DAX novice could understand and doesn't rely on a function introduced within that last year which pretty much means the solution was unsolvable for 7 years or so...

    WINDOW isn't required. A plain filter works fine too and has been around since the beginning.

     

    VAR _AllDates_ = SUMMARIZE ( ALL ( FactInternetSales ), Dates[Date]	)
	
VAR _CurrDate = MAX ( Dates[Date] )
VAR _CurrYear = YEAR ( _CurrDate )

VAR _DateRange_ =
    FILTER (
    	_ALLDates_,
    	YEAR ( Dates[Date] )  = _CurrYear &&
    	Dates[Date] <= _CurrDate
    )
VAR _Result =  CALCULATE ( [Internet Sales], _DateRange_ )
RETURN
    _Result

     

     

    However, there are still 15 other date intelligence measures in the file that are part of the challenge.

    I'm not really interested in doing all 15 other ones as I don't think they'll reveal much that this YTD example doesn't already. If there is one that you think is meaningfully different, let me know.

  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    Greg_Deckler, PW is an easy case where the TI solution or something like the following is just as fast.

    CALCULATE (
    [Internet Sales],
    TREATAS ( VALUES ( Dates[Prior Week Date] ), Dates[Date] )
)
dotykier's avatar
dotykier
Icon for Advocate I rankAdvocate I
2 years ago

Late to the party, and sorry if this has been posted before (maybe I'm just stupid, but for the life of me, I can't figure out how to browse through forum thread comments in the order they were posted...)

 

It seems to me that the NO CALCULATE performs better than TOTALYTD, because Greg_Deckler's dataset was carefully crafted for this particular scenario. Specifically, the 'Dates' table is much, much larger than it needs to be (containing dates from 2010 to 2030, even though the fact table only contains data for 2010-2014), while the fact table is a meager 60.000 rows.

 

If we reduce the 'Dates' table to only contain dates from 2010-2014, TOTALYTD performs much better than in the original model, but NO CALCULATE is still faster by about 50%:

 

 

However, TOTALYTD scales better with the size of the fact table. For example, on a model with ~17 mio. rows in the fact table (called 'Orders' in this example), the two approaches have identical performance:

 

I haven't tested it, but I would wager that TOTALYTD outperforms NO CALCULATE if you increase the size of the fact table further. This is because TOTALYTD doesn't need to consider the fact table when creating the date filter.

 

But more importantly, using TOTALYTD (or an equivalent CALCULATE) addresses a serious issue in the NO CALCULATE approach, namely that all filters are removed from FactInternetSales. This means that you can't slice/filter the fact table by anything other than dates. For example, try to add a slicer on the FactInternetSales[ProductKey] column, and see what happens as you filter different product keys... I would like to see a NO CALCULATE solution that retains any other filter that could potentially affect the fact table, while still performing as good as TOTALYTD. That is the real challenge imho.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    dotykier I can assure you that the semantic model wasn't specifically engineered for any particular purpose. The dates table is reasonable in that many corporate date tables would want a decade of history and also be future proof by about a decade. That's pretty standard. I mean, it's only ~7,300 rows. 

     

    The fact table is simply AdventureWorks which is also pretty standard for demonstration purposes. The fact that the table is a mere 60,000 rows means that TI and CALCULATE should have a super easy time with this semantic model. If they can't perform on a semantic model that includes 70,000 rows total between the fact and dimension tables how can we expect them to perform on a data model with 10x or 100x that number of rows? That doesn't make a lot of sense.

     

    All filters do not have to be removed, there are trivial ALLEXCEPT methods for No CALCULATE: https://youtu.be/fH0VcV9Smow

    Also, the scenario you are describing was kind of the focus for AlexisOlson's No CALCULATE Challenge #2 and it was solved by tamerj1 

     

    With the No CALCULATE Challenges, we didn't get to whine about the scenario and count that as a solution so the same applies in this case. You can whine and complain however you want, but that isn't a solution.