Filter a cube view based on the dimension element

[AskAndAnswer]

Here are the cube's dimensions:
Year
Version
Department
Month (with an input element)
Items (1 to 20)
Measures

Measure dimension has the following elements:
Employee ID
New Employee
Start Date
End Date
Salary

New Employee element is a flag with Yes/No picklist that is loaded into the input field of Month dimension.

Now the issue. When I create a cube view, I have all the measures displayed. The view will be for input month, one year and one version with departments and items as row dimensions and measures as column dimensions. How do I filter this view to see only new employees? In other words, is there a way to filter a view based on the "Yes" value in the New Employee measure?

[gtonkin]

The problem is that you are going to have combinations of Department and Item and by filtering on only item, you will end up with a list of items where and Employee may be marked as New but not for that department. If you are only ever working with one department then the below may work for you:
Try something like this on the Items dimension:

Code: Select all

FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Items] )}, 0)},
[CubeName].([Year].[2017],[Version].[Budget],[Department].CurrentMember,[Month].[#Input],[Measures].[New Employee])="Yes"
)

For Department, you would need to set to the relevant element if CurrentMember in the cube view does not work for your requirements
If you are using this in an active form, you can update the elements by joining the values from your selection criteria within the form. If using in cube views, you may want to create subsets with the relevant elements e.g. Year with a subset of Current Year and one element of 2017 then refer to that in the MDX e.g.
TM1Member([Year].[Current Year].Item(0),0) instead of [Year].[2017]
HTH-have not been able to test the syntax but hopefully the concept works.