If you’re working with data in Power BI and would like to count how many duplicates there are, the below example will help you.
Let’s start with the table structure. For this example, lets says we want to see how many people have the same first name, and the data looks like this:
| EmployeeID | FirstName | Surname |
| 1 | John | Price |
| 2 | Sarah | Williams |
| 3 | William | Smith |
| 4 | Jeff | Bridges |
| 5 | Richard | Walsh |
| 6 | Kate | Jones |
| 7 | Sarah | Parker |
| 8 | William | Wallace |
Now lets create a new column and create a formula to count how many people share the same first name.
1) Switch to the data view
2) Make sure you have your table selected, in my case, I only have one
3) Select ‘New column’
You should then see the below
4) Where it says ‘Column’, this is what you want the new column to be called. Change this to whatever you want your new column to be called, I’ll go with ‘SharesTheSameFirstName’ for mine.
5) Now let’s make the query itself (replace Query1 with the name of your table). The unique row id for this example is EmployeeID, so we will count the rows with unique id’s that have the same FirstName.
Here’s a simple example to get you started:
SharesTheSameFirstName =
COUNTX(
FILTER( Query1, EARLIER(Query1[FirstName])=Query1[FirstName]),
Query1[EmployeeID]
)6) When you have your code as you want, click the tick, and your data should be like this:
| EmployeeID | FirstName | Surname | SharesTheSameFirstName |
| 1 | John | Price | 1 |
| 2 | Sarah | Williams | 2 |
| 3 | William | Smith | 2 |
| 4 | Jeff | Bridges | 1 |
| 5 | Richard | Walsh | 1 |
| 6 | Kate | Jones | 1 |
| 7 | Sarah | Parker | 2 |
| 8 | William | Wallace | 2 |
You can filter on multiple colums too, so you could alter the code to check for people with the same first name and surname? An example of this approach would be:
SharesSameName =
COUNTX(
FILTER( Query1, EARLIER(Query1[FirstName])=Query1[FirstName]) && Query1[Surname])=Query1[Surname]),
Query1[EmployeeID]
)