Forum Discussion

Steven10970's avatar
Steven10970
Copper Contributor
Aug 10, 2022

Formula Cubeset values range with filter on secondary argument

Hello,   I try to create a cubeset that selectes all "calendar ID" values if another criteria, site, in the same pivot table is a fixed value. Thus i want to filter the calendar ID's for a fixed sh...
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Aug 11, 2022

Hi Steven10970 

1st time ever I touch CUBESET and the like so no guaranty at all & don't expect too much from me...

 

Loaded the following Fac_Site_Open_Hours table to the Data Model

CUBESET doesn't return anything on a sheet so nested the CUBESET formulas in CUBESETCOUNT to check the results

 

In E2 (no filtering at all, just to check I get 13 Child):

=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel", "[Fac_Site_Open_Hours].[Calendar ID].[All].Children"))

E3:

=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel", "EXISTS([Fac_Site_Open_Hours].[Calendar ID].[All].Children, [Fac_Site_Open_Hours].[Site].[All].[9999])", "Cubeset Calendar ID"))

cf. MDX https://docs.microsoft.com/en-us/sql/mdx/exists-mdx?view=sql-server-ver16 function

 

Hope this helps & more importantly does what you expect...

Resources