I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example…
Let's say I have a Student dimension, and in it are two attributes – Major and Minor (subjects).
I want to be able to create a Named Set in SSAS that gives me Students who have somehow been recorded as having the same subject for Major and Minor…
Dimension looks a little like this:
Student
- Major
- Minor
The underlying table looks something like this:
StudentID | MajorKey | MinorKey
1 50 51
2 45 101
3 58 56
4 45 45
So, in effect, I want an MDX expression like this:
FILTER (
[Student].allmembers,
[Student].[Major] = [Student].[Minor]
)
… but I can't quite figure out the syntax. Any ideas?
UPDATE:
If I use this syntax…
FILTER (
[Student].allmembers,
[Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)
Then it slows down the ENTIRE cube for some reason. Every Measure (natural or calculated) takes several minutes as opposed to 1-2 seconds pre-cache.
Best Answer
I got it!
Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...
I know I answered this quick but I've spent the best part of 10 hours on this today :P