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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Laokoon
Frequent Visitor

CALCULATETABLE vs CALCULATETABLE(FILTER

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
)

Laokoon_1-1627480870904.png

 

4 REPLIES 4
Anonymous
Not applicable

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."

Greg_Deckler
Super User
Super User

@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.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@Laokoon That was the purpose of the ALL


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors