Forum Discussion
Custom table is not working when filtering on date
Hi Jack00711, you don't need to build a calculated table for this case. Calculated table is refreshed once, when your semantic model is refreshed. Therefore, any further interaction with slicers won't change it.
If you already have a classic data model like this, the only thing you need to do is to apply a filter on "Date" column from "Calendar" table, it will filter the fact table accordingly.
Then you create DAX measure that will show you data for all fields selected before the current date, but when using data in slicer visual there is an easier way:
You can find sample pbix attached.
Good luck with your project 🙂
Super UserHi Jack00711 ,
If you want to dynamically filter the data based on the slicer, you should not use a calculated table like FilteredTasks, as it does not respond dynamically to slicers. Instead, you can achieve the desired behavior in one of the following ways:
Option 1: Add a Measure
You can create a measure to use directly in your visual:
FilteredTaskValue =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
SUMX(
FILTER(
Tasks,
Tasks[Date] <= SelectedDate
),
Tasks[Value]
)
You can then add this measure to your table or matrix visual, and it will dynamically display the sum of Value for tasks with dates less than or equal to the selected date.
Option 2: Use a Visual-Level Filter
If you prefer using a calculated column or table, try applying a visual-level filter instead of relying on a calculated table. For example:
- Use the original Tasks table for your visual.
- Add a slicer with Calendar[Date].
- In the table or matrix visual, set a filter on Tasks[Date]:
- Condition: Tasks[Date] <= SelectedDate.
Option 3: Modify the DAX for Calculated Table
If you insist on creating a calculated table, you can modify the DAX formula as follows:
FilteredTasks =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
FILTER(
Tasks,
Tasks[Date] <= SelectedDate
)
Then, use this table in your visuals. Note that the calculated table is static and does not respond to slicers unless you refresh the model.
I hope the provided solution works for you
If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.