Forum Discussion
How to link a slicer simultaneously to both a PivotTable and a normal table
Hi,
I created a table from Power Query and I created several Slicers (Dept, ID, Date, Time). This to dynamically filter the list of rows displayed by the table. Also I created a pivot table to display some analysis of this filtered rows.
My problem is that I can't make the Slicers I created for the table to be also connected to Pivot Table. Because the option to connect is Dimmed in these slicers. Also, If I created slicers from the pivot table, I can't link them to the table.
The objective is to link all slicers to both data table and Pivot table so I can work and display the list of rows and analysis for same resource. Note I tried to create the slicers for the data table, then use the define a table name and use the table name in the Pivot table, but the pivot table somehow ignores the filtered data and work on the data table rows as a whole.
Thamer_Tarabzouni You simply can't. Slicers can only connect to multiple pivot tables provided they share the same source data.
- Hi, I have a data table "Data_Tbl" which also is the data source table for the Pivot table. I created Slicers for this data table. When I created the Pivot Table, as I stated, using this data table as a data source, I created the same set of Slicers. The Slicers which created for the data table "Data_Tbl" is functioning well with the data table. But when I open their properties window, I see that report link option is "Dimmed" and not configurable. Unlike the one in the Pivot Table slicers, which you can select which Pivot tables to apply on.
Thamer_Tarabzouni A slicer connected to a Table can only control that one table. Accordingly, the Report Connections button is greyed out. Pressing a button on such a slicer has the same effect as filtering rows with the filter button in the column headers of the table. It merely hides/unhides rows. Pivot Tables that have such a Table as their source are not affected by the "Table-slicers".
However, when you create multiple pivot tables from the same source, you can create a set of slicers that connect to all pivot tables that share the same pivot cache.
If this makes no sense to you, I may have misunderstood your intentions.
To clarify things, is this what you're trying to achieve?
where the 2 slicers (Customer & Product) are connected to and filter the Data and the Pivot?
- Yes, this is what I need. A slicer that can be connected to both Data table, and Pivot table. So then I can use them interchangeably.
- What I previously pictured does it
This doesn't directly answer your question but, since I have always disliked Table filters that simply hide rows, I took a slightly different route. Namely, I linked the slicers to a Pivot Table and then read the PT Row Labels in order to apply the 365 FILTER function to the source data table.
= LET( include, BYROW(InputTable[Name]=TOROW(rowLabels, 3), OR位), data, FILTER(InputTable, include), totals, HSTACK("Totals", "", SUM(TAKE(data,,-1))), VSTACK(InputTable[#Headers], data, totals) )Nice one too. Doable with > 1 slicer?
Doable, yes. But I don't know whether it is the best approach or whether it would be better to have a separate helper pivot table for each dimension.
= LET( region?, BYROW(InputTable[Region] = UNIQUE(TOROW(rowLabels1, 3)), OR位), sector?, BYROW(InputTable[Sector] = UNIQUE(TOROW(rowLabels2, 3)), OR位), data, FILTER(InputTable, region? * sector?), totals, HSTACK({"Totals", "", ""}, SUM(TAKE(data, , -1))), VSTACK(InputTable[#Headers], data, totals) )
