MDX Filter expression understanding

3
votes

Am having trouble in understanding how FILTER function works in MDX.

Here is my query which gets all the non-empty Internet Order Count values for all Sales Territory Countries across all the Calendar Years.

    SELECT 
    NON EMPTY 
    {
        Filter
        (
        {[Date].[Calendar].[Calendar Year].MEMBERS}
        ,
        [Measures].[Internet Order Count] > 0
        )
    } ON COLUMNS
    ,[Sales Territory].[Sales Territory].[Country].MEMBERS
ON ROWS
FROM [Adventure Works]
WHERE 
    [Measures].[Internet Order Count];

This gives me all the orders as I am filtering by 0 and the result is as shown below

                CY 2010,CY 2011,CY 2012,CY 2013,CY 2014
France,         1      ,140    ,359    ,"1,917",67
Germany,               ,175    ,339    ,"1,909",61
United Kingdom ,1      ,175    ,405    ,"2,377",73
Canada,         1      ,170    ,169    ,"2,856",179
United States,  5      ,770    ,867    ,"7,590",335
Australia,      6      ,786    ,"1,130","4,640",156

Am putting in the results in csv format as am having restrictions in uploading image currently. Am new to MDX and my goal is to filter this result set where Internet Order Count is greater than 180.

Hence I now modified the query to this -

SELECT 
  NON EMPTY 
    {
      Filter
      (
        {[Date].[Calendar].[Calendar Year].MEMBERS}
       ,
        [Measures].[Internet Order Count] > 180
      )
    } ON COLUMNS
 ,[Sales Territory].[Sales Territory].[Country].MEMBERS
ON ROWS
FROM [Adventure Works]
WHERE 
  [Measures].[Internet Order Count];

This one gave me the below output -

                CY 2011,CY 2012,CY 2013,CY 2014
France,         140    ,359    ,"1,917",67
Germany,        175    ,339    ,"1,909",61
United Kingdom ,175    ,405    ,"2,377",73
Canada,         170    ,169    ,"2,856",179
United States,  770    ,867    ,"7,590",335
Australia,      786    ,"1,130","4,640",156

Basically CY 2010 entries are filtered out whereas what I am expecting is not to have entry/blank values of (France,CY 2011),(Germany, CY 2011),(UK, CY 2011), (Canada, CY 2011) etc. and of course similarily for some entries belonging to CY 2014 with similar result.

I am using AdventureWorksDW2014 cube. How would I go about doing this?

2
sql-serverssasmdxadventureworks
you seem to have prematurely marked an answer correct - does that answer actually answer your question? I will add my interpretation of your question to my answer. – whytheq
Just edited my answer now - hope that is a help. – whytheq

2 Answers

3
votes

You are only filtering Years with Internet Order Count over 180, not the Country Dimension. try this: 

SELECT 
    NON EMPTY [Date].[Calendar].[Calendar Year].MEMBERS ON COLUMNS,
    [Sales Territory].[Sales Territory].[Country].MEMBERS ON ROWS
FROM (
    SELECT
        Filter (
            (
                [Date].[Calendar].[Calendar Year].MEMBERS , 
                [Sales Territory].[Sales Territory].[Country].MEMBERS
            ),
            [Measures].[Internet Order Count] > 180
        ) ON 0
    FROM [Adventure Works]
)
WHERE 
    [Measures].[Internet Order Count]
3
votes

Your very first script is actually just the following. No need for any filter as it is a count so <0 will never happen:

SELECT 
  NON EMPTY 
    [Date].[Calendar].[Calendar Year].MEMBERS ON 0
 ,[Sales Territory].[Sales Territory].[Country].MEMBERS ON 1
FROM [Adventure Works]
WHERE 
  [Measures].[Internet Order Count];

In my old version of AdvWrks this results in the following:

enter image description here

AI actually think that you are more after the following:

WITH 
  MEMBER [Measures].[transformToNull] AS 
    IIF
    (
      [Measures].[Internet Order Count] <= 180
     ,null
     ,[Measures].[Internet Order Count]
    ) 
SELECT 
  NON EMPTY 
    {[Date].[Calendar].[Calendar Year].MEMBERS} ON COLUMNS
 ,[Sales Territory].[Sales Territory].[Country].MEMBERS ON ROWS
FROM [Adventure Works]
WHERE 
  [Measures].[transformToNull];

Results in the following:

enter image description here