Count [Distinct] Formula Usage in Calculations vs. Query Subject Items

[cdallum]

I am trying to figure out how (and why) the count [distinct] formula works differently in a calculation vs. a query subject item (usage = Fact).  I'm trying to count the distinct number of payments in a fact table with payment line item granularity.  I have the payment header key (e.g. payment_key) in the fact table, which is unique for each payment and repeated for each payment line item associated with each payment.  My goal is to count the distinct number of payments, even though the granularity of the fact table is lower than my count.

When writing an Oracle SQL statement, I would simply use the count(distinct payment_key) expression.

I first tried creating a new query subject item and used the count(payment_key) formula, assuming the distinct is understood.  The result was a count of every payment line item, not a distinct count of the payments.  I tried a number of variations on the query subject item to obtain the desired result to no avail.

I then tried creating a calculation outside of the query subject and used the same formula syntax (e.g. count(payment_key)) as I first tried in the query subject item.  Voila, I received a distinct count of the payments, not a count of every payment line item.

Does anyone have some insight as to why I receive different results when using essentially the same syntax and also what syntax is needed to get the distinct count I desire in a query subject item?

Thanks.

[cdallum]

I did determine that to obtain the count distinct result in the query subject item I had to bring in just the payment_key into the expression, and then set the Regular Aggregate property to count distinct.

I had tried setting both the property to count distinct and putting the count forumla into the expression.  That resulted in a very low count.  I suppose the aggregate property was counting the count in this case.