Forum Discussion

Thamer_Tarabzouni's avatar
Thamer_Tarabzouni
Copper Contributor
Oct 11, 2022

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

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 inse...
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 11, 2022

Thamer_Tarabzouni 

Alternatively if you run Excel > 2010 on Windows, load your SourceTable to the Data Model (Power Pivot):

 

 

in I11 (returns the 1st item selected in Slicer_Name😞

=CUBERANKEDMEMBER("ThisWorkbookDataModel",
    CUBESET("ThisWorkbookDataModel", Slicer_Name),
    1
)

 Corresponding sample attached

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 20, 2023

Lorenzo 

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)

 

Resources