Excel allows you to connect a slicer to multiple data sources using the power of Power Pivot. In the past a slicer could connect to multiple pivot tables provided those pivot tables shared the same source data. With the advent of Power Pivot there is a way to have separate tables that share a common field and link that field in the Diagram View of Power Pivot so that the tables are able to talk to one another.
The following YouTube video takes you through the process from start to finish.
First things first - we need to sent a minimum of 3 tables to Power Pivot. In the following example I will send the revenue table, the expense table and finally the region or location table. We will connect these 3 tables together in the diagram view.
Open the file - Create 3 tables.
Location - on the Region Tab.
Revenue - on the Revenue Tab.
Expense - on the Expense Tab.
From the Power Pivot Menu - Choose Add to Data Model.
Do the same for all 3 tables and you will have all 3 data sets in the Power Pivot back end.
Now Insert a 2 PivotTables from the Home menu. One from the Revenue table and one from the Expense table.
Be sure to use the Location table as the filter for both pivot tables, one from Revenue and one from Expense.
Insert the Slicer
Inserting the slicer is the second last part of the process. Click inside any of the 2 pivot tables and choose the Analyze menu.
Choose Insert Slicer. This will bring up the following dialog.
Be sure to tick Region from the Location table. This will give you access to both tables as the connection was set up in Power Pivot.
The above slicer is created.
Now right click the Slicer and choose Report Connections.
Be sure to choose both the Revenue and Expense Pivot tables. This should allow you to control both pivot tables from two completely different data sources.
Alternatively, select the slicer, go to the Slicer Tools Options tab > Slicer group, and click the Report Connections button. In the Report Connections dialog box, select all the pivot tables you want to link to the slicer, and click OK.
With the Report Connections icon, you can have a slicer control multiple pivot tables on a dashboard—but only if all the pivot tables are from the same data set. Fortunately, there's a separate way to control multiple pivot tables from different data sets, provided they have one field in common.
To create pivot table slicers, Click on the table to insert a MONTH Slicer in the first Pivot Table. Then go to PivotTable Tools. "Analyze/Options" will pop up. Select Insert Slicer. Click "Month." Lastly, click "OK."
Here's how to connect another pivot table to your slicer control: Make sure each pivot table was created using the same source data. This means that if the source of the first pivot table was an Excel table called “My_Source”, the other pivot table(s) must use the same table name. Otherwise, it won't work.
On the Ribbon, select the Table Tools Design tab. Click Insert Slicer, check the box next to Food, and then click OK. Now we have a slicer linked to both our table and our chart. To filter, click an item under the Food heading and then see the chart and table update.
Relational databases are not the only data source that lets you work with multiple tables in a PivotTable Field List. You can use tables in your workbook or import data feeds that you then integrate with other tables of data in your workbook.
Select the slicer that you want to share in another PivotTable. This displays the Slicer tab. On the Slicer tab, select Report Connections. In the dialog box, select the check box of the PivotTable in which you want the slicer to be available.
Here are the top 10 alternative methods to enable multiple selection in Power BI slicers: Ctrl+Click or Shift+Click: Users can hold down the Ctrl key and click on multiple items to make non-contiguous selections or use the Shift key for contiguous selections.
The first step would be to create a grouping by highlighting the 3 cells within the pivot table, right clicking and clicking on GROUP. Once you have done this, you can go back into the Insert Slicer option as shown below and you will notice that there is a new slicer option for the grouped field.
You can't change the data source for a slicer. You can change the data source for the underlying PivotTable's PivotCache, but that will clear the filter. So that won't actually help. However, you can filter a PivotTable or Slicer so they match a list of terms in a range.
Address: Apt. 935 264 Abshire Canyon, South Nerissachester, NM 01800
Phone: +9752624861224
Job: Forward Technology Assistant
Hobby: Listening to music, Shopping, Vacation, Baton twirling, Flower arranging, Blacksmithing, Do it yourself
Introduction: My name is Nathanial Hackett, I am a lovely, curious, smiling, lively, thoughtful, courageous, lively person who loves writing and wants to share my knowledge and understanding with you.
We notice you're using an ad blocker
Without advertising income, we can't keep making this site awesome for you.
