Filtering a slicer with context


#1

Hi

I have, what on the face of it, seems quite a simple scenario. I need to filter dates shown on a report page based on the dates that are relevant to the project being shown (via selection)

I have a date table that lists all StartDates (first of each month) - currently this shows for three years but will be much larger when the report is put into production.

I also have a table per project and a table that stores transactions and budgets for each project

The below diagram shows all the data before any selections are made

I then select the project I want to investigate (as below)

As you can see the third table has now reduced to only show the StartDates in which the selected project has either ledger transactions or budgets applicable. This is great. But my slider is not being affected by the project selection and is still showing the full 3 year date range… I need this to be filtered down?

Adding a visual or page level filter will not work and I am unable to figure out how to create a calculated table that would dynamically change based on the project selected?

Any suggestions for a different approach to solve this one?


#2

I would guess this is all driven by the relationships you have.

So the projectID is unable to filter across to STARTDATE likely because the STARTDATE is the one side of the relationship.

That’s my thinking without seeing the model.

Check out the strategy I use is this particular example.

I think just like this example you may need to create a brand new table that with automatically filter by the start and end dates derived from the PROJECTID

Check out the model around the 9 min mark

This is likely the strategy you need to use.

Somehow the model needs to be setup so that when you select a projects it can think filter another table based on that selection.

See how you go with this idea and let me know.