Excel Formula or Power Query

[Anonymous]

Is there any way to figure out a formula on the below :

I need the last column with a formula that could count as per the docket number how many people has worked on that docket. The last column will count and return a value accordingly.

If there are three dockets open with same number, and one person has worked on it - it will return value 1. The below is an example

I have attached both picture and a table below to understand it clearly.

Name	Docket Open	Number of Times Docket Appears	No. of Person on Same Docket
			Example
			Need a formula to count this column
Name	Docket Number	COUNT DOCKET	COUNT TECHNICIAN
Mr.X	BC375400	1	1
Mr.Y	BC375471	3	3
Mr.X	BC375471	3	3
Mr.Z	BC375471	3	3
Mr.A	BC375481	3	2
Mr.A	BC375481	3	2
Mr.B	BC375481	3	2
Mr.C	BC375495	3	1
Mr.C	BC375495	3	1
Mr.C	BC375495	3	1

[v-xuding-msft]

Hi @Anonymous ,

Have you resolved it? If yes, please accept the helpful answers as solutions. Others will benefit from this thread. Thanks!! If you still need help, please feel free to ask us.

[CNENFRNL]

Aha, formula, my good old ones ...😉

Hi, @Anonymous , since PQ and DAX solutions are already provided, pls refer to formula solution as follows,

COUNT DOCKET =COUNTIF(DS[Docket Number],DS[@[Docket Number]])

//Array formula, Ctrl+Shift+Enter to enter
COUNT TECHNICIAN {=COUNT(0/FREQUENCY(IFERROR(MATCH(IF(DS[Docket Number]=DS[@[Docket Number]],DS[Name]),DS[Name],),""),ROW(DS[Name])-ROW(DS[[#Headers],[Name]])))}

 first things first, convert the dataset range into a table (hotkey: Ctr+T) named "DS".

[samdthompson · Accepted solution]

Hi,

1. group by on docket number - use the advanced grouping.

2. distinct count on rows and then expand:

// if this is an answer please mark as such, kudos always appreciated.

[Greg_Deckler]

@Anonymous - Maybe:

Count Technician (Column) =
  VAR __Table = FILTER('Table',[Docket Number]=EARLIER([Docket Number]))
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Name",[Name])))