Forum Discussion
Formula Cubeset values range with filter on secondary argument
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.
Thanks for the links (I had them already). I did understand the coercition to String by default, hence why the following does it
....Currentmember.Properties('Key0') = '9999'On the other hand I was wondering if I misunderstood something re. "strongly typed" as the following does it as well (in our context):
...Currentmember.Properties('Key0', TYPED) = 9999Anyway, let's see what Steven10970 has to say...
