FILTER by Value with nested rows

[mvaspal]

Hello,

I have cube with versions, time, stores, departments, and metrics dimensions.
I am trying to use a dynamic set on the rows to show only those department/store combinations where the margin 5 (shown on the column) is less than 5%. So departments and stores should be nested

Simple MDX would look like:

Code: Select all

FILTER({TM1FILTERBYLEVEL(TM1SUBSETALL([Departments_PLAN].[Departments_PLAN]) , 0)},
[PLANCube].([SalesLines].[Margin%])<0.05)

And a similar MDX could work for Stores. This MDX works fine as long as there is only one dimension (i.e. Departments) on the rows, but once I nest stores and departments, it does not work; i.e. for departments, the condition is being checked against Total Stores, even if the nested Stores dimension shows the leaves only. I tried adding the CurrentMember to the MDX which did not help (as expected...). I also tried all possible combinations:
- both dimension use an MDX filter
- only the outer dim, and then only the inner dim uses the filter

Any idea how is it possible to write a cube-value based FILTER condition in an MDX statement that would work with nested dimensions?

Thank you for your help!