When it’s coming to parent child hierarchies in SSAS dimensions, we can’t filter those as same as other hierarchies. Well, I’m not going to write about parent-child hierarchies here. But in short, we have to create parent-child hierarchies for self referencing fields in tables. Such as employee table, where employees also behave as managers.
So there was a situation where I wanted to filter all the parents using one attribute in the same dimension without affecting to the children. I had agent table with agent parent key and agent key and agent designation(senior manager, manager, associate manager). Simply, I wanted to filter all the parent agents who are “senior managers” and their children. If we filter using designation code as we do normally, we won’t get the correct set of members. Definitely I had to use DESCENDANTS function to get all the children in mdx and before use that I had to filter relevant parents.
this is the normal DESCENDANTS function ,
Descendants(
{[Agents].[Agent Parent].Members},,SELF)
and with FILTER function,
Descendants(
Filter([Agents].[Agent Parent].Members ,[Agents].[Agent Parent].CurrentMember.Propoerties(“Agent Designation”)=”Senior manager”) ,,SELF )
Self in the above example implies that I want only parent members in the result set.
Now this set can be used to retrieve a relevant measure like follows.
With Set FilteredAgents AS (
Descendants(
Filter([Agents].[Agent Parent].Members ,[Agents].[Agent Parent].CurrentMember.Propoerties(“Agent Designation”)=”Senior manager”) ,,SELF)
)
Select [Measure] ON 0,
FilteredAgents ON 1
FROM [Cube]
Anushka's Tech Notes 