MDX Filter doesn't see value Calculation by CalculatedCount

[EP_explorer]

I have a dimension and some elements in it have 0 weight
But I have to use Value of a Cube with the dimension in MDX Filter
Filter(dim_for_filter,Cube(...,[dimension].[sum_element consolidated elements with 0 weight])>0)

As I receive 0 on element [sum_element consolidated elements with 0 weight] I decided to calculate it by ConsolidatedCount. I did it, and in Architect I see nonzero but Filter anyway consider that [sum_element consolidated elements with 0 weight] contains 0.

Is it normal behaviour of Filter?
MDX see only normally calculated values (by hierarchy), but doesn't see Values calculated by Rules - using ConsolidatedCount.

[MarenC]

Hi,

cant you do something like:

Code: Select all

FILTER(TM1SubsetAll([Dimension]), [dimension].currentmember.properties("MEMBER_WEIGHT")="0.000000" and [Cube].([Dim1].[Element],[Dim2].[element])>0)

That way you might not need the "sum_element consolidated elements with 0 weight" and just look at the value element.

If the above isn't relevant, then I would personally need more information than has been provided.

Maren

[EP_explorer]

Yes. You are right. Without example it is difficult to understand the problem
So I have a dimension with zeroweight elements (left on picture). Cube in which I have flags (in the middle) and MDX which have to filter CFR dimension based on Sum element (90000000000). As 90000000000 contains 0 so filter return nothing (right)

01.jpg (97.65 KiB) Viewed 706 times

After it I add in Cube Rule whcih calculate 90000000000 using ConsolidatedCount. And filter starts working (actully I made mistake early) but I found another problem - on real volumes it works slowly than simple consolidation.

03.jpg (67.83 KiB) Viewed 697 times

I think of create new dimension which contains only 90000000000 and details elements (based on Account dimension). It works faster but less convinient (I have to mantain 2 dimension - Account with zero weightd elements and new - Account_details).
May be anybody have another ideas?

[PavoGa]

So what is desired is to return a set of the top level elements where at least one leaf element is populated with a flag value in the cube?

Instead of another dimension, consider adding an alternate hierarchy with the only components of the top level consolidation are the leaf elements:

--90000000000
903001
903002
903003

The leaf elements would be weighted to 1 so that alternate consolidation has the total of the flags.

The MDX for retrieving those would be:

Code: Select all

GENERATE(  
	FILTER( 
		FILTER( TM1SUBSETALL( [dimname].[alt hierarchy] ),  
			[dimname].[alt hierarchy].currentmember.Properties("ELEMENT_LEVEL") = "level number"),
   		cube.(filter conditions) ),
   {STRTOMEMBER( "[dimname].[dimname].[" + [dimname].[alt hierarchy].currentmember.name + "]")})