Count ALL issue

[Anonymous]

Hi,

In a measure I'm making a count of the file numbers. This seems to be a distinct count.

This is the measure that I'm using:

count(DOSSIERNR)

But how do I modify the measure so I also count the duplicate file numbers?

I've been experimenting with some variations but without succes.

Example:

file number

101

102

102

103

104

The measure count(DOSSIERNR) results in 4 but I also need a measure that counts 5 in this case.

Many thanks in advance for your assistance.

Wim

[Chanty4u]

try simple way

count(Distinct DOSSIERNR)

[arvind_patil]

Hi  Wim,

I agree  with chanty. Use  His code.

count(Distinct DOSSIERNR)

Thanks,

Arvind Patil

[stigchel]

In your example the Count([file number]) will result in 5 and Count( DISTINCT [file number]) will result in 4. So when you say that Count(DOSSIERNR) results in 4, there must be something else going on. You might be using a dimension (Afdeling?) that has no value for one of the DOSSIERNR rows.

[sunny_talwar · Accepted solution]

Seems like DOSSIERNR is a key field. It might make sense to count another field which is not a key field. For example, lets say you want to count DOSSIERNR from your fact table which look like this

LOAD DOSSIERNR,

     AnotherField,

...

FROM ....;

Then may be do a count like this

Count(AnotheField)

or

Count({<DOSSIERNR = {'*'}>}AnotherField)

[Chanty4u]

check sunny example here

How to load distinct value?

[Anonymous]

Hi Sunny, Chanty, Arvind and Piet Hein,

Thank you all for your valuable feedback.

Count distinct or just count have the same result and it was what Sunny wrote that made me thinking.

In the Data Model Viewer the field Dossiernr is not marked as a key but I found the cause of the 'strange' behaviour. Also in the Data Model Viewer I noticed that the field Dossiernr has no duplicates. From that point of view it seems perfectly normal that a count and a count distinct have the same result.

The solution offered by Sunny using set analysis worked great! Thanks for that.

Wim