How to link a slicer simultaneously to both a PivotTable and a normal table

[Thamer_Tarabzouni]

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. 

[#2]

@Thamer_Tarabzouni You simply can't. Slicers can only connect to multiple pivot tables provided they share the same source data.

[#3]

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.

[#4]

@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.

[#5]

Hi @Thamer_Tarabzouni 

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?

[#6]

OK.

[#7]

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.

[#8]

What I previously pictured does it

[#9]

Hi, and how can you do this? i am having the same problem.

[#10]

Hi @tomasnavarro 

Actually, as you probably experienced, this isn't possible as of today
A possible workaround is doc. in Slicer on Table and PivotTable (works with > 1 slicer)

If you have issue putting this in place please let me know and share (i.e. with OneDrive, Google Drive or the like) a representative workbook - Thanks

[#11]

@Lorenzo 

Nice article!

[#12]

@Thamer_Tarabzouni 

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)
  )

[#13]

@PeterBartholomew1 

Nice one too. Doable with > 1 slicer?

[#14]

@Lorenzo 

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)
)

[#15]

@PeterBartholomew1 

Do you mind if I update the article I shared the other day, adding a link to this discussion and mentionning your 365 alternative?
(interesting to note that since I posted the article it got 20 more views, still nobody found it helpful)

[#16]

@Lorenzo 

Of course you may, use what you want and by all means put your own spin on it!

I did set up a help forum on LinkedIn for Lambda questions but it is still pretty much dead.  I do wonder to what extent the Excel user base is even aware of the extent to which the art of spreadsheet development has changed.  Power Query is still grossly underused and for Lambda it appears to be even worse.

[#17]

Thanks @PeterBartholomew1 

...by all means put your own spin on it! I won't, am too lazy 🙂

[#18]

@PeterBartholomew1 

Forgot to mention that I updated the article the other day. Hope that's fine with you otherwise let me know what should be changed & Thanks again

(obviously nothing was good enough to deserve any kind of feedback from @tomasnavarro ...)