SSAS MDX - aggregate with multiple members

Question

I have the MDX query below and the result is not what I expected. If my where clause is included just 1 city ([Geography].[Iorg].[City].[San Francisco]) then my aggregate result is correct for that city, but if Included 2 cities then my aggregate result is for the whole state of california which is not what I wanted. I just want to return result of those two cities.

    { [Geography].[Iorg].[City].[San Francisco]
    ,[Geography].[Iorg].[City].[San Jose]
}

This clause is for security {[Geography].[State].[California]} but I don't get when 1 city is included then the result is good but when I included two cities then the result is for state California.

If I remove my [Geography].[Country Name].children ON ROWS then the result is correct but I need that in my query. Any help would be appreciated.

    SELECT       
    CROSSJOIN  ({       
    [Measures].[Fleet]},    
    {[Time Calculations].[Current Period] })  ON COLUMNS
    ,
    [Geography].[Country Name].children
    ON ROWS    
    FROM [DMI]      
    WHERE 
    (  
    [Date].[Date Hierarchy].[Date].&[2019-02-12T00:00:00] , 
    { [Geography].[Iorg].[City].[San Francisco]
    ,[Geography].[Iorg].[City].[San Jose]
    }
    ,{[Geography].[State].[California]}
    )

I presume that [Geography].[Iorg].[City] is a user hierarchy and it has a level state in it. If that is the case you are facing Reference Conflict. Read the following stackoverflow.com/questions/54258146/… — MoazRub
correct, [Geography].[Iorg].[City] is a user hierachy with Country:State:City. — xanhdieu

MoazRub MoazRub · Accepted Answer · 2019-02-14T20:44:52

You should query like this

SELECT       
    CROSSJOIN  ({       
    [Measures].[Fleet]},    
    {[Time Calculations].[Current Period] })  ON COLUMNS
    ,
    [Geography].[Country Name].children
    ON ROWS    
    From (select {[Geography].[Iorg].[City].[San Francisco],
[Geography].[Iorg].[City].[San Jose]}on 1 FROM [DMI] 
)     
    WHERE 
    (  
    [Date].[Date Hierarchy].[Date].&[2019-02-12T00:00:00] 
    ,{[Geography].[State].[California]}
    )

SSAS MDX - aggregate with multiple members

1 Answers