How to list relationships between Slicers and Tables/Charts VBA

Yep, ANOTHER post... To be honest, I've been working on this solution for a while and was quite excited when I finally got it working.  So, now I get to share it with you!

Have you ever created a monstrosity of a dashboard that has MANY slicers and MANY charts?  Well, I have, and it gets to be unruly quite fast.

OR, have you ever had to take over a dashboard developed by someone who is no longer around to make modifications or support the file?  Again, I have and it can be a bit daunting to have to figure out what they were thinking and their organizational strategies. 

One of the biggest issues that I have had is knowing which slicers are connected to which charts.  Yes, I know you can look at each and every slicer, go to "Report Connections" and see what is checked, but how do you then quickly look at the other related slicers that should be connected to the same charts as the first? 

Sure you could grab a couple of snapshots or write them down, but I must admit, that doesn't work for me.  Or to quote a viral YouTube sensation "Ain't nobody got time for that!"

So, after much complaining and digging through the VBA object library and looking around the internet (where I found the incomplete start for this code), I have developed the following:

Option Explicit


Sub MultiplePivotSlicerCaches()
    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As PivotTable
    Dim oSh As Worksheet
    Dim Row As Integer
    Dim ObjChart As Chart
    Dim x As String
'Create New Worksheet & Table Structure
Sheets.Add.Name = "HIDDENSlicerConnections"
Sheets("HIDDENSlicerConnections").Cells(1, 1).Value = "Slicer Name"
Sheets("HIDDENSlicerConnections").Cells(1, 2).Value = "Pivot Parent Name"
Sheets("HIDDENSlicerConnections").Cells(1, 3).Value = "Pivot Name"
Sheets("HIDDENSlicerConnections").Cells(1, 4).Value = "Chart Title"
'Add Slicer Cache Name and Pivot Table Name
Row = 2
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.PivotTables
            oPT.Parent.Activate
            Sheets("HIDDENSlicerConnections").Cells(Row, 1).Value = oSlicercache.Name
            Sheets("HIDDENSlicerConnections").Cells(Row, 2).Value = oPT.Parent.Name
            Sheets("HIDDENSlicerConnections").Cells(Row, 3).Value = oPT.Name

            If InStr(oPT.Name, "Chart") < 1 Then GoTo Skip
            Sheets("HIDDENSlicerConnections").Cells(Row, 4).Value = oPT.PivotChart.Chart.ChartTitle.Caption

Skip:
            Row = Row + 1
        Next
    Next
   
End Sub

This code will create a table on a new spreadsheet called "HIDDENSlicerConnections" and list out every slicer and the chart/table that it is connected to. 

The first file that I used this on, I discovered that with all of the filters and charts and tables, I had a combined 238 connections in my file.  Again, "Ain't nobody got time for that!" - Okay, I'm done quoting....maybe.

Hold on, this is a cool macro, but it's no more than a raw data puke! We need to make sense of it. 

Next step is to create a pivot table based on the dataset.  Highlight the whole dataset, insert > PivotTable.  I prefer to keep the table on the same sheet as the dataset so choose a location and click OK. 

Now, to organize the data.  Do the following:
Row = Pivot Name
Row = Chart Title
Column = Slicer Name
Value = Pivot Parent Name: COUNT()

This will now create a table that shows the chart/table name down the left and Slicer name across the top and show a "1" for each connection that exists. SWEET!  No more snapshots and no more writing!  I'm down with that.

Edit: To use the Macro, follow these steps:

• Open your file > Press Alt + F11 to Open the VBA Editor
• In the left-hand pane, find the VBAProject([YOUR FILE NAME HERE]).  
• Right-click the "Microsoft Excel Objects" Folder and Select Insert > Module
• Copy/Paste the code into the module.  
• Click the "Play button" or Press F5 on the Keyboard to run the Macro.

If you are deploying your dashboard to SharePoint using Excel Services, your files cannot use Macros.  Simply continue to save as a .xlsx file and all will be well.  The Macro may not save, but you've already run it, and you can always find again right here on the blog!

Rolling 12 Months Chart with PowerPivot

Once again, work has provided me with a great article topic!  A request recently came across my desk to create a rolling 12 & 24 months average.

Here is how to do it:

First thing is first: You will need to have the “Last Updated” Table&Column in your Model.

 If you can connect to a SQL Server database, I create this by adding a table "Last Updated" with the following SQL as source (This also tells the users how new the data is):

Select GetDate() as ‘Last Updated'

This method is preferred to simply using the Today() formula in a linked table because your source data may not be refreshed daily.  For example, we have some financial dashboards that are only refreshed once per month on the first day of the month.

Next, create a calculated measure on the "Last updated" table with the following formula:

LastUpdatedMeasure:=sum([Last Updated])

Next, on your Fact table that you are going to use to create the rolling average (or whatever rolling metric you desire), add the following Calculated Measures to determine start and end dates for the rolling year ([last updated date] - [12 months]):

RollingYearEnd:=EOMonth([LastUpdatedMeasure],-1)

RollingYearBegin:=1+edate([RollingYearEnd],-12) - You can make this 2 years by changing "-12" to "-24".

Finally, use the following calculate formula to run whatever calculation you are looking for within that rolling year. This is the measure you will use as the “Value” on your chart or pivot table.

The formula below has multiple parts that I will explain:

=Calculate(expression, filter1, filter2..)

=Average(Column)

=Filter(TableName, TableName[Filter Criteria])

"&&" - This allows you to use an "AND" in your Filter Criteria.  Example: Event Date is >= RollingYearBegin "AND" Event Date is <= RollingYearEnd  - This ensures your data is between the start and end date. You can also include a column to qualify the event to be included by simply adding another "&&"

RollingYear_Avg:=calculate(average([ColumnToBeAveraged]),filter(FactTableName,FactTableName[EventDateColumn]>=[RollingYearBegin]&&FactTableName[EventDateColumn]<=[RollingYearEnd]&&FactTableName[QualifyingEventColumn]="Y"))

The final part of the formula &&FactTableName[QualifyingEventColumn]="Y" was required in my case because not all of the rows in my dataset qualified to be included in the average.  This is not required to make this formula work.

So, now that you are an expert in rolling year formulas, go out and impress the world!