MDX WHERE NOT IN() equivalent in many-to-many dimension

Question

Hi,

I have a many-to-many dimension in my cube (next to other regular dimensions). When I want to exclude fact rows in my row count measure, I usually do something like the following in MDX

SELECT [Measures].[Row Count] on 0
FROM cube
WHERE ([dimension].[attribute].Children - [dimension].[attribute].&[value])

This might seem more complicated than needed in this simple example, but in this case the WHERE can grow sometimes, also including UNIONs.

So this works for regular dimensions, but now I have a many-to-many dimension. If I perform the trick above it does not produce the desired result, namely I want to exclude all rows that have that specific attribute in the many-to-many dimension.

Actually it does exactly what the MDX asks, namely count all rows, but ignore the specified attribute. Since a row in the fact table can have multiple attributes in a many-to-many dimension, the row will still be counted.

That's not what I need, I need it to explicitly exclude rows that have that dimension attribute value. Also, I might exclude multiple values. So what I need is something similar to T-SQL's WHERE .. NOT IN (...)

I realize that I can just subtract the resulting values from [attribute].all and [attribute].&[value], but that won't work any more when UNIONing multiple WHERE statements.

Anybody got a good idea on how to solve this?

Thanks in advance,

Delta

Answer 1

Have you tried the EXCEPT command? It's syntax is like the following:

EXCEPT({the set i want}, {a set of members i dont want})