• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Issue with slicer for data model |Power Query|Excel Forum|My Online Training Hub

You are here: Home / Issue with slicer for data model |Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryIssue with slicer for data model
sp_PrintTopic sp_TopicIcon
Issue with slicer for data model
Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
1
June 29, 2022 - 5:17 am
sp_Permalink sp_Print sp_EditHistory

Hello everyone,

I created a data model using Power Query (see first attachment, "Data Model"). 

Data-Model.pngImage Enlarger

The model includes 4 tables:

  • Main Incident Extract - This table includes one record for each unique incident that occurred at one of our facilities 
  • Incident Types - This table include one or more records for each incident.  If, for example, incident 1234 involved both an Assault and Contraband, this table would list incident 1234 two times, once for Assault and once for Contraband, so it's a one to many relationship between Main Incident Extract and Incident Types.  The two tables connect via ID_REPORTABLE_INCIDENT.
  • Youth Details - We'll ignore this one for now, but just so you know, it connects to Main Incident Extract via ID_REPORTABLE_INCIDENT.  Each incident can involve more then one youth so it's a one to many relationship.  
  • Caredays Final - We'll also ignore this one for now, but just so you know it connects to Main Incident Extract via ID_FACIL. Each facility has one care day count but many potential incidents so the relationship between Main Incident Extract and Caredays Final is many to one.

My issue is when I want to slice the incident data by Incident Type.  For example, as shown in the second attachment ("Pivot Table 1"), 188 of the 334 incidents that occurred in the most recent month (i.e., MON13) involved a Disturbance.

Pivot-Table-1.pngImage Enlarger

Let's say I want to look at those 188 Disturbance incidents alone.  To do that, I added a slicer for CD_RI_INCIDENT_TYPE, which is in the Incident Types table, connected it to all pivot tables in my workbook, and then selected Disturbances (see third attachment, "Slicer").

Slicer-1.pngImage Enlarger

After selecting Disturbances, I do see that my pivot table listing the incidents by type adjusts to only show the 188 Disturbances...

Pivot-Table-1-Sliced.pngImage Enlarger

... However, when I look at my other pivot tables I am still seeing a total of 334 incidents, not the 188 I expected.  For example, Pivot Table 2, which shows the incidents by time of day (a variable/field from Main Incident Extract) still has all 334 incidents, not just the 188 that involved a Disturbance.

Pivot-Table-2.pngImage Enlarger

This pivot table only contains variables from Main Incident Extract, but I had assumed, since I connected Main Incident Extract to Incident Types using ID_REPORTABLE_INCIDENT, that slicing on incident type (i.e., CD_RI_INCIDENT_TYPE) should work, even though the variables used in this pivot table come from the Main Incident Extract only.

Is there something I'm missing?  How can I get the slicer to work so that pivot tables based on the Main Incident Extract can be sliced by incident type?

Thank you!!!  I'm new to using data models so I really appreciate the help.  

sp_PlupAttachments Attachments
  • sp_PlupImage Data-Model.png (53 KB)
  • sp_PlupImage Pivot-Table-1.png (32 KB)
  • sp_PlupImage Slicer.png (11 KB)
  • sp_PlupImage Slicer-1.png (12 KB)
  • sp_PlupImage Pivot-Table-1-Sliced.png (13 KB)
  • sp_PlupImage Pivot-Table-2.png (64 KB)
Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 218
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
June 30, 2022 - 12:13 am
sp_Permalink sp_Print

I couldn't find an article or video for it, though Mynda does show this concept in most of the dashboard videos I have seen; the connection of the pivot tables to the slicers comes from the report connection, not the relationship of the tables. As long as your PTs are using the same pivot cache you simply right-click your slicer>Report Connections which will bring up a list of the PTs you can connect the slicer to and simply choose the PTs you want to control with that slicer. Hope that makes sense and answers your question. Good luck! 🙂

Avatar
Adam Bender
Minneapolis, MN

Active Member
Members

Power Query
Level 0
Forum Posts: 5
Member Since:
June 14, 2022
sp_UserOfflineSmall Offline
3
June 30, 2022 - 2:18 am
sp_Permalink sp_Print

So, are you saying that you have multiple pivot tables that you'd like controlled by a single slicer?

If so, take a look at this page.  https://excelchamps.com/pivot-.....ot-tables/

Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
4
June 30, 2022 - 3:44 am
sp_Permalink sp_Print sp_EditHistory

I already connected all pivot tables to the slicer.  The issue is that the slicer for Incident Type seems to be filtering any pivot tables that only include data from the Incident Types table, but not data based on the other tables.  For example, if I select Disturbances from the slicer and I look at my pivot table that shows the incident counts by type (which is based on data from the Incident Type table), the pivot table adjusts as expected to only show the 188 incidents that involved a disturbance; however, if I look at my pivot table and chart that show the incident counts by time of day (which is based on data from the Main Extract), I am still seeing all 334 incidents.   I would like to see only the 188 incidents that involved a disturbance appear in the time of day pivot table and graph.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4524
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
June 30, 2022 - 8:59 am
sp_Permalink sp_Print

Hi Jennifer,

The issue you're having is caused by the direction of the relationships in your model. If you look at the arrows on the connector lines it tells you the filter direction. You can see that the Main Incident table is filtering the Incident types table. Based on your question this is the wrong way around. If you want to filter the main incident table based on a field in the incident types table then the arrow on the connector line needs to be going towards the main incident table.

It's not as simple as changing the direction, because your incident types table is on the many side to the relationship, whereas it most likely should be the other way around. i.e. I'm assuming the Indident types and youth details tables are your dimension tables and your Main Incident and Caredays Final tables are your fact tables. Dimension tables filter fact tables and dimension tables must be on the 'one' side of the many to one relationship, with the fact table on the many side.

It looks like your Main Indident table is also not in the correct tabular layout required for Power Pivot i.e. you have multiple 'Mon' columns. Likewise for your Caredays Final table with multiple 'days' columns. You need to fix this layout with Power Query's Unpivot tool before proceeding further.

There are many problems with this file which tells me you haven't learnt the fundamentals of Power Pivot and now you're running into insurmountable roadblocks. You must be careful building Power Pivot models because sometimes Power Pivot won't return an error telling you something is wrong. It can return values that appear to be correct, so be sure to manually cross check your PivotTable results.

I hope that helps explain things. It's difficult to cover everything here. I recommend you take my Power Pivot & DAX course so you can get to grips with how to set up your data correctly so that you can create the calculations you're after.

Mynda

Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
6
July 1, 2022 - 12:08 am
sp_Permalink sp_Print sp_EditHistory

Thank you for your help.  I will play with it some more.

These data get complicated.  The Main Incident Extract contains one row for each unique incident that occurred at one of our facilities over the past 13 months.  Each incident can involve multiple types (e.g., Contraband, Assault, Accident) so those need to be in a different table connected via a one to many relationship.  The same goes for the youth table.  Each incident can involve one or more youth, so once again, those records are saved in a different table that connects to the main incident table via a one to many relationship.  The care days table is there so I can calculate incident rates per 100 care days.  That table contains one record for each facility and the caredays they accrued during each of my thirteen months.  The slicers I want to use in the main incident table work fine (counts are as expected) and the slicers in the caredays table work fine.  Based on what you're saying, it sounds like I'll have issues with the other two tables (incident type and youth) due to the one to many relationship with my main incident table. I'll see if I can restructure the data somehow.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4524
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
July 1, 2022 - 8:59 am
sp_Permalink sp_Print

You should be able to use Power Query to restructure your data.

While the PivotTables you currently have are working fine now, the multiple Mon and Days columns will cause you problems eventually. This PivotTable video tutorial illustrates the issue.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Orlando Menes, Alicia Greynolds, Lee Mapes
Guest(s) 5
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Blair Gallagher
Brandi Taylor
Hafiz Ihsan Qadir
Gontran Bage
adolfo casanova
Annestine Johnpulle
Priscila Campbell
Jeff Mikles
Aaron Butler
Maurice Petterlin
Forum Stats:
Groups: 3
Forums: 24
Topics: 6369
Posts: 27852

 

Member Stats:
Guest Posters: 49
Members: 32359
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x