Filter multiple fields/visualisations using one slicer

[tslupphaug]

Hi. 

I want to make a really simple report showing how many items have been created in each of three different SharePoint Lists on any given day using a date slicer. The lists/tables contains unrelated data, and are not linked through any relationships. 

I can easily add e.g. "Count of title" for each list, and add corresponding date slicers for each list linked to "Created". But this way I would manually need to change all three slicers each time I'd like to see numbers for another date/period. 

How can I link all three lists to only one date slicer, so that I can select a date and have it simultaneously apply to all three lists?

[Mikelytics · Accepted solution]

Hi @tslupphaug 

Please create a Date Dimension Table and link it with a 1:n relationship to the three tables. WHen you then put the date field of the Date Dimension Table into the slicers then all tables will be filtered.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[Mikelytics]

@tslupphaug 

In the end it should look like this

being the Fact tables your sharepoint lists.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[tslupphaug]

Thank you very much for your quick and thorough reply @Mikelytics!

I've followed your steps, but encountered some issues. Can't see what i did wrong.

Made a Date Dimension Table and created relasionships between DimDate's "Date" to Facts tables' "Created".

Then I added all my three Sharepoint Lists to the report, and a date slicer with Date from my new dimDate table.

However, when I start making changes to the slicer start date, all contents just disappear, and doesn't come back even if I change the start date back to 01.01.2023. All values are listed again if I clear the slicer filter. Something is clearly not right, but can't see where I went wrong.

Got any idea what I might have done or things I could test/check?

P.S.: Tried starting all over again making a new file/report and repeating all the steps. Got the exact same result. 😕

[Mikelytics]

Hi @tslupphaug . Are all columns formatted as date? And are all dates which are listed in the dimension table also in the fact table and vice versa?

[tslupphaug]

The date columns for the SharePoint lists (Facts tables) were formatted as Date/time, while dimDate was formatted as date only. Made duplicates with date only and updated relationships, and it all works as intended now.

Thanks a lot for all your help!

[mangaus1111]

Hi @tslupphaug ,

you need to create a calendar table and then create 1 to many relationships between the calendar table and the facts tables, using the column Calendar[Date] as filter in your report.