MDX Filter expression understanding

Question

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?

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, Jul 20 '15 at 12:44

score 5 · Accepted Answer · answered Jul 20 '15 at 10:43

5

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]

answered Jul 20 '15 at 10:43

mxix

3,539
1
16
23

Thanks for the answer mxix. Just one more follow-up question, is it not possible to obtain this without the use of sub cube query? – VKarthik Jul 20 '15 at 11:13
Your use of sub select is intriguing :) – SouravA Jul 21 '15 at 04:47
@SouravA I agree - this is an interesting approach - does exactly as required and I imagine very quickly. – whytheq Jul 21 '15 at 10:43
Thank you. Still learning MDX,, don't know if there is an impact in performance due to the use of subselect. I find this to be a good aproach the understand the how the axis can be filtered (still trying to jump from sql mindset to mdx mindset). Without the use of subcube i guess @whytheq 's answer is a good way to do it. Has my +1. – mxix Jul 21 '15 at 10:51
sub-selects straight away decrease the size of the cube space: it should be quick. MS use sub-selects all over the place in their default code....so they must do something right! – whytheq Jul 21 '15 at 11:00

whytheq · Answer 2 · 2015-07-20T12:55:32.787

4

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

edited Jul 20 '15 at 12:55

answered Jul 20 '15 at 11:23

whytheq

34,466
65
172
267

Thanks whytheq for the reply. I have just started to hone my learning path with MDX by reading and practicing the code in the book MDX Solutions (Wiley Publication). In one example dealing with creation of sub cubes, the author cites an example of inner SELECT with FILTER and outer SELECT taking the results from it, which is what I am trying to reproduce. Now inner select as a whole was giving not giving me correct result, hence creating confusion. So I was looking to understand why is FILTER not working properly within the inner query..cntd.. – VKarthik Jul 21 '15 at 04:18
I now understand that for the filter to work, the whole set i.e. cross section of dates and the Sales Territory members need to be present which kind of makes sense. I have one question with regard to your reply. Performance wise, would the usage of MEMBER funciton and WHERE clause be effective? This looks very easy to understand and implement. – VKarthik Jul 21 '15 at 04:22
Good attempt @whytheq – SouravA Jul 21 '15 at 04:44
@VKarthik your suggestion of using filter in the WHERE clause - try it out for yourself. Take the sub-select of the answer and move it to the where clause. It won't work because you are not then allowed the same hierarchies on rows or columns. It is a common problem of needing to filter on the hierarchies which you also want to present - sub-select is a common approach. – whytheq Jul 21 '15 at 21:38

MDX Filter expression understanding

2 Answers2