Forum Discussion

Thamer_Tarabzouni's avatar
Thamer_Tarabzouni
Copper Contributor
Oct 06, 2022

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...
Excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 07, 2023

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

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 08, 2023

    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)

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Dec 08, 2023

      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.

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 08, 2023

        Thanks PeterBartholomew1 

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

Resources