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