Add column with duplicate value counts

[BiJoe]

I have a denormalized employee dimension table, and employee fact table with key columns.

Some of the employees have same roles in multiple departments, and I want to add a column to the table, showing number of times the employees have the same role. Basically counting duplicate names.

For simplicity, I need to show everything in the same table

A super simple dummy:

I have already made a separate table: 

# duplicate roles = FILTER(SUMMARIZE(dim_employee, dim_employee[Display name], "# duplicates", count(dim_employee[Display name])), [# duplicates] > 1)

but I would like to keep this as a calculated column in the fact table. It seems that I can not use the EARLIER function referenced in many similar issues, because of calling this "from" the fact table.

Happy if someone can help out.

[v-cgao-msft · Accepted solution]

Hi @BiJoe ,

Please try:

Count = COUNTROWS(FILTER(ALL('dim_employee'),'dim_employee'[Name]=RELATED('dim_employee'[Name])))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Use variables rather than EARLIER

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...