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...
Yes, you are right, strongly typed works with numbers, there is no type conversion. On the other hand strongly typed with string
Currentmember.Properties('Key0', TYPED) = '9999'also works.
I don't know what is behind. Long ago had some issues with that, don't remember which exactly. Since with string it always works, at least in my experience, I use apostrophes by default. Something like
Currentmember.Properties('Key0') = '9999'most probably shall work in any case.
MDX is not what I do from day to day, good if touch it once per few months. With that it's bit hard to collect experience. At the same time with modifying in Excel cube connected from Power BI dataset it's new motivation to use MDX.