Help Needed - MDX Dynamic Subset based on Cubevalues

[HighKeys]

Hello Community,


i got stuck in a mdx statement, first the MDX Code:

Code: Select all

{Filter(
{TM1FILTERBYLEVEL({
DESCENDANTS(Planungseinheit.[Leistungsspektrum]) }, 0)},  
[Mengen-Planung Tag Mandant].([Mengen].CurrentMember, [Kontaktart].CurrentMember, [Version].CurrentMember, [Planungseinheit].CurrentMember, [Mandant].CurrentMember, [Tag].[2022], [Kennzahlen Mengen-Planung Mandant].[LMin]) > 0 
and 
[MA-Pool Zuordnung Menge].([Jahre].[2022], [Monat].CurrentMember, [Version].CurrentMember, [Mengen].CurrentMember, [Kontaktart].CurrentMember, [MA-Pool].[MA-Pool Gesamt], [Planungseinheit].CurrentMember, [MA-Pool Zuordnung].[Zuordnung (%)]) = 0 )
  }

I want to get all "Planungseinheiten" which have "LMin" and "Zuordnung (%)" is zero, it works perfectly with the statement above, just i dont want to hardcode the year 2022, but i'm using diffeerent Time Dimensions in the 2 cubes, i select the Year from "Jahre" but how to assign this value to "Tag" Dimension in the other cube?

"Tag" element should match the Current Member from Year Dimension in the Cube.

Thanks for your help!
BR

[declanr]

Is the Jahre dimension in the title element of the cube that you are using this subset in?
If so you can use CurrentMember to filter the Jahre dimension.
And then you can use StrToMember combined with the intrinsic properties to find it in the Tag dimension also.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL({
DESCENDANTS(Planungseinheit.[Leistungsspektrum]) }, 0)},  
[Mengen-Planung Tag Mandant].([Mengen].CurrentMember, [Kontaktart].CurrentMember, [Version].CurrentMember, [Planungseinheit].CurrentMember, [Mandant].CurrentMember, StrToMember ( "[Tag].[" + [Jahre].CurrentMember.Properties("MEMBER_NAME") + "]" ), [Kennzahlen Mengen-Planung Mandant].[LMin]) > 0 
and 
[MA-Pool Zuordnung Menge].([Jahre].CurrentMember, [Monat].CurrentMember, [Version].CurrentMember, [Mengen].CurrentMember, [Kontaktart].CurrentMember, [MA-Pool].[MA-Pool Gesamt], [Planungseinheit].CurrentMember, [MA-Pool Zuordnung].[Zuordnung (%)]) = 0 )
  }

Based on the fact that you have used CurrentMember in other places, I am guessing that neither of the Jahre or Tag dimensions exist in the cube that you are using this subset in.
So you need to then utilise the MDX concepts above (e.g. StrToMember) but combine it with holding the Year value somewhere else.
For example a control cube could hold the year and then you can just retrieve the year e.g. 2022 from there.

[paulsimon]

Hi BR

Another approach that I have used is to add an attribute to the dimension Jahre eg Selected Year

You can then put a rule into the }ElementAttributes_Jahre cube which sets the Attribute to Y based on some condition eg the equivalent value in the Tag dimension.

Then in your MDX you can just record a Select by Attribute value on the Jahre dimension where the Attribute is Y

The basic advantage is that writing rules is often simpler than writing MDX.

If you actually want your statement to run over multiple years, and want to match Jahre and Tag then you can again do this via an Attribute on Jahre that gives the equivalent Tag, and vice-versa depending on requirements. I am assuming that the two formats are different.

Regards

Paul

[HighKeys]

Is the Jahre dimension in the title element of the cube that you are using this subset in?
If so you can use CurrentMember to filter the Jahre dimension.
And then you can use StrToMember combined with the intrinsic properties to find it in the Tag dimension also.

Code: Select all

{Filter(
{TM1FILTERBYLEVEL({
DESCENDANTS(Planungseinheit.[Leistungsspektrum]) }, 0)},  
[Mengen-Planung Tag Mandant].([Mengen].CurrentMember, [Kontaktart].CurrentMember, [Version].CurrentMember, [Planungseinheit].CurrentMember, [Mandant].CurrentMember, StrToMember ( "[Tag].[" + [Jahre].CurrentMember.Properties("MEMBER_NAME") + "]" ), [Kennzahlen Mengen-Planung Mandant].[LMin]) > 0 
and 
[MA-Pool Zuordnung Menge].([Jahre].CurrentMember, [Monat].CurrentMember, [Version].CurrentMember, [Mengen].CurrentMember, [Kontaktart].CurrentMember, [MA-Pool].[MA-Pool Gesamt], [Planungseinheit].CurrentMember, [MA-Pool Zuordnung].[Zuordnung (%)]) = 0 )
  }

Based on the fact that you have used CurrentMember in other places, I am guessing that neither of the Jahre or Tag dimensions exist in the cube that you are using this subset in.
So you need to then utilise the MDX concepts above (e.g. StrToMember) but combine it with holding the Year value somewhere else.
For example a control cube could hold the year and then you can just retrieve the year e.g. 2022 from there.

Hello,

thank you so much, this is exactly what i searched for! Yes "Jahre" Dimension is in the Cube where i use the Subset.

StrToMember is really a nice funciton good to know how it works!

I wish you a nice weekend!

BR