Sql-server – Need to filter dimension where two attributes are the same

mdxsql-server-2008ssas

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

FILTER (
  CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

I know I answered this quick but I've spent the best part of 10 hours on this today :P