How to filter by measure values in MDX while having dimension members in both axis

Question

I'm developing an application that uses a tabular database to show some business data.

I need to provide some basic filtering over measures values (equal to, greater than, lesser than etc.) and I'm currently analyzing the proper way to generate the MDX.

Looking at some documentation (and other threads on this site), I found that the most efficient approach would be using the FILTER or HAVING functions to filter out undesired values.

Unfortunately all examples normally include measures on one axis and dimension member on the other, but I potentially have dimension members in both axis and can't find a proper solution to use such functions to filter by measure value.

What have I done so far?

To make it easier to explain, let's say that we want to get the yearly sales quantities by product class filtering quantity > 1.3 milions

Trying to use HAVING or FILTER Functions, the resulting MDX I came up with is

SELECT 
NON EMPTY {[YearList].[Year].[Year].MEMBERS * [Measures].[Qty]} 
    HAVING [Measures].[Qty] > 1.3e6 ON COLUMNS,
NON EMPTY {[Classes].[cClass].[cClass].MEMBERS} 
    HAVING [Measures].[Qty] > 1.3e6 ON ROWS
FROM [Model]

or

SELECT 
NON EMPTY FILTER({[YearList].[Year].[Year].MEMBERS * [Measures].[Qty]}, 
    [Measures].[Qty] > 1.3e6) ON COLUMNS,
NON EMPTY FILTER({[Classes].[cClass].[cClass].MEMBERS} , 
    [Measures].[Qty] > 1.3e6) ON ROWS
FROM [Model]

But this is of course leading to unexpected result for the final user because the filter is happening on the aggregation of the quantities by the dimension on that axis only, which is greater then 1.3M

Wrong Result

The only way I found so far to achieve what I need is to define a custom member with an IIF statement

WITH
    MEMBER [Measures].[FilteredQty] AS 
    IIF ( [Measures].[Qty] > 1.3e6, [Measures].[Qty], NULL)

SELECT 
    NON EMPTY {[YearList].[Year].[Year].MEMBERS * [Measures].[FilteredQty]} ON COLUMNS,
    NON EMPTY {[Classes].[cClass].[cClass].MEMBERS} ON ROWS
FROM [Model]

The result is the one expected:

Expected Result

Is this the best approach or I should keep using FILTER and HAVING functions? Is there even a better approach I'm still missing? Thanks

MoazRub MoazRub · Accepted Answer · 2019-01-10T15:57:45

This is the best approach. You need to consider how MDX resolves result. In the example above it is a coincidence that your valid data in a continous region of first four columns of first row. Lets relax the filtering clause and make it >365000. Now take a look at last row of the result, the first two columns and the last column are eligible cells but the third and fourth column is not eligible. However your query will report it as null and the non empty function will not help. The reason is that non empty needs the entire row to be null Now the question that why filter is not eliminating the cell? Filter will eliminate a row or column when the criteria is greater then the sum on the other axis. So if filter is on columns the filter value has to be greater than the sum of rows for that column. Take a look at the sample below as soon as you remove the comments the last column will be removed.

select 
non empty
filter(
([Measures].[Internet Sales Amount]
,{[Date].[Calendar Year].&[2013],[Date].[Calendar Year].&[2014]}
,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]
),([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount])>45694.70--+0.05
)
on columns 
,
non empty
[Product].[Subcategory].members
on rows
from
[Adventure Works]

Edit another sample added.

with 
member [Measures].[Internet Sales AmountTest]
as 
iif(([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount])>9000,
([Date].[Calendar Year].currentmember,[Date].[Calendar Quarter of Year].currentmember,[Product].[Subcategory].currentmember,[Measures].[Internet Sales Amount]),
null
)


select 
non empty
({[Measures].[Internet Sales Amount],[Measures].[Internet Sales AmountTest]}
,{[Date].[Calendar Year].&[2013]}
,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]
)
on columns 
,
non empty
[Product].[Subcategory].[Subcategory]
on rows
from
[Adventure Works]

How to filter by measure values in MDX while having dimension members in both axis

1 Answers