Hi,

I have Store, Visit and Time(Year, Month, Day) dimensions and VisitResult
measure in my Cube - to simplify things.
Stores can be visited 0, 1 or many times per month.

I need to write MDX query that returns number of different Stores visited in
one year, that have been visited at least once each month.
And it shoud be done by months, so for January it's only count of distinct
Stores that have been visited, that's no problem.
Then for February it is count of distinct stores that have been visited both
in January and February. For March it is count
of distinct Stores visited at least once in January and February and March.
And so on to December which is count of distinct Stores
visited in one year, that have been visited at least once each month.

Does the "Visits" dimension track individual visit events as members -
its role is not clear from the description?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

maybe a formula like this: (not tested)

count(filter(stores.storelevel.members, measures.visits =12))

this can works if you have only 2 dimensions (time and store)
if you want to count the number of months with at least 1 visit then a
formula like this:

sum(stores.storelevel.members,
iif(12=count(filter(time.month.members, measures.visits>0)), 1,0)

for each store, the number of months with visits = 12 count it.

read this excellent article:
http://msdn.microsoft.com/library/de...l/semiadd2.asp
"Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the
Multiple Selection of Members Problem"

"Bender" <roman.ilic (AT) avtenta (DOT) si> a écrit dans le message de news:
u3zWwgR4EHA.3908 (AT) TK2MSFTNGP12 (DOT) phx.gbl...