How should I create new measure to count distinct record in Business Layer?

[Former Member]

Dear Experts,

I'm facing problem to create new measure to count distinct record in business layer.

My data stored as below:

Per_ID	Year	Month	Department	Subject	Generation
1	2008	December	D. Computer	HTML	10
1	2008	Janaury	D. Math	Mathematics	10
1	2008	June	D. General	Social Comunication	10
1	2008	March	D. Computer	C Language	10
1	2008	September	D. Computer	C++ Language	10
2	2008	April	D. Computer	MS Word	10
2	2008	January	D. Math	Mathematics	10
2	2008	June	D. General	General English	10
2	2008	September	D. General	Social Communication	10
3	2009	February	D. General	General English	11
3	2009	Janaury	D. Math	Mathematics	11
3	2009	March	D. General	Social Comunication	11

I need to create new measure that count number of student by distinct base on Per_ID.

I need result like that:

     2008 have only 2 students.

     2009->D.General have only 1 student.

     January->D. Math have only 3 students.

How should I do with this problem?

Please help me if you have any suggestion.

Best regards,

Chenna Yon

[former_member4998 · Accepted solution]

Hi

@ Universe

Number of Student = Count (DISTINCT Table. Per_ID)

Data Type: Numeric

Projection Function: SUM

And place year and months on the filter:

If you give

Year as 2008 , you will get Number of students as two

Year as 2009 , Get Number of students as one

Year 2008 and Month jan, Get Number of students as two

[Former Member · Accepted solution]

Dear Sreenivasulu Dasari,

Why I can not use count(distinct(Per_ID)) with access connection in IDT?

Best regards,

Chenna Yon

[former_member4998 · Accepted solution]

Number of Student = Count (DISTINCT Table name. Field Name)

Ex: Number of Customers = Count(DISTINCT Customer.Customer_ID),

Here table Name is Customer and Field Name is Customer_ID.

[Former Member · Accepted solution]

Dear Sreenivasulu Dasari,

I tried what you suggested me.

But it met problem as image below:

How should I do with this problem?

Best regards,

Chenna Yon

[former_member4998 · Accepted solution]

Hi

I think you are using excel has a database, so DISTINCT function not supported.

if you try COUNT(Testing.Per_ID) will work. But if you want to count distinct Per_ID ...you need to find DISTINCT alternative function which supports excel.

[Former Member]

If you are using MS Access, I think the syntax needs to be slightly different:

count(distinct(table.column))

Also, you have been advised incorrectly on the projection of the measure.

Count uses projection of sum but Count distinct uses projection of none.

If you're particularly interested in why, I'll dig a link out.