DISTINCT COUNT

Question

Table: CustID, Partner and EdiToDoNo are unique in the following table 

CustID, Partner, EdiToDoNo 

C1, P1, 1 

C1, P1, 2 

C1, P2, 2 

C1, P2, 3 

C1, P3, 3 

C2, P3, 3 

I want to precalculate the above table into following table using SQL in which CustID and Partner are unique.

CustID, Partner, Col1, Col2, .....

In the above table CustID and Partner are unique. The Col1, Col2 ... so on contains the calculation to calculate the measure Order in report. 

Measure Order = Distinct of EdiToDoNo.

The order should be calculated based on the Col1, Col2....so on.

Test case 1: When C1 is selected from CustID filter, the Order should be 3 as 1, 2, 3 are distinct for C1. 

Test case 2: When C1 is selected from CustID  and P1 and P2 is selected from Partner the Order should be 3 as 1,2,3 are distinct,

Test case 3: When C2 is selected from CustID the Order should be 1 as 3 is distinct for C2.

If any one gets answer please respond

Answer 1

Hi,MohanR_492

Thank you for consulting the Microsoft community and reporting your issue.

Seeing that your question is posted in the Word board, I would like to confirm that you need to edit or update tabular data in a Word document? Or do you mean using SQL statements to insert and manipulate data in Excel?

Or, are you using other software to work with this data?

Please provide us with more information so that we can test it in the appropriate test environment for your needs.

We look forward to hearing from you and hope to help you resolve your issue as soon as possible. Thank you for your patience and understanding.

Best wishes.

Cherry.Z |Microsoft Community Support Specialist