Cubemember and Cubeset problem

[mfarr76]

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]

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]

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]

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]

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]

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]

Very good, thank you again!