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 context or the new context created by filter(all('Date'[Date])? Why? 

 

Similarly, which filter context is applied to YEAR ( 'Date'[Date] ? The external filter context or the new context created by filter(ll('Date'[Date])?

 

sales amount_1M test =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date'[Date] ),
        CONTAINS (
            VALUES ( 'Date'[Date] ),
            'Date'[Date], DATE ( YEAR ( 'Date'[Date] ), MONTH ( 'Date'[Date] ) - 1, DAY ( 'Date'[Date] ) )
        )
    )
)

 

According to the textbook, the measure should work similarly as Dateadd(). So I have written the measure below. 

sales amount_1M = calculate([Sales Amount],dateadd('Date'[Date],-1,month))
 
However, when I compared the two measures, the results are quite different in some product categories.  Why the 1st measure didn't work as exprected?

 

 

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

     

4 Replies

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    I am not sure of what you want but i think you want to calculate the sales of the previous month.  Try this measure

    =calculate([Sales Amount],previousmonth('Date'[Date]))

    Please ensure that you drag Year and Month from the Date table to your visual and there is a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Date table.

    Hope this helps.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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