Knowledge of MDX Filter Expressions
I am having trouble understanding how the FILTER function works in MDX.
Here's a query of mine that gets all non-empty internet address counts for all sales territories for all 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 when I filter for 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
I am putting the results in csv format as I have limitations when uploading an image at the moment. I am new to MDX and my goal is to filter this result set where the number of internet orders is greater than 180.
Hence, now I have changed the request 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 gave me the following result -
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 records are filtered out and what I expect should not have input / null values (France, CY 2011), (Germany, CY 2011), (UK, CY 2011), (Canada, CY 2011), etc. .d. and of course similar for some records belonging to CY 2014 with a similar result.
I am using AdventureWorksDW2014 cube. How can i do this?
source to share
You are only filtering out Years with Internet Order Counts greater than 180, not 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]
source to share
Your first script is actually the following. There is no need for any filter as it is a counter, 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, AdvWrks
this results in the following:
AI actually thinks you are bigger 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];
The results are as follows:
source to share