Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@HenryJS - I'm thinking:
Column =
VAR __Table = FILTER('Table',[Candidate + Date] = EARLIER([Candidate + Date]))
RETURN
COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Company",[Company])))
@HenryJS , new columns
countx(filter(table, [Candidate + Date] =earlier([Candidate + Date])),[Company])
or
calculate(distinctcount([Company]) ,filter(table, [Candidate + Date] =earlier([Candidate + Date])))
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |