CODEWITHSUNDEEP
ssasmdx
Peter
Peter

Reputation: 133

filter in a mdx query is not correctly applied

I have a MDX Filter problem, which I think I use correctly however I still get some rows which I don't want to get. Sample query is :

SELECT {[Measures].[AFR],[Measures].[IB],[Measures].[IC All]} ON COLUMNS,
NON EMPTY ( ([dim_ProductModel].[Product Model].&[DSDB]) 
* FILTER(  ([dim_Country].[Country Name].members -[dim_Name].[Country Key].[All]),[Dim_Date].[Date Full].&[2014-01-01]*[Measures].[IB] > 0 AND NOT ISEMPTY ([Dim_Date].[Date Full].&[2014-01-01]*[Measures].[IB] )) 
* {[Dim_Date].[Date Full].&[2013-02-01]:[Dim_Date].[Date Full].&[2014-01-01]}) 
ON ROWS FROM [cub_dashboard_spares]

Now what I need, is to exclude those countries! (Filter) for particular product model [DSDB] in this case , where in january 2014 (dimension) the measure IB was > 0 or not null. Now it seems that it filters out correctly some countries however I still get some results, where either IB is 0 in last month OR IB is (null) in last month (January 2014 in our case).

Could please anybody help me where can be the problem?

Thank you very much

Upvotes: 0

Views: 944

Answers (2)

Peter
Peter

Reputation: 133

this is my problem..: don't understand why the results with AFR 0 appear :(

i don't get it..

Upvotes: 0

FrankPl
FrankPl

Reputation: 13315

I would add the country to the filter condition, i. e. use

SELECT {[Measures].[AFR],[Measures].[IB],[Measures].[IC All]}
       ON COLUMNS,
       NON EMPTY
       [dim_ProductModel].[Product Model].&[DSDB]
       * FILTER(([dim_Country].[Country Name].members -[dim_Name].[Country Key].[All]),
                ([dim_Country].[Country Name].CurrentMember, [Dim_Date].[Date Full].&[2014-01-01], [Measures].[IB]) > 0
                AND NOT
                ISEMPTY(([dim_Country].[Country Name].CurrentMember, [Dim_Date].[Date Full].&[2014-01-01], [Measures].[IB]))
               ) 
       * {[Dim_Date].[Date Full].&[2013-02-01]:[Dim_Date].[Date Full].&[2014-01-01]}
       ON ROWS
  FROM [cub_dashboard_spares]

Upvotes: 1

Related Questions