Formula Cubeset values range with filter on secondary argument

[Steven10970]

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.

[#2]

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)

[#3]

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 Exists function

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

[#4]

@Steven10970 

As variant, based on @L z.  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] & "' )
       )"
) )

[#5]

@Sergei Baklan 

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?

[#6]

@L z. 

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.

[#7]

@Sergei Baklan 

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) = 9999 

Anyway, let's see what @Steven10970 has to say...

[#8]

@L z. 

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.

[#9]

@Sergei Baklan 

I had a similar challenge some time ago with authored SQL queries (from PQ) and the quotes were the solution. So, as you say, it's probably safe to use them in any case

Thanks again

[#10]

@Sergei Baklan 

Working on something +/- close I remembered this old case. The FILTER option is close to yours:

[#11]

@L z. 

Nice sample, thank you for sharing. Never used CROSSJOIN.

[#12]

@Sergei Baklan 

Never used CROSSJOIN You actually did

This should be enough to close the subject

[#13]

@L z. 

Oops. Yes, actually. Thank you.