FILTER by Value with nested rows

Post Reply
mvaspal
Community Contributor
Posts: 330
Joined: Wed Nov 03, 2010 9:16 pm
OLAP Product: tm1
Version: 10 2 2 - 2.0.5
Excel Version: From 2007 to 2013
Location: Earth

FILTER by Value with nested rows

Post by mvaspal » Wed Mar 04, 2020 12:36 pm

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!

Post Reply