Forum Discussion
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 shop so i can use the cubeset in subsequent formula's.
I tried with below formula but it didn't work so far.
Can someone provide feedback where the problem lies?
Remarks:
- I live in the Benelux so arguments are seperated by ";" instead of "," .
- Site N° 9999 surely exists.
=CUBESET("ThisWorkbookDataModel";"[Fac_Site_Open_Hours].[Calendar ID].[All].children,[Fac_Site_Open_Hours].[Site].&[9999]";"Cubeset Calendar ID")
Many thanks.
Wkr.
12 Replies
As variant, based on Lorenzo sample,
filtered set could be
=CUBESET( "ThisWorkbookDataModel", "( [Fac_Site_Open_Hours].[Calendar ID].[All].children, FILTER( [Fac_Site_Open_Hours].[Site].[All].children, [Fac_Site_Open_Hours].[Site].Currentmember.Properties('Key0', TYPED) ='9999' ) )", "Filtered Set")For counting (table on the right)
=CUBESETCOUNT( CUBESET( "ThisWorkbookDataModel", "( [Fac_Site_Open_Hours].[Calendar ID].children, FILTER( [Fac_Site_Open_Hours].[Site].children, [Fac_Site_Open_Hours].[Site].Currentmember.Properties('Key0', TYPED) =" & "'" & [@Site] & "' ) )" ) )I was reading and reading to get the FILTER option in place but for sure that wouldn't have been for today so Thank You for posting it
Quick question re. formula in (your) D6:
.... Currentmember.Properties('Key0', TYPED) ='9999' ...Is there a good reason for which you added quotes around 9999 or did I misunderstood what they mean with strongly typed?
I'm not deep in MDX.
Quotes since value returned by Member_Expression.Properties(Property_Name [, TYPED]) by default is coerced to be a string. Thus we compare with string. Properties (MDX) - SQL Server | Microsoft Docs
[, TYPED] could be missed in our case
....Currentmember.Properties('Key0') = '9999' )works as well.
Bit more about properties at Intrinsic Member Properties (MDX) | Microsoft Docs and around.
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...
Hi Steven10970
from the distance it's difficult to see if the formula is correct or not, because we do not know your datamodel / tables.
But just keep in mind, that the CUBESET function itself does not provide any visible results. In order to see all the individual members of the cubeset (e.g. calendar id's), you would need to use also the CUBERANKEDMEMBER and CUBEVALUE functions.
Let's assume you have your CUBESET function in cell A1. Then this will give the first member:
=CUBERANKEDMEMBER("ThisWorkbookDataModel";$A$1;1)
