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

banderasxp's avatar
banderasxp
Copper Contributor
Feb 20, 2023

hi Lorenzo , 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.

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Feb 21, 2023

    banderasxp 

    You're asking for a PivotTable to not behave as a PivotTable (what's the point???)

     

    If you run Excel > 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

     

    Sample attached

    • banderasxp's avatar
      banderasxp
      Copper Contributor
      Feb 21, 2023
      wow, thank you, so my problem is I don't have an Id for each person. Thank you so much, God bless❤

Resources