Using ALL()

[raj-ascendum]

Hi,
I am trying to get a table variable with all filters removed from 'Table 1' and data within a user selected 'Start' & 'End' date from a 'Date' table 

 (a)-      Using All( ) returns the whole 'Table 1', ignoring even the filter applied by the 'DATESBETWEEN' 

                    CALCULATETABLE ( All( 'Table 1')
                                                   ,DATESBETWEEN('Table 1'[Reporting Date], start_date, end_date)
                                                    )

 (b)-     Using : without All( ) works as desired by the 'DATESBETWEEN'

                            CALCULATETABLE ( 'Table 1'
                                                            ,DATESBETWEEN('Table 1'[Reporting Date], start_date, end_date)
                                                          )

Not using ALL() applies the filter applied by the 'DATESBETWEEN', whereas ALL() removes the filter of 'DATESBETWEEN'.

Please help.

[raj-ascendum]

Thanks for guiding.
I will dig deeper in the book.

[raj-ascendum]

Thanks a lot Edhans,
I modified the DATESBETWEEN  to  <  & > and used FILTER. It works as desired.
I am trying to get a deeper understanding of Why Calculatetable with ALL() returns all the records ignoring the Dates ( <= & >= ) filter, whereas Filter() with ALL() returns records after applying the dates filter.

I am asking this because I have a number of measures desinged that way with 'CALCULATETABLE', I will have to update all of them.

[edhans · Accepted solution]

CALCULATETABLE() invokes context transition, and as I said, DATESBETWEEN() is meant to work only on date tables which have a hidden ALL() filter on them. Your data table is not a date table, so using DATESBETWEEN() on it will cause problems. DATESBETWEEN() returns a table, whereas the filter critera I set up will filter the records that the FILTER() function iterates on. In other words, it looks at every record and evaluates what does and doesn't fall in between the dates. It keeps those that do. DATESBETWEEN() returns a table with all dates from a valid date table between the start/end date, it does not filter data in normal date columns.

It goes beyond the scope of this forum to really dig into context transition and Time Intelligence functions. THere are some really good chapters on this in the Definitive Guide to DAX Second Edition book. But the short answer is, unless you are using a time intelligence function, do not use any Time Intellgence functions like DATESBETWEEN or LASTDATE. They will not work properly or efficiently if used as normal functions.

If my answer helped, please mark it as a solution so this thread can be known to be solved. Thanks!

[edhans]

DATESBETWEEN() is a time intelligence function and should only be used on a date table. Try this:

Test Measure =
    FILTER(
        ALL('Table 1'),
        'Table 1'[Reporting Date] >= start_date
            && 'Table 1'[Reporting Date] <= end_date
    )

I'm not sure why you need CALCULATETABLE here, but you can wrap the above in that if you are needing some particular context transition. FILTER() returns a table.