Distinct Count

[HenryJS]

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'

Candidate	UserName	Company	Candidate + Date	Count of Company
Louisa Bennett	Will Goldup	A.J. Morrisroe & Sons Limited	90721-24/08/2020 14:23:00	3
Louisa Bennett	Will Goldup	Abbey Wood	90721-24/08/2020 14:23:00	3
Louisa Bennett	Will Goldup	Abbey Wood	90721-24/08/2020 14:23:00	3
Louisa Bennett	Will Goldup	Aggregate Industries UK Ltd	90721-24/08/2020 14:23:00	3
Louisa Bennett	Alan Freer	Danaher & Walsh (Civil Engineering) Limited	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	Kier EA Framework	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	Kier EA Framework	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	Kier EA Framework	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	Keltbray Limited	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	VINCI Construction UK Limited	90721-21/08/2020 10:35:00	5
Louisa Bennett	Alan Freer	Inabensa	90721-21/08/2020 10:35:00	5

Thanks,

H

[Fowmy · Accepted solution]

@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

[Greg_Deckler]

@HenryJS - I'm thinking:

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

[amitchandak]

@HenryJS , new columns

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