I am trying to get the distinct number of accounts that have subscribed
to recieve emails about cruises, have had a past trip between 2004 and
2006, and have flown out of either the Seattle or Houston airports.

My MDX is given below. It is returning the number of accounts but not
the number of distinct accounts. Could anyone give me some insite on
how to change this query to return distinct accounts?

Thanks. -- Nedra

SELECT
NON EMPTY
CROSSJOIN (
CROSSJOIN (
{[Sub_CruisesDim].[All Sub_CruisesDim]}
, {filter( {[Departure Date].[All Departure
Date]}, [Departure Date].[All Departure Date] >= 2004 or [Departure
Date].[All Departure Date] <= 2006) }
)
, {
filter ( {[DepartureAirport_Dim].[All
DepartureAirport_Dim]}, Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[IAH]) or Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[SEA]))
}
)
ON COLUMNS

FROM
[Account_Fact]
WHERE
([Measures].[Account Id])

the distinct keyword can do it.

select non empty distinct(....)

but you talk about a distinct "number" or ditinct "list"?


"Nedra" <nedraalbrecht (AT) hotmail (DOT) com> wrote

I am trying to get the distinct number of account ids. I think I read
somewhere this is called basket analysis (?). I'm very new to this, so
I'm mostly just fumbling around trying to get queries to work.

I tried to change my query to this:

with member measures.a
as 'distinctcount(
{ NONEMPTYCROSSJOIN (
CROSSJOIN (
{[Sub_CruisesDim].[All Sub_CruisesDim]}
, {filter( {[Departure Date].[All Departure
Date]}, [Departure Date].[All Departure Date] >= 2004 or [Departure
Date].[All Departure Date] <= 2006) }
)
, {
filter ( {[DepartureAirport_Dim].[All
DepartureAirport_Dim]}, Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[IAH]) or Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[SEA]))
}
)})'

SELECT
measures.a
ON COLUMNS

FROM
[Account_Fact]
where
[Measures].[Account Id]

I get an error saying that measures.a is unbindable in the current
context. Any ideas where I'm going wrong?

TIA - Nedra

first, try :
select measures.a on 0
from CubeName

second, do you use AS2005 or AS2000?

after...
the nonemptycrossjoin function support more then 2 sets at the same time!
nonemptycrossjoin(set1, set2, set3 ....)

the nonemptycrossjoin return a non empty set, so a simple count is enough
instead-of a the distinctcount formula.
(distinctcount will exclude empty cells which are allready exclude using the
nonemptycrossjoin; double work for nothing)

with member measures.a
as 'count(
NONEMPTYCROSSJOIN (
[Sub_CruisesDim].[All Sub_CruisesDim].MEMBERS
, filter( [Departure Date].[All Departure
Date].MEMBERS, [Departure Date].[All Departure Date] >= 2004 or [Departure
Date].[All Departure Date] <= 2006)
,
filter ( [DepartureAirport_Dim].[All
DepartureAirport_Dim].MEMBERS, Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[IAH]) or Not IsEmpty([DepartureAirport_Dim].[All
DepartureAirport_Dim].[SEA]))
)
)'

and you are right, its a basket analysis way.
search on the MSDN web site to find samples.


"Nedra" <nedraalbrecht (AT) hotmail (DOT) com> wrote