Search code examples
ssasmdx

MDX // Filter DESCANDANTS on Columns including ALL


I want to get a set of certain members from a hierarchy including the ALL-Member.

SELECT 
    [Measures].[Distinct Count SKU]
ON 0,
        DESCENDANTS(
            [Filter Place].[Location].[All],, SELF_AND_AFTER    
        )
ON 1
FROM 
    [STOCK]

I do get the All-Member from this expression, however it contains locations I would like to have filtered out.

This is what I get: enter image description here

This is what I want to get: enter image description here


Solution

  • You dont need to use the descendants function, insteed use the [ALL] level. in a simple set.

    SELECT     [Measures].[Distinct Count SKU] ON 0,
    {
    [Filter Place].[Location].[All],
    [Filter Place].[Location].[Berlin],
    [Filter Place].[Location].[London],
    [Filter Place].[Location].[Paris]
    } ON 1
    FROM [STOCK]