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

deharris

New Member
Joined
Dec 15, 2017
Messages
14
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

Active Member
Joined
Apr 30, 2014
Messages
251
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

New Member
Joined
Dec 15, 2017
Messages
14
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

CUSTOMERPROGRAM ITEMCUSTOMER SUBGROUPPLANNER DATEPLANNER TYPE
CUSTOMER 1ITEM 1CUSTOMER SUBGROUP 1DECEMBER - 2017PLANNER
CUSTOMER 1ITEM 1CUSTOMER SUBGROUP 2DECEMBER - 2017OPTIONAL
CUSTOMER 1ITEM 1CUSTOMER SUBGROUP 3DECEMBER - 2017OPTIONAL
CUSTOMER 1ITEM 2CUSTOMER SUBGROUP 1DECEMBER - 2017PLANNER
CUSTOMER 1ITEM 2CUSTOMER SUBGROUP 2DECEMBER - 2017
CUSTOMER 1ITEM 2CUSTOMER SUBGROUP 3DECEMBER - 2017
CUSTOMER 1ITEM 3CUSTOMER SUBGROUP 1DECEMBER - 2017PLANNER
CUSTOMER 1ITEM 3CUSTOMER SUBGROUP 2DECEMBER - 2017OPTIONAL
CUSTOMER 1ITEM 3CUSTOMER SUBGROUP 3DECEMBER - 2017OPTIONAL

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
251
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

New Member
Joined
Dec 15, 2017
Messages
14
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

Active Member
Joined
Apr 30, 2014
Messages
251
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

New Member
Joined
Dec 15, 2017
Messages
14
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

Active Member
Joined
Apr 30, 2014
Messages
251
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.
 

Forum statistics

Threads
1,082,017
Messages
5,362,695
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top