Can't get filter to work with multiple conditions

filter, mdx, ssas, ssms

Solution

Reworking your query I end up with this, let me know how it goes:

SELECT NON EMPTY 
{
     [Measures].[Score %]
    ,[Measures].[Month Key]
} ON COLUMNS,
NON EMPTY 
{
    ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307])
    * 
    FILTER
    (
        [Customer].[Customer Full Name].[Customer Full Name].members,
        SUM({[Date].[YMD].CURRENTMEMBER}, [Measures].[Score %])<> null
        AND 
        SUM({[Date].[YMD].CURRENTMEMBER}, [Measures].[Score %])<> 0
    )
} ON ROWS
FROM [Cube]

Or an even simpler version might work:

SELECT NON EMPTY 
{
     [Measures].[Score %]
    ,[Measures].[Month Key]
} ON COLUMNS,
NON EMPTY 
{
    ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307])
    * 
    FILTER
    (
        [Customer].[Customer Full Name].[Customer Full Name].members,
        [Measures].[Score %] <> null
        AND 
        [Measures].[Score %] <> 0
    )
} ON ROWS
FROM [Cube]

Edit: Third attempt, let's see if this does the trick:

SELECT NON EMPTY 
{
     [Measures].[Score %]
    ,[Measures].[Month Key]
} ON COLUMNS,
NON EMPTY 
{
    ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307])
    * 
    [Customer].[Customer Full Name].[Customer Full Name].members
} 
HAVING  [Measures].[Score %] <> null
AND [Measures].[Score %] <> 0
ON ROWS
FROM [Cube]

Problem

I'm trying to make a MDX query for use with SSRS later on. To start with the basic query that works: ``` Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301]) * FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], [Date].[YMD].&[201301]) <> null AND ([Measures].[Score %], [Date].[YMD].&[201301]) <> 0 ) } on Rows from [Cube] ``` But this is only for one month. in SSRS i want to be able to select multiple months, so i altered my query to this: ``` Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], ([Date].[YMD].&[201301] : [Date].[YMD].&[201307]))<> null AND ([Measures].[Score %], ([Date].[YMD].&[201301] : [Date].[YMD].&[201307])) <> 0 ) } on Rows from [Cube] ``` But here i get an error: The <> function expects a string or numeric expression for the 1 argument. A tuple set expression was used. As far as i understand it is because i'm returning multiple months in my set where it expects one month. So i write the next query: ``` With Set [QC relation] as FILTER([Customer].[Customer Full Name].[Customer Full Name].members, ([Measures].[Score %], [Date].[YMD].currentmember) <> null AND ([Measures].[Score %], [Date].[YMD].currentmember) <> 0 ) Select NON EMPTY { [Measures].[Score %] ,[Measures].[Month Key] } on Columns, NON EMPTY { ([Date].[YMD].[Month Name].&[201301] : [Date].[YMD].[Month Name].&[201307]) * [QC relation] } on Rows from [Cube] ``` But here it seems my filter doesn't work properly. It is returning all the rows with a score in the data for each month so i have a lot of null values. How do i get the proper results for each month without the null values? I'm working with SSMS/SSAS on SQLServer2008 Thanks

Original source