Forum Discussion

mehsani's avatar
mehsani
New Member
2 years ago

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

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

Need Help

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    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.

  • mehsani's avatar
    mehsani
    New Member
    2 years ago

    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?