cancel
Showing results for
Did you mean:
Regular Visitor

## Excel Formula or Power Query

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

1 ACCEPTED SOLUTION

Accepted Solutions
Impactful Individual

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.

// if this is a solution please mark as such. Kudos always appreciated.
4 REPLIES 4
Super User IV

@sanjurk - Maybe:

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Impactful Individual

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.

// if this is a solution please mark as such. Kudos always appreciated.
Resident Rockstar

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

Hi, @sanjurk , 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

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

Community Support

Hi @sanjurk ,

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors