topic Re: CALCULATETABLE doesn't filter rows with current filter context in DAX Commands and Tips

CALCULATETABLE doesn't filter rows with current filter context

Handlordz — Mon, 16 Jan 2023 17:50:29 GMT

Hi everyone,

I have an issue with CALCULATETABLE function:
My fact table 'f_MNT_visits' is connected to a dates table with a range of date from year 2021 to year 2023.
I need to filter my fact table from year selected by user from filter pages.
I expect by using MAX function that fact table will be filtered from filter context (range of dates selected by the user).
I get the max year of date table instead: If user select year 2022, the table is filtered on 2023.
I wrote my dax code like this:

CALCULATETABLE(

f_MNT_visits,

YEAR ( f_MNT_visits[DatePlan.]) = YEAR ( MAX ( d_dates[Date] ) )

)

How can I filter my fact table from year select by the user and not from max value of the dates table.

Thank you in advance for your support.

Re: CALCULATETABLE doesn't filter rows with current filter context

tamerj1 — Mon, 16 Jan 2023 18:05:52 GMT

Handlordz

I assume that you have disabled the interaction between the the visual and the slicer?

Re: CALCULATETABLE doesn't filter rows with current filter context

Handlordz — Mon, 16 Jan 2023 18:27:39 GMT

tamerj1 ,

I am not using a visual here, I check the number of rows returned by CALCULATETABLE from data tab.
Year is fixed with value 2022 by user from filter section but number of rows returned correspond to year 2023

When I use this expression in a measure "YEAR ( f_MNT_visits[DatePlan.]) = YEAR ( MAX ( d_dates[Date] ) )"
the filter is applied from filter context but for a table generated by CALCULATETABLE the filter is applied from the max value of my dates table.

Re: CALCULATETABLE doesn't filter rows with current filter context

tamerj1 — Mon, 16 Jan 2023 18:52:48 GMT

Handlordz
Calculated tables do no interact with slicers. They simply do not read the filter context. Dynamic calculated tables are not possible. What are you trying to achieve?

Re: CALCULATETABLE doesn't filter rows with current filter context

Handlordz — Tue, 17 Jan 2023 08:30:33 GMT

tamerj1
Thank you for your feedback and help for my issue.

I have to provide a list of exceptions from a portfolio file that entries don't match with my fact table 'f_MNT_visits'.
Below my complete DAX code which runs well and retuns the number of rows I expect when I specify explicitly the year:

However, this list varies according year selected by user from filter panel.
So I need to read dynamically year selected by user to filter the list accordingly.

As I mentionned in my first post, code below get max date from my dates table instead of max date from filter context (when the user selects a specific year).

I understand, it is not possible to read the filter context for a virtual table. Could you please confirm me ?