CUBESET Issue

[frederic]

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]

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]

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.