FILTER by Value with nested rows
Posted: 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:
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!
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)
- 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!