problem with summarizing a table after date filter is applied through a slicer

[mehsani]

I have a date slicer visual limited to a date range on a calendar table defined below:

Calendar = CALENDAR(TODAY() - 1000, TODAY())

I have another table that has a date column. I want to create a summary of this table in another table using DAX measure after the date filter is applied on the slicer, so I create another table with the DAX measure below:

ModelTotal =

VAR MinDate =

CALCULATE(

    MIN('Calendar'[Date]),

    ALLSELECTED('Calendar')

    )

VAR MaxDate =

MaxDate = CALCULATE(

    MAX('Calendar'[Date]),

    ALLSELECTED('Calendar')

    )

RETURN

    SUMMARIZE(

        FILTER('table',

            'table'[table_date] >= MinDate &&

            'table'[table_date] <= MaxDate),

        'table'[Model],

        "model total", COUNTROWS('table')

    )

the problem I have is the summary table is not reacting to the date slicer, i.e., when I change the slicer the filter doesn't apply to my "table". The data in the table is static and only includes the data from 1000 days ago until today, whereas I need it to show only the summary for the selected date range.

Can anyone help please? What seems to be the problem? Note that:

1. I have defined a 1-* relationship between columns 'Calendar' [Date ] and 'table' [date].

2. When I test the expressions: MinDate = CALCULATE(MIN('Calendar'[Date]), ALLSELECTED('Calendar')) and MaxDate = CALCULATE(MIN('Calendar'[Date]), ALLSELECTED('Calendar')) on a card visual they show the correct values, i.e., they would react to the date slicer changing. But for some reason this doesn't work in my other DAX where I create the summary table.

Thanks

[mehsani]

Thanks @v-zhangti for the reply! I have a hard time understanding what you mean. Isn't my measure below a "table variable in a measure"? 

ModelTotal =

VAR MinDate =

CALCULATE(

    MIN('Calendar'[Date]),

    ALLSELECTED('Calendar')

    )

VAR MaxDate =

MaxDate = CALCULATE(

    MAX('Calendar'[Date]),

    ALLSELECTED('Calendar')

    )

RETURN

    SUMMARIZE(

        FILTER('table',

            'table'[table_date] >= MinDate &&

            'table'[table_date] <= MaxDate),

        'table'[Model],

        "model total", COUNTROWS('table')

    )

if it isn't, can you please elaborate? How else should I define my variable table?

[v-zhangti]

Hi, @mehsani 

DAX calculated tables and columns do not update with slicer changes (only at data refresh). You need to build your table expression into a table variable in a measure.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.