We have a fact table in a retail application containing a
transaction number. For each sale, we have the following
fact:
Time, Amount, Item, TransactionNumber

We need to create a measure that gives us the DISTINCT
COUNT of TransactionNumber.

Can someone help me with the MDX needed to create the
measure?

Thanks,

James.

Are you aware that a measure can be defined as distinct count?

public @ the domain below
www.tomchester.net

"James" <news (AT) att (DOT) com> wrote

If you go to the Measure and look at basic properties, you will find the aggregate function property that you can change to distinct count. You can only have ONE per cube and I found when I tried to create a distinct count, that the measure I had to be numeric.

I had a client number which is a varchar field, and it wouldn't let me set it to distinct count. When I created a new field, which was an integer identity column, I could then pick distinct count on the aggregate function property of the measure

Molly

Many thanks Molly, that sure looks like my problem, the
transaction number is defined as nvarchar for some reason.
I'll create a view on the fact table to show that column
as int, and I should be all set.



change to distinct count. You can only have ONE per cube
and I found when I tried to create a distinct count, that
the measure I had to be numeric.
new field, which was an integer identity column, I could
then pick distinct count on the aggregate function
property of the measure.

I'm experiencing the following problem:

"The cube contains a distinct count measure, and at least
one of the enabled levelsin the dimensions used in this
cube contains a custom rollup definition."

Do you know a workaround?

Because DISTINCT COUNT aggregarion doesn't support custom
aggregations, the use of this aggregation function in
combination with CustomRollupExpression and
CustomRollupColumn properties is not supported.

HTH,
Sanka