locked
Get Slicer member using Cube function when only 1 member is present in the slicer RRS feed

  • Question

  • If the slicer has only 1 member/value then how can I get that 1 value in a cell using the CUBE functions. 

    The formula below results in "ALL" when there is only 1 item in the slicer. 

    =CUBERANKEDMEMBER(“CONNECTION NAME”,Slicer_Name,1)
    Thursday, January 14, 2016 9:09 PM

Answers

  • I was able to get the result using the approach/formulas below.

    Cell A1 first gets a list of all the Org.

    <<Cell A1>>=CUBESET("CONNECTION NAME","[PL].[Organization].[All].Children","Org")

    Then in the next cell, I put the formula below that first check if the set only has only 1 value. If yes, then returns that else checks the slicer. 

    =IF(CUBESETCOUNT(A1)=1,CUBERANKEDMEMBER("CONNECTION NAME",A1,1),IF(CUBESETCOUNT(Slicer_Name)> 1, "Multiple",CUBERANKEDMEMBER("CONNECTION NAME",Slicer_Name,1)))


    • Proposed as answer by Charlie Liao Monday, January 18, 2016 1:11 AM
    • Marked as answer by VivDev Monday, January 18, 2016 11:24 AM
    Sunday, January 17, 2016 1:04 PM

All replies

  • Hi VivDev,

    According to your description, you need to get slicer member using cube function, right?

    In a Pivot table, we can get the slicer member by using cube function. And the the sample expression would like below.
    =CUBESET(“PowerPivot Data”, Slicer_Description3,”This is My Set”)
    Here are the blogs which describes how to achieve this requirement step by step, please refer to the link below to see the details.
    http://www.powerpivotpro.com/2012/11/better-way-to-catch-multiple-slicer-selections-in-a-formula/
    http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/

    Regards,


    Charlie Liao
    TechNet Community Support


    Friday, January 15, 2016 5:58 AM
  • That is the approach I have followed but the problem is that when the Slicer has ONLY 1 member it shows value as "All". I am not able to get that 1 member. 

    Also, I know I can get it using the pivot table approach in the 2nd link but want to know if there is way to get it using cube functions alone which I believe there should be. 

    • Edited by VivDev Friday, January 15, 2016 10:47 AM
    Friday, January 15, 2016 10:46 AM
  • I was able to get the result using the approach/formulas below.

    Cell A1 first gets a list of all the Org.

    <<Cell A1>>=CUBESET("CONNECTION NAME","[PL].[Organization].[All].Children","Org")

    Then in the next cell, I put the formula below that first check if the set only has only 1 value. If yes, then returns that else checks the slicer. 

    =IF(CUBESETCOUNT(A1)=1,CUBERANKEDMEMBER("CONNECTION NAME",A1,1),IF(CUBESETCOUNT(Slicer_Name)> 1, "Multiple",CUBERANKEDMEMBER("CONNECTION NAME",Slicer_Name,1)))


    • Proposed as answer by Charlie Liao Monday, January 18, 2016 1:11 AM
    • Marked as answer by VivDev Monday, January 18, 2016 11:24 AM
    Sunday, January 17, 2016 1:04 PM