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