Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tslupphaug
Regular Visitor

Filter multiple fields/visualisations using one slicer

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?

1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

6 REPLIES 6
Mikelytics
Resident Rockstar
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@tslupphaug 

In the end it should look like this

Mikelytics_0-1674320955511.png

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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".

tslupphaug_0-1674334352169.png

 

 

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

tslupphaug_1-1674333547486.png

 

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.

tslupphaug_2-1674333638649.png

 

 

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. 😕

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?

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.