Forum Discussion

jajaAtPowerbi's avatar
jajaAtPowerbi
Frequent Visitor
3 years ago
Solved

Why ALL is not working ?

 

 

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's avatar
    tamerj1
    3 years ago

    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's avatar
    jajaAtPowerbi
    3 years ago

    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.

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
    jajaAtPowerbi
    Frequent Visitor
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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.