How to capture the selected value of a slicer in a formula?

Question

I need to capture the selected value of a slicer like ID or Date. In browsing the Slicer Settings, there a defined name for the Slicer and stated that its can be used in a formula. I tried to in insert in a cell =INDERECT formula or simply =defined name but none did work.

Is there a way to get the value of selected slicer?

sergeibaklan · Answer

Lorenzo&nbsp;
And if on 365 to return multiple selections
slicerSelection=
LAMBDA( connection, slicer,
LET(
    itemsSelected, CUBESETCOUNT( CUBESET(connection, slicer) ), 
    CUBERANKEDMEMBER(connection,slicer, SEQUENCE( itemsSelected )))
);

called as
=slicerSelection("ThisWorkbookDataModel", Slicer_Name)

&nbsp;

lorenzo · Answer

Thamer_Tarabzouni&nbsp;Alternatively if you run Excel &gt; 2010 on Windows, load your SourceTable&nbsp;to the Data Model (Power Pivot):&nbsp;&nbsp;in&nbsp;I11 (returns the 1st item selected in Slicer_Name😞=CUBERANKEDMEMBER("ThisWorkbookDataModel",
    CUBESET("ThisWorkbookDataModel", Slicer_Name),
    1
)&nbsp;Corresponding sample attached

riny_van_eekelen · Answer

Thamer_Tarabzouni&nbsp;The attached article clearly demonstrates how to do just that. And at the end it also explains the real meaning of the bit "Name to use in formulas:"https://www.myonlinetraininghub.com/use-excel-slicer-selection-in-formulas&nbsp;

banderasxp · Answer

hi&nbsp;Lorenzo&nbsp;, I just want to ask if you know how to solve the duplicated value that is not displaying in pivot table just like in the photos. Even if I click in the slicer, duplicated value will just show 1 result. please help. Thank you.&nbsp;

lorenzo · Answer

banderasxp&nbsp;You're asking for a PivotTable to not behave as a PivotTable (what's the point???)&nbsp;If you run Excel &gt; 2013 / Windows you can do it though:- Load the Pivot Source table to Power Query- Add an ID (Index) column- Build the PivotTable from the query- Add the ID column to the Rows area of the PivotTable- Hide the column that holds the [ID] field&nbsp;Sample attached

Forum Discussion

How to capture the selected value of a slicer in a formula?

Share

Resources