MDX Dynamic Subset with Value from other Cube

Post Reply
HighKeys
Posts: 102
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

MDX Dynamic Subset with Value from other Cube

Post by HighKeys » Tue Feb 16, 2021 4:46 pm

Hi Mates,

i tried to make a new dynamic subset based on my very low MDX skills and figured out a problem, may someone can help me out.

Code: Select all

{
	Filter({
		[GuV Gliederung].members
	},[GuV Gliederung Attribute].([Jahre]. [2021], [Version].[Version].[Prognose 4],[GuV Gliederung Attribute].[Köpfe])>1)
}
i Filter the Dimension "GuV Gliederung" and look in the Cube "GuV Gliederung Attribute" for the "köpfe" measure if its greater then 1 add the element so the subset.. So this works fine, but i want that the subset is Dynamic according to the Version and Year (Jahre) selected in the Cube.

I tried this with [Jahre].CurrentMember , but it doesnt work, i think cause its "just a subset" and it cant get the selected value from the cube viewer.

Is there any Workaround for this?

Thanks and BR

declanr
MVP
Posts: 1696
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: MDX Dynamic Subset with Value from other Cube

Post by declanr » Tue Feb 16, 2021 4:53 pm

The CurrentMember method as you have suggested should work but you need those dimensions to be in the Title Elements/Context area, it won't work if you have them as rows or columns.
Declan Rodger

HighKeys
Posts: 102
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: MDX Dynamic Subset with Value from other Cube

Post by HighKeys » Tue Feb 16, 2021 5:09 pm

Oh nice i see if i put them up and reload it loads the subset, thank you!

But one other thing

it just shows me everything above the 0 Level but i just want to see the 0 level elements.

Code: Select all


{
	Filter({
	TM1FILTERBYLEVEL({	[GuV Gliederung].[GuV Gliederung].Members}, 0)
	},[GuV Gliederung Attribute].([Jahre].CurrentMember, [Version].[Version].CurrentMember,[GuV Gliederung Attribute].[Köpfe])>1)
}

i tried to add afilter but this doesnt solve it, also my drilldown doesnt made it.

Would be very nice from you if you could help me again :)

Thanks for it!

User avatar
PavoGa
Community Contributor
Posts: 490
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: MDX Dynamic Subset with Value from other Cube

Post by PavoGa » Tue Feb 16, 2021 6:21 pm

Try this:

Code: Select all

Filter(
    TM1FILTERBYLEVEL(	[GuV Gliederung].Members, 0),
    [GuV Gliederung Attribute].([Jahre].CurrentMember, [Version].CurrentMember, [GuV Gliederung].currentmember, [GuV Gliederung Attribute].[Köpfe]) > 1)
    
The difference is this is telling the filter condition to explicitly use the current member of [GuV Gliederung].

NOTE: if you have leaf elements that are under multiple consolidations, .Members will return every occurrence of the members. TM1SUBSETALL( [dimname]) will return just the unique members.
Ty
Cleveland, TN

HighKeys
Posts: 102
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: MDX Dynamic Subset with Value from other Cube

Post by HighKeys » Tue Feb 16, 2021 8:05 pm

Hi,

Edit:

Worked, i missed that the 1 Values in this cube has a weighting so i have to change the ">1" to a "=0" and your statement worked perfectly

Thank you so much for your help!

Post Reply