filtering with set?

i have two dimensions named dimLocation and dimTargetSet. What is needed is to count the members of dimLocation that is also a member of dimTargetSet. I am thinking of using a filter but have no idea what to do.

Count(FILTER(Descendants([dimLocation].currentmember,9,Leaves), ?))

Any ideas?... thank you so much....

Are you saying that both dimensions have the same key values? From your example code, I am guessing that you may be using AS2k (as there are only dimension and no hierarchy references) so I have coded my suggestion below to work on AS2k.

count(filter(descendants([dimLocation].currentmember,,leaves), linkMember([dimLocation].currentmember,[dimTargetSet]) <> NULL))

LinkMember will look for a member with the same key in the other hierarchy.

|||

thanks for your reply... i tried it but its not what i need. uhhmmm... what i need to do is to count the members of the first dimension.

count(Descendants([dimLocation].&[2583],9,LEAVES))

But the problem is when we select a member of the second dimension ([dimTargetset])... the second dimension is about location grouping... so if we select a group for west it should only count the members of [dimLocation] that belongs to that group....

Thank you....

|||

If targetset is a group of locations, why haven't you just modelled it as an attribute (or level in AS2000) of the location hierarch? Unless there is a many-to-many relationship between targetsets and locations, this would probably be the best way to go.

If you model two items as separated dimensions, then they are, by definition, treated as independent entities.

The only other way to do this would be to count a non-empty measure value

count(filter(descendants(dimLocation.Currentmember,,LEAVES),NOT IsEmpty(Measures.[....])))

If you are using AS2k5 you could use the NonEmpty() function which would be faster in most cases than using filtering.

|||

thank you so much for your reply....