Why ALL is not working ?

[jajaAtPowerbi]

would like to calculate the member count for the same period of last month, expect ALL will remove the filter context at Calendar, but seem no.

Could you teach how to fix the problem?

[tamerj1 · Accepted solution]

Hi @jajaAtPowerbi 
Please try

redeemed member count (same period LM) =
VAR StartDateLM = [start date LM]
VAR EndDateLM = [end date LM]
RETURN
    CALCULATE (
        [redeemed member count],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= StartDateLM
                && 'Calendar'[Date] <= EndDateLM
        )
    )

[jajaAtPowerbi · Accepted solution]

@tamerj1 it works by creating a local variable inside measure redeemed member count (same period LM) but i do not understand the difference.

Would you mind explain why?

 Thanks you so much.

[tamerj1]

@jajaAtPowerbi 
Measures in a DAX formula impose context transition therefore, in the original formula there are evaluated in a filter context that is created by transforming the row context created by FILTER into a filter context by the measure reference. So each measure will be repeatedly re-evaluated for every iteration of the FILTER function.

However, variables are evaluated only once. If a measure is evaluated within the outer filter context and stored in a variable, the value of the variable will not be ultered (meaning the measure will not be re-evaluated) even if referenced inside an iterator, rather it will always keep the value of the very first evaluation.

[tamerj1 · Accepted solution]

Hi @jajaAtPowerbi 
Please try

redeemed member count (same period LM) =
VAR StartDateLM = [start date LM]
VAR EndDateLM = [end date LM]
RETURN
    CALCULATE (
        [redeemed member count],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= StartDateLM
                && 'Calendar'[Date] <= EndDateLM
        )
    )