Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
try simple way
count(Distinct DOSSIERNR)
Hi Wim,
I agree with chanty. Use His code.
count(Distinct DOSSIERNR)
Thanks,
Arvind Patil
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.
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)
check sunny example here
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