Search code examples
mdxiccube

Filter Set by another Set

I want to filter a set by another set, which is returned by the event @{date}. The following code only works if the @{date} event returns a single member (e.g. [TIME_DIMENSION].[YEAR].[2010]). Any suggestions how to filter if the event returns more than one member?

WITH SET [A] AS
    Filter(
        TopPercent(
            [PRODUCTS].members - [PRODUCTS].[all],
            80,
            [Measures].[Sales]
        ),
        @{date}
    )
Solution

  • Maybe you could use the NonEmpty function? It would return the set of non empty tuples from the set.

    WITH SET [A] AS
NonEmpty(    
        TopPercent(
            [PRODUCTS].members - [PRODUCTS].[all],
            80,
            [Measures].[Sales]
        )
    ,@{date} * [Measures].[Sales]
       )