CUBEMEMBER Solution Needed - showing a result that's not in the data

[deharris]

I'm working on a CUBEMEMBER string and it always returns me the last value regardless of whether that value is in the table or not. here's the actual text.

=CUBEMEMBER("ThisWorkbookDataModel",{"[Program Entry].[PROGRAM KEY].&[PROGRAM KEY DESCRIPTION]","[Program Entry].[PLANNER TYPE].&[PLANNER]"})

I'm trying to use the formula to return if the CUBEMEMBER says "PLANNER", "OPTIONAL" or blank (which would be an error message) if it doesn't find the string array shown here. When I change the final &[PLANNER] to [OPTIONAL], the formula result is OPTIONAL even though that result isn't in the data itself.

thoughts on how to solve?

 

[macfuller]

Do you need to be using CUBEVALUE here? Are you trying to return a scalar value or a data set?

Maybe if you can give a sample of the data you're looking to return...

 

[deharris]

Thank you for the reply @macfuller. I'm trying to return a text value. I have a list of 'planner' items and the Planner Type will be "PLANNER," "OPTIONAL," or "BLANK" depending on the data entry for the specific planner item. I tried inserting a photo but it wouldn't go through with the URL (that I can tell) so I'm pasting it below. I'm trying to solve for a cube formula that tells me the PLANNER TYPE if I solve for Customer, Program Item, Customer Subgroup and Planner Date

[TABLE="width: 682"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CUSTOMER[/TD]
[TD]PROGRAM ITEM[/TD]
[TD]CUSTOMER SUBGROUP[/TD]
[TD]PLANNER DATE[/TD]
[TD]PLANNER TYPE[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 1[/TD]
[TD]CUSTOMER SUBGROUP 1[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]PLANNER[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 1[/TD]
[TD]CUSTOMER SUBGROUP 2[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]OPTIONAL[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 1[/TD]
[TD]CUSTOMER SUBGROUP 3[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]OPTIONAL[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 2[/TD]
[TD]CUSTOMER SUBGROUP 1[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]PLANNER[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 2[/TD]
[TD]CUSTOMER SUBGROUP 2[/TD]
[TD]DECEMBER - 2017[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 2[/TD]
[TD]CUSTOMER SUBGROUP 3[/TD]
[TD]DECEMBER - 2017[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 3[/TD]
[TD]CUSTOMER SUBGROUP 1[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]PLANNER[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 3[/TD]
[TD]CUSTOMER SUBGROUP 2[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]OPTIONAL[/TD]
[/TR]
[TR]
[TD]CUSTOMER 1[/TD]
[TD]ITEM 3[/TD]
[TD]CUSTOMER SUBGROUP 3[/TD]
[TD]DECEMBER - 2017[/TD]
[TD]OPTIONAL[/TD]
[/TR]
</tbody>[/TABLE]

 

[macfuller]

Try using this formulation... assuming your table name is "MyTable"...

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel",
"([MyTable].[Customer].[Customer 1],
Do the same for your other selection fields...
[MyTable].[Planner Type].children)"),1)

If you're using slicers to select each filter value, put a CUBERANKEDMEMBER statement into a cell to return the value of each selected slicer and use the [MyTable].[Selected Field].[" & $B$3 & "]" format.

 

[deharris]

thank you for the insight. i've had to rework my data and I built the formula as close as I could to your recommendation but I'm getting #N/A.

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel",{"[Table 1].[Table 1 Column 1].&[Table 1 Column 1 Value]","[Table 2].[Table 2 Column 1].&[Table 2 Column 1 Value]","[Table 2].[Table 2 Column 2].&[Table 2 Column 2 Value]","[Table 3].[Table 3 Column 1].&[Table 3 Column 1 Value]","[Table 4].[Table 4 Column 1].CHILDREN"}),1)

I'm trying to solve for the text value of the value of Table 4 Column 1 if all the other values are found. It all works in PowerPivot and a can convert all of this in pivot tables to formulas so I'm sure I'm just missing some syntax somewhere. Thank you for any further insight.

 

[macfuller]

I'm not sure if you're offering your actual syntax, but the structure is

.[Column].[Value]. You don't repeat the table value for the other fields. You also don't need the curly brackets for the 2nd CUBESET parameter - it's one long string. I'm not sure if it matters but they would be inside the quote marks if you did use them. I'm also not sure if .children needs to be lower case but it works that way for me.

 

[deharris]

Your notes have enabled progress! The formula is now too long for one cell (exceeds character limits) so I tried a concatenate in the data itself. You were correct about the syntax not being exactly as shown with the [Table # Column #] reference, I was simply being too descriptive. I've shortened the formula to this (below) and the answer it returns is the text of the Program Key Value as opposed to the text of [Table 2].[Column 2].children. The [Table 2].[Column 2].children text is the variable that could be blank, "planner' or "optional." Your help has brought it closer. Any other advice?

=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","[Table 1].[Column 1].&[Program Key Value]","[Table 2].[Column 2].children"),1)

 

[macfuller]

CUBESET has only the one set expression parameter after the workbook data model. The way you've written it has [Table 2].[Column 2].children as the caption parameter. Everything in the set expression variable is a single string.