cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

NameDocket OpenNumber of Times Docket AppearsNo. of Person on Same Docket
   Example
   Need a formula to count this column
NameDocket NumberCOUNT DOCKETCOUNT TECHNICIAN
Mr.XBC37540011
Mr.YBC37547133
Mr.XBC37547133
Mr.ZBC37547133
Mr.ABC37548132
Mr.ABC37548132
Mr.BBC37548132
Mr.CBC37549531
Mr.CBC37549531
Mr.CBC37549531

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Impactful Individual
Impactful Individual

Hi,

 

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

2. distinct count on rows and then expand:

2020-09-09_8-46-19.jpg2020-09-09_8-46-34.jpg2020-09-09_8-46-48.jpg

 

 

 

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

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

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

@sanjurk - Maybe:

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Impactful Individual
Impactful Individual

Hi,

 

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

2. distinct count on rows and then expand:

2020-09-09_8-46-19.jpg2020-09-09_8-46-34.jpg2020-09-09_8-46-48.jpg

 

 

 

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

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

View solution in original post

Resident Rockstar
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
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".

Formula.png

Community Support
Community Support

Hi @sanjurk ,

 

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.

 

 

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.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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