Dax: calculate with filter and all, result not expected

Question

I have a filter date table start from 2000/01/01 to 2020/1028 and a fact table of SP index. The model set as below:

I use the this expression:

firstDateYear2020 =
CALCULATE (
    MIN ( 'SP Index 20201028'[Date] ),
    FILTER (
        ALL ( 'SP Index 20201028'[Date] ),
        YEAR ( 'SP Index 20201028'[Date] ) = 2020
    )
)

to get the first date of SP index in 2020.

In the report view, add the date slice and to see the first date of 2020, when the state date of slicer less than 2020/01/01, the DAX expression returns the date as expected (2020/01/01 or 2020/01/02)

However, when I select a start time greater than 2020/01/01, the DAX expression returns the selected start date rather than the first date 2020, I expected it would behave the same result as above.

The DAX expression I use ALL in the filter, I think it would ignore slicer effect, however, when start time greater than 2020/01/01, slicer looks like it still has an effect? Can you please help explain to me why this DAX expression has a different result?

Answer 1

You should try to put in your Filter DateTable[Year] instead of 'SP Index 20201028'[Date] because you have a relationship and filtering on dimension is always a better choice; Probably in your slicer, you have column from DateTable and you lose context.

firstDateYear2020 = CALCULATE(MIN('SP Index 20201028'[Date]),
FILTER(ALL(DateTable[Year]),DateTable[Year]=2020))

Answer 2

I agree with @msta42a. If your slicer is using DateTable, then this is what I'd expect. You can write this instead:

firstDateYear2020 =
CALCULATE (
    MIN ( 'SP Index 20201028'[Date] ),
    ALL ( 'DateTable' ),
    YEAR ( 'DateTable'[Date] ) = 2020
)

The reason for this is that ALL removes filters only on the table it refers to but does not remove filters that come from other tables. So you've successfully removed any filters specifically on the column 'SP Index 20201028'[Date] but if that's not the column used for your slicer, then the slicer still applies a filter since it comes from another table that is upstream. Filtering on tables only flows downstream in the relationships.