IN clause – MDX

SELECT * FROM table1 WHERE code IN (subquery)

How are we going to achieve this sort of query in mdx ? I faced scenario like this where mdx subquery couldn’t be much helpful.

EXISTS

That’s right. Exists is the function that we are going to use here.

SET [AgentCodes] AS
EXISTS([Agent].[AgentKey].Members,
[Agent][AgentCode].Members
)

Simply this helps a lot where there are duplicate agent codes and we want to find each code’s key member.  we can input filtered set into second parameter of EXISTS function to retrieve all the key members.

If I input set of {A001,A002,A003} then I will get 100,101,102,103,104,105 set as the result according to following data set.

Keys	Codes
100	A001
101	A001
102	A002
103	A002
104	A002
105	A003