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
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
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
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
It worked!!! Thank you so much. You are my Excel guru 🙂
