I am trying to compute a distinct count after I have filtered out
certain elements, but I can't seem to get the query right. Here's the
equivalent query on the FoodMart 2000 database. I'm using Analysis
Services 2000.

In English, I'm trying to execute this query:

"For each store type, count the number of promotions run at stores with
more than 1325 sales made."

Here's the query that I have now:

WITH
SET [SetStoreType] AS '{[Store Type].[Store Type].Members}'
SET [SetPromotion] AS 'NonEmptyCrossjoin([Promotions].[Promotion
Name].Members,{Filter({[Store].[Store Name].Members},[Measures].[Sales
Count] >1325)},2)'
MEMBER [Measures].[MyCount] AS 'DistinctCount([SetPromotion])'
SELECT NON EMPTY {[Measures].[MyCount] } ON COLUMNS,
NON EMPTY {SetStoreType} on rows
from Sales

This query produces incorrect results because I want to have a distinct
count on the number of promotions, but the query gives me a distinct
count on the number of promotion/store combinations. For instance, in
the Small Grocery store type, the answer should be 17, but this query
returns 21, since four promotions (No Promotion, One Day Sale, Super
Savers, and Tip Top Savings) were run at both stores 2 and 22.

However, if I change the last parameter of the NonEmptyCrossjoin to 1,
in order to consolidate duplicate stores, my query no longer eliminates
stores with <= 1325 in sales made. In the Small Grocery category, this
means that store 14 (with sales of exactly 1325) is now included again,
giving a total of 24 promotions. Again, the answer I want is 17.

I've looked around the documentation for other functions to use, but I
can't find something that meets my needs. Thanks for any help you can
provide!

Breaking this down, when you get a count of 21 for Small Grocery, you
are counting a set of Promotion and Stores, so you are getting a is
distinct count of unique promotion/store combinations.

When you exclude the stores from the returned set you are getting all
promotions for which *any* store type has stores with more than 1325
sales, when you cross this set with store types, it brings back in
stores that had any sales for the given set of promotions, hence your 24
figure.

This is a difficult concept to explain and I don't know if I am doing a
good job, but I do have a solution.

I think the problem you are facing is because you are viewing the
measure by [Store Type], but you are not specifying any context for
store type in calculation. I have included [Store Type] in the filter
statement and then I am using the SetCount parameter of the
NonEmptyCrossjoin to only bring back Promotions members.

WITH
SET [SetStoreType] AS '{[Store Type].[Store Type].Members}'

MEMBER [Measures].[MyCount] AS 'DistinctCount(NonEmptyCrossjoin
([Promotions].[Promotion Name].Members,{Filter(NonEmptyCrossjoin({[Store
Type].CurrentMember},{[Store].[Store Name].Members}),[Measures].[Sales
Count] >1325)},1) )'


SELECT
NON EMPTY {[Measures].[MyCount] }
ON COLUMNS,
NON EMPTY
{SetStoreType}
on rows
from Sales

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132084470.828195.118600 (AT) o13g2000cwo (DOT) googlegroups.com>,
bigredgum1 (AT) excite (DOT) com says...

Thanks! That query works perfectly.

I didn't realize that breaking out the NonEmptyCrossjoin into the
SetPromotion set was different from including it directly in the
DistinctCount. I thought that the two were functionally equivalent.

In article <1132236146.256568.157520 (AT) f14g2000cwb (DOT) googlegroups.com>,
bigredgum1 (AT) excite (DOT) com says...
Yes, it had me scratching my head too

The difference is the way in which it treats the [Store
Type].CurrentMember function. WITH SET... creates one set with Query
scope, where as what you wanted was to re-evaluate the set on each row.

From deduction (I could not find any doco on this) I am assuming that
with the named set approach, AS is resolving the axis of the query, then
looping over all the [Store Type] members and unioning the result into a
single set of promotions called "SetPromotions".

Where as with the inline expression, the filtering gets dynamically run
for each [Store Type] member.

HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell