Return all children where at least one exists in same parent

Question

I'm trying to write an MDX query that I think is similar to a where exists in SQL. We have Files which are made of SubFiles, and every SubFile is in a Location. There's a dimension for Locations and a dimension for Files (containing a File -> SubFile hierarchy), and a measure count of all SubFiles.

So the following MDX:

select
    [Location].[Location].members on 0,
    [File].[File].members on 1
from
    [MyCube]

returns something like:

             | LocA | LocB | LocC | LocD
----------------------------------------
FileA        | null |  2   |   2  | null
FileB        |  1   |  2   | null | null
FileC        | null | null |   1  |  2

This is showing that, for eg, FileA has 2 SubFiles in LocB and 2 SubFiles in LocC (and none in LocA or LocD). It has 4 SubFiles total.

What I need to achieve is, for a given Location return all SubFiles where at least one SubFile under the same File is in a given Location. So for eg given the above example, if the given location were LocC, the result set should be:

             | LocA | LocB | LocC | LocD
----------------------------------------
FileA        |
   SubFileA1 | null | null |   1  | null
   SubFileA2 | null |   1  | null | null
   SubFileA3 | null |   1  | null | null
   SubFileA4 | null | null |   1  | null
FileC        |
   SubFileC1 | null | null | null |   1
   SubFileC2 | null | null |   1  | null
   SubFileC3 | null | null | null |   1

So all SubFiles for FileA and FileC are returned as they have at least 1 SubFile in LocC, whilst FileB is not returned as it has no SubFiles in LocC.

How can I achieve this in MDX?

Answer 1

You can use the Exists function to get the Files, and then the Descendants function to add the sub files:

select
    [Location].[Location].members
    on 0,
    Descendants(
        Exists(
            [File].[File -> SubFile].[File].members, 
            {[Location].[Location].[LocC]}
        ), 
        [File].[File -> SubFile].[SubFile],
        SELF_AND_BEFORE
    )
    on 1
from
    [MyCube]