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

     

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