Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
4 years ago
Solved

how does filter context work

I'm studying The Definitive Guide to DAX and get confused on how filter context works.   In the measure below (p193), which filter context is applied to values('Date'[Date])? The external filter co...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Jeanxyz 

    The same logic is difficult to reproduce in a general dax formula( without considering the number of days), which has been mentioned in 'The Definitive Guide to DAX'.

    The following formula is similar, but not completely equivalent.

    TREATAS(
    SELECTCOLUMNS(
        CALCULATETABLE(DISTINCT('Date'[Date])),
        "Date",DATE(
            YEAR('Date'[Date]),
            MONTH('Date'[Date])-1,
            DAY('Date'[Date])
        )
    ),
    'Date'[Date]))

    This formula will work normally only when the target month and the current month have the same number of days, otherwise several days(1day=31-30, 2days=29-31,3days=28-31 )  will be lost or included.

    Best Regards,
    Community Support Team _ Eason

     

lbendlin's avatar
lbendlin
Super User
4 years ago

Your first measure is missing the transition from January to December of the prior year. The book has that shown correctly.

 

The applied filter context is ALL(Date[Date]).

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    lbendlin is totally correct that the expression doesn't handle dates in January going back to December.

     

    However I think the answer to your other question about context needs expanding.


    ALL(Date[Date]) is only providing the base table for the iterator. FILTER doesn't modify the filter context (only calculate expressions can do that). Therefore VALUES(Date[Date]) comes from the external context.  YEAR(Date[Date]) comes from the row context of the iterator.