• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Slicer not greyed out for items without data|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Slicer not greyed out for items without data|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
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 PivotSlicer not greyed out for items wit…
sp_PrintTopic sp_TopicIcon
Slicer not greyed out for items without data
Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
1
March 18, 2023 - 4:21 am
sp_Permalink sp_Print

I have data by Fiscal Year and Pay Period.  My data model includes a Fact Table, a dimension table for Fiscal Year that contains one row for each of the 5 most recent Fiscal Years, and a dimension table for Pay Period that contains one row for each pay period from 01 to 27.  Both of the dimension tables connect to the Fact Table by a one to many relationship:

Fiscal Year                            Fact Table
     FY (1)---------------------> (Many) FY                                   Pay Period
                                                    PP (Many) <---------------------(1) PP

     (Note: this is a simplified version of my data model; the actual model includes additional dimension tables)  

I used the two dimension tables shown above to add a slicer for Fiscal Year and a slicer for Pay Period. They both work fine; however, there is an issues with Pay Periods not greying out for Fiscal Years that have no data for those Pay Periods. For example, my current Fact Table does not include complete data for FY 22-23.  I only have data for Pay Periods 01 - 22 for FY 22-23.  However, when I select 22-23 from the Fiscal Year slicer, the Pay Period slicer still shows all Pay Periods in Blue, indicating that data are available for all Pay Periods.  I expected the Pay Periods after PP 22 to all appear grey, since there are no data for those PPs for FY 22-23.  

My slicer settings have a check next to both "Visually indicate items with no data" and "Show items with no data last."  I tried unchecking those and selecting "Hide items with no data" instead, but that did not change anything.  I still see all PPs for FY 22-23.

I also tried creating a new dimension table that contains both FY and PP.  In that table there is one row for each Pay Period that has available data for each Fiscal Year.  So, for example, for Fiscal Year 22-23, there are only 22 rows, one for each Pay Period that has available data.  I tried adding this new table to the data model shown above in a couple of different ways, but nothing I tried seemed to make a difference.  

Am I going in the right direction?  Is there some way I can use this new combined FY/PP table to solve my problem?  I tried connecting it to the Fact Table, but that resulted in an error message due to it being a Many-to-Many relationship.  I also tried connecting it to both the FY dimension table and the PP dimension table, but that does not seem to have addressed the issue--Pay Periods 23-27 still look like they have data when I slice on FY 22-23.

Thank you for any advice you can provide!! 

~Jennifer 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 20, 2023 - 10:44 am
sp_Permalink sp_Print

Hi Jennifer,

I am not able to replicate the issue. See file attached. Please modify this file by adding data that replicates the problem you're having so we can help further.

Mynda

Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
3
March 21, 2023 - 2:08 am
sp_Permalink sp_Print

Please see attached for my updated Excel file.  I added a bit more data to the Fact Table, including a new column (PP LE Current), which flags rows as 1 if the Pay Period is less than or equal to the most recent PP for the current Fiscal Year.  In this case, the most recent PP is PP 2 for FY 22-23, so any row listing a PP <= 2, is coded as 1 and the rest are coded as 0.  I also added a dimension table for this new variable, and then created an new Pivot Table that incorporates both this new variable and FY, along with a chart.  

Now when you select FY 22-23 from the slicer, you can see my problem. Pay Period 3 does not grey out even though there are no data for PP 3 for FY 22-23.  I believe the problem is related to the new Pivot Table and the fact that it is not connected to the Fiscal Year slicer.  When the Table gets sliced by FY rows disappear.  I did not want rows to disappear because I want all FYs to be maintained in my final graph so the horizontal axis does not change.  As in my original workbook, I tried going to the Pivot Table opinions and Selecting "For empty cells show 0" and "Show items with no data" (for both columns and rows), but rows still disappeared when I used the FY slicer, so I disconnected the FY slicer from the new Pivot Table.  For some reason, when this new Pivot Table is not connected to the slicer, the relationship seems to break between the FYs and PPs.  If I select FY 22-23, it looks like there is data for all three PPs, when there is not.  

I'm not sure how to correct this issue besides connecting the new Pivot Table to the FY slicer, but then that messes up my chart data, which depends on the shape of the Pivot Table staying the same.  I considered using GETPIVOTDATA references, rather than cell references, to obtain the data for my graph from the Pivot Table, but that won't work when new FYs are added (my data model is set up to always display the 5 most recent FYs contained in the source data, so the Fiscal Year will change over time).  

Any thoughts?  

Thank you!! 

Jennifer 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 21, 2023 - 10:05 am
sp_Permalink sp_Print

Hi Jennifer,

The Pay Period slicer will only grey out if the Fiscal Year Slicer is also connected to both PivotTables or, the Pay Period slicer is not connected to PivotTable1 (the one in orange).

It's not that the relationship is breaking, it's simply that one of the PivotTables is not filtered by the Fiscal Year, therefore there is still data for PayPeriod 3 in PivotTable1.

It seems like the Pay Period slicer doesn't need to be connected to PivotTable1. If so, removing it fixes the issue with the pay periods with no data being greyed out.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Jennifer Owens
Member
Members
Level 0
Forum Posts: 7
Member Since:
June 29, 2022
sp_UserOfflineSmall Offline
5
March 22, 2023 - 1:51 am
sp_Permalink sp_Print

It worked!!! Thank you so much.  You are my Excel guru 🙂 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
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.