Is it possible to have a variable that's only calculated whenever a filter is applied?

Question

I'm trying to optimize a measure, and after analyzing it I found that the problem comes from the fact that part of it is calculated on every row when it only needs to be calculated once when a certain filter is applied.

Here's the measure :

Effectif :=
VAR LastPeriod =
    MAX ( 'Time'[Period] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[ClientID] ),
        FILTER (
            Sales,
            OR (
                LastPeriod - Sales[ClientLastOrder] < 4,
                LastPeriod - Sales[ClientEntry] < 4
            )
        )
    )

In this case, LastPeriod will be calculated over and over, whereas we only need it to be calculated once when a filter is applied on Time.

Is there any way to store this information somewhere so that it doesn't have to make superfluous calculations ?

Answer 1

Try this 2 Codes, and let me know If one or both of them are any faster. Instead of iterating (or scanning ) a full table, you only iterate the columns you need in a filter argument. Like this:

Version-1

Effectif :=
VAR LastPeriod =
    MAX ( 'Time'[Period] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[ClientID] ),
        FILTER (
            ALL ( Sales[ClientLastOrder], Sales[ClientEntry] ),
            OR (
                LastPeriod - Sales[ClientLastOrder] < 4,
                LastPeriod - Sales[ClientEntry] < 4
            )
        )
    )

Version-2

Effectif :=
VAR LastPeriod =
    MAX ( 'Time'[Period] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales'[ClientID] ),
        CALCULATETABLE (
            SUMMARIZE ( Sales, Sales[ClientLastOrder], Sales[ClientEntry] ),
            OR (
                LastPeriod - Sales[ClientLastOrder] < 4,
                LastPeriod - Sales[ClientEntry] < 4
            )
        )
    )