Using One Slicer to Filter Date Fields Across Multiple Tables

[Anonymous]

Hello All,

I have a time/labor report that references four tables... these tables are joined by employeeID. Each table has a date field related to the specific timesheet transaction. Since I have joined these tables on Employee ID, I cannot create active joins to these tables by date field. 

Please provide some guidance on how I could use a single slicer to filter all existing date fields for the same date range?

What I've tried so far is:

1. Created auto calendar, joined the date fields from each table to the date field in the auto calendar and used the auto calendar date field in the slicer. Results: Does not work.

2. Attempted to create a relationship on the date field between the tables but unable to do that due to existing joins.

Thanks,

greatly appreciated in advance.

[KNP]

The correct approach is to have a date table (don't forget to mark it as a date table) and join on that.

Can you show us a screen shot of your model?

I suspect there's something else going on.

[Anonymous · Accepted solution]

I figured out a very simple solution which is to just add each of the date fields from each dataset to the same slicer group and that works very well.

[KNP]

I'm glad you've solved your problem.

It's worth noting, this data model is not following recommended design and dealing with dates that way is not scalable/future proof.

I would recommend reading up on star schemas if you want to build robust solutions.

[Anonymous]

Thank you @KNP. I absolutely agree! For now I've been working with what I've been provided but have expressed some challenges with modelling the data in a more efficient, way. Since it's historical data that has now become static (point in time data, that will not change), scalability is not required.