# Cubemember and Cubeset problem



## mfarr76 (Dec 23, 2015)

Hello,
I am entering the world of cube formulas and I am running into a problem that I haven’t seen a solution to.
I have a dataset that has 20 names in it that can be further split into 3 groups: A,B,and C.  My problem is displaying the names in a single row that belong to one of groups.
For example, Group A has 5 names in it.  I am currently using Cubmember that has a reference to a cell in the worksheet:
=CUBEMEMBER(“ThisWorkbookDataModel”,”[TABLE1].[NAMES].&[“&AK10&”]“)
I am trying to create a scatter plot with this data and I want to be able to use a slicer to change between groups, however this doesn’t work when you have hard references as in the equation above.
I tried using CUBESET and CUBERANKEDMEMEBER in which that shows all the names without a cell reference but I can’t filter groups when using this approach, its all or nothing.
Does anyone have any suggestions?
Thanks,
Michael


----------



## SimonNU (Dec 24, 2015)

I can only loosely grasp the problem you're experiencing.  If it is what I think it is, then I too have had the same issue in the past and I had to approach it from a different angle.  Are you able to explain it in a bit more detail? Or better yet, provide a workbook?


----------



## mfarr76 (Dec 24, 2015)

SimonNU

Sorry if I was not clear, I should've done a better job explain my problem.  

The link below is to a sample spreadsheet that illustrates my problem.  The names start in cell C8 and represent all the names.  

The slicer that is shown are groups that the names belong to.  I would like for the Cuberankmember cells to change according to the selected group in the slicer.  The way it is set up now is all the names are shown, I haven't figured out how to engage the slicer.  


https://www.dropbox.com/sh/lmqf77q3v4ymzo9/AAAletPEXDOKvrz6fkW4lD7Ea?dl=0


----------



## ImkeF (Jan 3, 2016)

It looks as if you want to filter your dataset by a slicer. To my knowledge this is not possible, but there is a workaround:
Excel Cubefunctions: Trick for dynamic sets – The BIccountant

I've applied it to your example here:
https://www.dropbox.com/s/91a1t2xjf2xlzgg/ME_CubesetAlternative.xlsx?dl=0


----------



## mfarr76 (Jan 4, 2016)

Imkef, 

Thank you, that worked great!  I appreciate your response.  

Can you explain why your offset formula uses -2 for the width?  I am a little confused by this.


----------



## ImkeF (Jan 4, 2016)

mfarr76,
thanks for the feedback - good to hear that it worked for you.

Re -2: This is the part that determines the length of the offset area. As we're referencing the whole column ($A:$A) we need to deduct the rows with the header and footer of the pivot table if we only want to return the rows with desired output in it.


----------



## mfarr76 (Jan 4, 2016)

Very good, thank you again!


----------

