What is MDX "not in" SQL's analog for where clause?

user_1154283 0
Viewed 632

In SQL I can write this:

SELECT some_column_1, some_column_2 FROM some_table WHERE some_column_3 NOT IN ("blah", "bleh", "meh");

How to do the same in MDX? Notice that some_column_3 is not selected, but acts as filter. I know about EXCEPT thing in MDX, but I can use it only for something that selected on axes. How to put it in WHERE clause? Or how to get same result with something else?

For example, I need something like this:

SELECT some_measure ON COLUMNS FROM [Model] WHERE some_hierarchy NOT IN ("blah", "bleh", "meh");
mdx ssas
2 Answers

You can use the Except function or its abbreviation - (minus sign) like this:

SELECT [Measures].[some_measure] ON COLUMNS 
FROM [Model] 
WHERE {-{[Some Dimension].[some_hierarchy].[blah], [Some Dimension].[some_hierarchy].[bleh], [Some Dimension].[some_hierarchy].[meh]}}
user_50704400

IN MDX if you want all members of a dimension attribute except any particular member you use the following syntax

Dim1.attrubute1.children - Dim1.attrubute1."member value you dont want"

So your query gets transformed to

select ([measures].[column1]) on columns, ( [dim1].[column2].children ) where ([dim1].[column2].children - {[dim1].[column2].[blah],[dim1].[column2].[bleh],[dim1].[column2].[meh]})

user_97000350
Related