MDX Filter by Cube Value

[dharav9]

Hi,

I have placed this statement in subset editor expression window. It saves but did not bring any values.
It should bring two elements from Employee dimension based on FTE value in the cube.

Where Measures_Plan_Employee dimension have both string and numeric elements in the dimension but FTE is numeric element.

{FILTER({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[Employee].[All Existing Employees]},ALL,RECURSIVE)}, 0)}, [Plan_Employee].([Category].[Fcst],[Measures_Plan_Employee].[FTE]) > 0 )}

Please Advise.

Thank You
Dharav

[declanr]

How many dimensions does the "Plan Employee" cube have?

Your expression accounts for 3 dimensions; Employee, Category, and Measures_Plan_Employee. If there are more dimensions in the cube then it will either be trying to automatically derive an element for those dimensions - it can do this by you having them as title elements but if they are not it will use a default element that may not be what you think of.

Also, is "Fcst" definitely the name of the element in the Category dimension? It sounds more like something that would exist in a version dimension.

[Wim Gielis]

I would write it as:

Code: Select all

Filter( TM1FilterByLevel( Descendants( [Employee].[All Existing Employees] ), 0), [Plan_Employee].([Category].[Fcst], [Measures_Plan_Employee].[FTE]) > 0 )

but the syntax that you have, should be working too.

I would suggest double checking all the components of the formula.

Also, do it step by step by each time adding 1 piece of the MDX. Start from the inside and work towards the outside, by filtering more.
Each time check the outcome, using both Subset editor and cube viewer. It's easy to make a typo or to use an element from a different dimension.

[dharav9]

@wim@declanr:

It is resolved.

@declanr: 100 % on Target. I applied all other dimensionality in filter except Employee dimension and it worked. It seems it takes some elements randomly if we have not spacified.

Thank You Fellas,

Dharav

[Wim Gielis]

It seems it takes some elements randomly if we have not spacified.

No that's not correct. Such a system does not take elements randomly.