• I think that this technique (using AdventureWorks as an example) does what you are looking for. You should be able to adapt it to your needs (providing you have a level above store in your hierarchy):

    WITH MEMBER SPCount AS

    [Geography].[Geography].CURRENTMEMBER.PARENT.CHILDREN.COUNT

    SELECT

    {[Measures].[Reseller Sales Amount]} ON 0,

    NON EMPTY

    GENERATE (

    [Sales Territory].[Sales Territory].[Country].MEMBERS,

    [Sales Territory].[Sales Territory].CURRENTMEMBER

    *

    TOPCOUNT(

    FILTER([Geography].[State-Province].[State-Province].MEMBERS, [SPCount] > 1),

    1,

    [Measures].[Reseller Sales Amount]

    )

    )

    ON 1

    FROM [Adventure Works]

    So in the example above, it excludes a sales territory (United Kingdom) as it only has 1 "State-Province" (England).

    It's a bit of a fudge but frankly, I don't care 😛


    I'm on LinkedIn