topic Re: CALCULATETABLE vs CALCULATETABLE(FILTER in DAX Commands and Tips

CALCULATETABLE vs CALCULATETABLE(FILTER

Laokoon — Wed, 28 Jul 2021 14:03:45 GMT

Hello,

Could anybody explain or reccomend any topic with answer, why the second measure is cca 1000x faster then the first one?
(I've already heard about the context of the transition, but I can't imagine what's going on here 🙂 )
Thanks a lot in advance

Time 1st year_first =
AVERAGEX (
    Installation,
    VAR ActivationDate = 'Installation'[First Activation Date]
    VAR tab =
        CALCULATETABLE (
            'GDO SD Ticket Evidence',
            'GDO SD Ticket Evidence'[Datum] <= ActivationDate + 365,
            ALL ( 'DIM Date' )
        )
    VAR Result =
        SUMX ( tab, 'GDO SD Ticket Evidence'[Time Minutes] )
    RETURN
Result
)

Time 1st year_second =
AVERAGEX (
    Installation,
    VAR ActivationDate = 'Installation'[First Activation Date]
    VAR tab =
        CALCULATETABLE (
            FILTER (
                'GDO SD Ticket Evidence',
                'GDO SD Ticket Evidence'[Datum] <= ActivationDate + 365
            ),
            ALL ( 'DIM Date' )
        )
    VAR Result =
        SUMX ( tab, 'GDO SD Ticket Evidence'[Time Minutes] )
    RETURN
Result
)

Re: CALCULATETABLE vs CALCULATETABLE(FILTER

Greg_Deckler — Wed, 28 Jul 2021 14:31:14 GMT

Laokoon My question would be if you even need the CALCULATETABLE versus something like:

    VAR tab =
      FILTER (
          ALL('GDO SD Ticket Evidence'),
                'GDO SD Ticket Evidence'[Datum] <= ActivationDate + 365
            )

Also, that strikes me as a weird way overall to construct that calculation.

Re: CALCULATETABLE vs CALCULATETABLE(FILTER

Anonymous — Thu, 29 Jul 2021 12:41:49 GMT

To see why this is so you'll have to gather data via DAX Studio using the Traces tab. Right now it's almost impossible to answer this question. By the way, there is no such thing as "the context of the transition," but only "context transition."

Re: CALCULATETABLE vs CALCULATETABLE(FILTER

Laokoon — Wed, 04 Aug 2021 07:54:13 GMT

Thank you Greg_Deckler your code looks more elegant, but I am not sure how to use it...

Without CALCULATETABLE I have to use RELATEDTABLE (which should be better I guess)

VAR tab =

FILTER(

RELATEDTABLE('GDO SD Ticket Evidence'),

'GDO SD Ticket Evidence'[Datum]<=ActivationDate+365
)

But how to ignore DATE filters ('DIM Date') comming from UI?
Thank you

Re: CALCULATETABLE vs CALCULATETABLE(FILTER

Greg_Deckler — Wed, 04 Aug 2021 12:51:39 GMT

Laokoon That was the purpose of the ALL