I'm new to cube functions but they've been incredibly helpful so far in a recent project. I'm currently trying to extract an ordered list of every unique integer value in the field "CYQ" from Excel's data model. Everything I try returns a blank list.

My intended formula was: =CUBESET("ThisWorkbookDataModel", "[IND_TABLE].[CYQ].children","ALL",1,"[IND_TABLE].[CYQ]")

which I believed would pull the field CYQ from the table IND_TABLE, the only only table in my model, and sort it by CYQ. Currently it only returns the word "ALL" in the cell and nothing else.

Any insight or advice would be great, thanks you. Funny enough, searching online has only brought up help for constructing more complicated cubeset formulas, but I can't find help for one this simple.

CodePudding user response：

CUBESET on its own does not return the values to the worksheet; for that, you need to query the return from CUBESET using CUBERANKEDMEMBER. Properly coerced, you can return an array of values with a single formula, for example:

=LET(ζ,CUBESET("ThisWorkbookDataModel","[IND_TABLE].[CYQ].Children"),CUBERANKEDMEMBER("ThisWorkbookDataModel",ζ,SEQUENCE(CUBESETCOUNT(ζ))))

Edit: I see that you've tagged Excel 2010, in which case you won't be able to return all values using a single formula; instead, you'll need something like:

=IF(ROWS(A$1:A1)>CUBESETCOUNT(CUBESET("ThisWorkbookDataModel","[IND_TABLE].[CYQ].Children")),"",CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","[IND_TABLE].[CYQ].Children"),ROWS(A$1:A1)))

and then copied down until you start to get blanks for the results.