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?
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.