cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Distinct Count

Hi all,

 

How can I create the 'Count of Company' column?

 

This is a distinct **bleep** of 'Company' in relation to 'Candidate + Date' field.

 

So '90721-24/08/2020 14:23:00' has 3 distinct 'Company's'

 

And '90721-21/08/2020 10:35:00' has 5 distinct 'Compant's'

 

 

CandidateUserNameCompanyCandidate + DateCount of Company
Louisa BennettWill GoldupA.J. Morrisroe & Sons Limited90721-24/08/2020 14:23:003
Louisa BennettWill GoldupAbbey Wood90721-24/08/2020 14:23:003
Louisa BennettWill GoldupAbbey Wood90721-24/08/2020 14:23:003
Louisa BennettWill GoldupAggregate Industries UK Ltd90721-24/08/2020 14:23:003
Louisa BennettAlan FreerDanaher & Walsh (Civil Engineering) Limited90721-21/08/2020 10:35:005
Louisa BennettAlan FreerKier EA Framework90721-21/08/2020 10:35:005
Louisa BennettAlan FreerKier EA Framework90721-21/08/2020 10:35:005
Louisa BennettAlan FreerKier EA Framework90721-21/08/2020 10:35:005
Louisa BennettAlan FreerKeltbray Limited90721-21/08/2020 10:35:005
Louisa BennettAlan FreerVINCI Construction UK Limited90721-21/08/2020 10:35:005
Louisa BennettAlan FreerInabensa90721-21/08/2020 10:35:005

 

Thanks,

 

H

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@HenryJS

Use it as a new column

Column = CALCULATE( DISTINCTCOUNT('Table'[Company]), ALLEXCEPT('Table','Table'[Candidate + Date]))

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@HenryJS , new columns

countx(filter(table, [Candidate + Date] =earlier([Candidate + Date])),[Company])
or
calculate(distinctcount([Company]) ,filter(table, [Candidate + Date] =earlier([Candidate + Date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

@HenryJS - I'm thinking:

 

Column =
  VAR __Table = FILTER('Table',[Candidate + Date] = EARLIER([Candidate + Date]))
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Company",[Company])))

 


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

@ 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!




Highlighted
Community Champion
Community Champion

@HenryJS

Use it as a new column

Column = CALCULATE( DISTINCTCOUNT('Table'[Company]), ALLEXCEPT('Table','Table'[Candidate + Date]))

________________________

If my answer was helpful, consider Accepting it as the solution to help other members find it

Click the Thumbs-Up icon if you like this answer 🙂

Youtube Linkedin

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

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