13 Replies Latest reply: Sep 8, 2016 4:03 AM by Anya Kiatpanon

# Count distinct?

I have the following data and as can be seen there is one row for each question, each of these rows have the same (assignment)number.

How can I count how many different (assignment)numbers I have? Right now when I count I get 13 times as many as I would like as the (assignment) number appears 13 times for each questionaire.

Thanks!

• ###### Re: Count distinct?

try this Count(DISTINCT number)

• ###### Re: Count distinct?

May be this:

Count(DISTINCT question)

• ###### Re: Count distinct?

It Seems you want only distinct values of the umber column.

Use below expression in measure.

= Count( Distinct  number).

As per Above images this expression will give result 2.

• ###### Re: Count distinct?

I tried Count (DISTINCT number) but it gives me the total number of (assignment)numbers as it is connected to another table (Assignment) with all assignment(numbers).

Is there any way to calculate only those in a special qvd file (QualityReportAnswer)?

In my qvd files I have renamed the field numbers to AnswerAA.

• ###### Re: Count distinct?

Hi,

You can do one thing, the table from where you need the Count, you can add one more dummy field like

From xyz;

Now in expression you can use the set analysis to get the count only from that table like below.

Count({<Source = {"ABC"}>} distinct Number)

Regards,

Kaushik Solanki

• ###### Re: Count distinct?

try this.

• ###### Re: Count distinct?

Perhaps the count on the key field questions is causing you to include too many items, as it would count over all linked tables. Try counting on a non-key field, for example Count(DISTINCT QuestionName) with the assumption that QuestionName is an unique as your question number.

• ###### Re: Count distinct?

I could get your suggestion to work Kaushik Solanki, or yours either Toni Kautto.

However I tried to add a duplicate of my filed numbers that isn't connected to any other table. This one I used in a count instead, this worked.

• ###### Re: Count distinct?

So which field did you end up using to do the count here? and if possible briefly describe why do you think it worked for you and if you have got what you wanted, please close the thread by marking your own response as correct.

Best,

Sunny

• ###### Re: Count distinct?

To be fair I did not see the number field in your initial screenshot.

• ###### Re: Count distinct?

I ended up using the following expression to do the count: Count( distinct number)

• ###### Re: Count distinct?

But wasn't this proposed by Ugonna already? If this is what worked for you, I would suggest marking there response as the correct answer instead of your own.

Thanks,

Sunny