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 https://stackoverflow.com/questions/54258146/adventureworks-date-dimension-shows-different-results-depending-on-selected-hier/54392679#54392679 — MoazRub, Feb 14 '19 at 20:06
correct, [Geography].[Iorg].[City] is a user hierachy with Country:State:City. — xanhdieu, Feb 14 '19 at 20:25

MoazRub · Accepted Answer · 2019-02-14T20:56:53.597

0

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]}
    )

edited Feb 14 '19 at 20:56

answered Feb 14 '19 at 20:44

MoazRub

2,881
2
10
20

Thanks for the Reference Conflict direction. I solved this by creating a new attribute dimension STATE just for this. – xanhdieu Feb 15 '19 at 17:39
@xanhdieu You are welcome.You can use your old structure if you use the above format, this will prevent the reference conflict. – MoazRub Feb 15 '19 at 17:41

SSAS MDX - aggregate with multiple members

1 Answers1