Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HenryJS
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
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@HenryJS , new columns

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.