Forum Discussion

tslupphaug's avatar
tslupphaug
Regular Visitor
3 years ago
Solved

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 unr...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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's avatar
Mikelytics
Resident Rockstar
3 years ago

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's avatar
Mikelytics
Resident Rockstar
3 years ago

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's avatar
    tslupphaug
    Regular Visitor
    3 years ago

    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's avatar
      Mikelytics
      Resident Rockstar
      3 years ago

      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's avatar
        tslupphaug
        Regular Visitor
        3 years ago

        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!