Forum Discussion
Add column with duplicate value counts
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.
Hi BiJoe ,
Please try:Count = COUNTROWS(FILTER(ALL('dim_employee'),'dim_employee'[Name]=RELATED('dim_employee'[Name])))Best Regards,
Gao
Community Support TeamIf 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
2 Replies
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-Forum/ba-p/963216
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/1447523Hi BiJoe ,
Please try:Count = COUNTROWS(FILTER(ALL('dim_employee'),'dim_employee'[Name]=RELATED('dim_employee'[Name])))Best Regards,
Gao
Community Support TeamIf 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
