# CUBESET Issue



## frederic (Feb 6, 2014)

Hi everybody,

I'm working on a dashboard based on cube formulas and in my work i want to get the values for a specific dimension but filtered with another dimension. I tried a lot of thing with CUBESET combined with FILTER or not but nothing is working.

I'm french and i'm using French version of excel so formulas names are different and syntax too. I've translated my function. (in French version we use ; for separator in functions whereas it's , in English.

My Data are these for Example. 

DataBase : *compta*

*place ___* *work*
CA *___* TP
AM *___* TP
AN *___* TP
VL *___* TP
VR *___* TP
RN *___* TP
NU *___* TP
DO *___* TP
ST *___* TP
PA *___* TP
AN *___* CC
AM *___* CC
AN *___* CC
CF *___* CC
VI *___* CC
RH *___* SE
IN *___* SE
DI *___* SE
FI *___* SE

There is no hierarchy between *place* and *work.*

So i'd like to get a set of data with this critera : all the *place* for work *TP *with a CUBSET formula which will become an entry for CUBERANKEDMEMBER function. In that case the COUNTSET function would return 10, the 10 first lines of my example.

First example with filter function :
=CUBESET("PowerPivot Data","FILTER([compta].[place].[All],[compta].[work]='TP')","Data Set")
A COUNTSET with the cell containing this function returns or 0 (empty or 1 (All) if i change ='TP' by <>'TP'.
It's the same if i change [work]='TP' by [work].[All]='TP'

Second example :
=CUBESET("PowerPivot Data";"[compta].[work].[All].[TP],[compta].[place].Children";"Data Set").
A COUNTSET with the cell containing this function returns #N/A.

I'll be glad if somebody could help me.
Thank's


----------



## frederic (Feb 6, 2014)

Hi,

After lot of time, i have result with this syntax :
=CUBESET("PowerPivot Data","FILTER([compta].[place].Children,[compta].[work]="&Q5&")","Places For TP")
Where Q5="TP". The only problem is that data are not filtered, i have 19 results where i should have only 10.

It's the same result with : =CUBESET("PowerPivot Data","[compta].[place].Children","Places") who have no filter options.

Any idea.

PS : Data are stored in PowerPivot in Excel 2010.


----------



## frederic (Feb 7, 2014)

Hi,

I found the answer which is simple. To solve my filter problem i just have to create a Hierarchy in PowerPivot window view and diagram view.
The hierarchy (Hierarchy) contains First *work* and *place*.

So it's possible to use CUBESET function to get *place* (s) for *work* 'TP' with this syntax :
[FONT=Georgia, Bitstream Charter, serif]*A1=CUBESET("PowerPivot data","[compta].[Hierarchy].[TP].Children,"Data set").
*[/FONT]So if you use CUBESETCOUNT(A1), the result is 10, not 19.


----------

